Re: [PATCH] Support % wildcard in extension upgrade filenames

> On May 1, 2023, at 4:24 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Eric Ridge <eebbrr(at)gmail(dot)com> writes:
>> FWIW, outside of major ZDB releases, most of those have little-to-zero schema changes. But that doesn't negate the fact each release needs its own upgrade.sql script. I'm working on a point release right this moment and it won't have any schema changes but it'll have an upgrade.sql script.
>
> Hmm ... our model for the in-core extensions has always been that you
> don't need a new upgrade script unless the SQL declarations change.

That's a great model when the schemas only change once every few years or never.

> Admittedly, that means that the script version number isn't a real
> helpful guide to which version of the .so you're dealing with.

It isn't. ZDB, and I think (at least) PostGIS, have their own "version()" function. Keeping everything the same version keeps me "sane" and eliminates a class of round-trip questions with users.

So when I say "little-to-zero schema changes" I mean that at least the version() function changes -- it's a `LANGUAGE sql` function for easy human inspection.

> We expect the .so's own minor version number to suffice for that,
> but I realize that that might not be the most user-friendly answer.

One of my desires is that the on-disk .so's filename be associated with the pg_extension entry and not Each. Individual. Function. There's a few extensions that like to version the on-disk .so's filename which means a CREATE OR REPLACE for every function on every extension version bump. That forces an upgrade script even if the schema didn't technically change and also creates the need for bespoke tooling around extension.sql and upgrade.sql scripts.

But I don't want to derail this thread.

eric