drop table event triggers for vectorize.jobs

[ChuckHend]

if a table is dropped that is part of vectorize.jobs, then the vectorize.job should also be deleted.

an event trigger can solve this. some scratch code as example of the idea

CREATE OR REPLACE FUNCTION after_drop_trigger()
RETURNS event_trigger AS $$
BEGIN
    DELETE FROM vectorize.job WHERE table_name = TG_TABLE_NAME;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER trg_after_drop
ON sql_drop
WHEN TAG IN ('DROP TABLE')
EXECUTE FUNCTION after_drop_trigger();

[algora-pbc]

💎 $200 bounty • Tembo

Steps to solve:

1. Start working: Comment /attempt #148 with your implementation plan
2. Submit work: Create a pull request including /claim #148 in the PR body to claim the bounty
3. Receive payment: 100% of the bounty is received 2-5 days post-reward. Make sure you are eligible for payouts

Thank you for contributing to tembo-io/pg_vectorize!

Add a bounty • Share on socials

Attempt	Started (GMT+0)	Solution
🟢 @varshith257	Oct 17, 2024, 12:22:28 PM	WIP

[varshith257]

/attempt #148

Algora profile	Completed bounties	Tech	Active attempts	Options
@varshith257	16 bounties from 8 projects	Go, Rust, Scala & more	﹟145	Cancel attempt

[varshith257]

@ChuckHend Can I get this issue assigned?