While working on the pgmq overloaded drop_queue fix for https://github.com/supabase/postgres/pull/2400 (the extension teardown issue with db reset on Postgres 17.6+), I started wondering if there’s a broader pattern here. Since after-create.sql scripts are static and only run once when the extension is initially created, a future extension version bump could potentially bring back the same kind of bug. Would it make sense to have a more general validation or lint step for these extension scripts, maybe checking for hardcoded function signatures and similar issues? Or is per-extension maintenance considered good enough for now?
Mandar Joshi raises a concern about potential bugs in static after-create.sql scripts for PostgreSQL extensions, suggesting a validation step to prevent issues like the pgmq overloaded drop_queue fix. Edward Song agrees, proposing a lightweight validation focused on install and upgrade behavior, with optional static checks for hardcoded function signatures.
I think a lightweight validation step would be worthwhile, but I would keep it focused on install and upgrade behavior rather than trying to lint arbitrary SQL patterns.
The pgmq failure is a good example: the static DROP FUNCTION statements assumed a single signature, while newer pgmq versions introduced an overload. A useful check could install each extension into a clean database for the supported PostgreSQL versions, run the extension's after-create.sql, and then exercise the upgrade/reset path. That would catch failures caused by overloaded functions, changed argument types, or objects that are no longer extension members.
For the SQL itself, a lint rule could flag hardcoded function drops when an extension has multiple matching entries in pg_proc, but I would treat that as a warning rather than a correctness rule. Some scripts intentionally target one specific signature, and the authoritative check is whether the object is registered in pg_depend for that extension.
So my preference would be:
That gives us protection against the class of bug without requiring every extension to follow one rigid implementation pattern.