Obscured runtime extension patching and migrations

[steve-chavez]

Problem

At create extension time, several extensions are patched and migrations are applied to them. This is hard to see and maintain.

For example:

• pgmq function has a function patched here.
• pg_cron has a privilege migration applied here
• See more on ansible/files/postgresql_extension_custom_scripts subdir.

Additionally:

• Whenever one of the custom scripts has bad syntax, the error message is complicated and unhelpful:

PL/pgSQL function inline_code_block line 1 at EXECUTE
SQL statement "    do $_$    begin      execute replace(replace(replace(replace(            pg_read_file('/build/tmp.RwJWZ0twg6/extension-custom-scripts/pgmq/after-create.sql')          >
Error executing SQL file

Solution

Nix has a standard patches attribute which can be used. Which is much more visible and doesn't happen at runtime, but at "release" time.

Migrations should have a SSoT and that should be the migrations subdir.

---

It seems that this practice was adopted before Nix was introduced.

Following this, supautils custom scripts should likely be gone too.