David Christensen: Building PostgreSQL Extensions: Dropping Extensions and Cleanup

I recently created a Postgres extension which utilizes the pg_cron extension to schedule recurring activities using the cron.schedule(). Everything worked great. The only problem was when I dropped my extension, it left the cron job scheduled, which resulted in regular errors: 2024-04-06 16:00:00.026 EST [1548187] LOG: cron job 2 starting: SELECT bridge_stats.update_stats('55 minutes', false) 2024-04-06 16:00:00.047 EST [1580698] ERROR: schema "bridge_stats" does not exist at character 8 2024-04-06 16:00:00.047 EST [1580698] STATEMENT: SELECT bridge_stats.update_stats('55 minutes', false) If you look in the cron.job table, you can see the SQL for the cron job is still present, even though the extension/schema isn’t: select schedule, command, jobname from cron.job; schedule | command | jobname -----------+-------------------------------------------------------+---------------------------------- 0 0 * * 0 | SELECT bridge_stats.weekly_stats_update() | bridge-stats-weekly-maintenance 0 * * * * | SELECT bridge_stats.update_stats('55 minutes', false) | bridge-stats-hourly-snapshot (2 rows) This got me thinking: how can you create a Postgres extension that can clean up after itself for cases like this? How Extension Creation/Cleanup works If you’ve created or used an extension in Postgres (such as pg_partman, PostGIS, pg_kaboom, etc) you may know that every extension in PostgreSQL has a SQL file that gets run as part of the creation. This SQL file may create database objects for you, such as schemas, tables, functions, etc. When database objects are created in the context of a CREATE EXTENSION command, they have an object dependency created against the underlying pg_extension object. (These are stored in the pg_depend system catalog, if you are interested in the more fine-grained details.) When Postgres removes an extension (via the DROP EXTENSION command), it will also remove any dependent objects that were created f[...]