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

On Sat, May 28, 2022 at 11:37:30AM -0400, Tom Lane wrote:
> Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> writes:

> > 2. What if you have a "postgis--%--3.3.sql", and somebody tries to upgrade
> > their PostGIS 1.1 installation with it? Would that work?
> > Having a lower bound for a matching version might be a good idea,
> > although I have no idea how to do that.
>
> The lack of upper bound is a problem too: what stops the system from
> trying to use this to get from (say) 4.2 to 3.3, and if it does try that,
> will the script produce a sane result?

This is a very old problem we had before EXTENSION was even available
in PostgreSQL, and so we solved this internally. The upgrade script
for PostGIS checks the version of the existing code and refuses to
downgrade (and refuses to upgrade if a dump/restore is required).

> I'm frankly skeptical that this is a good idea at all. It seems
> to have come out of someone's willful refusal to use the system as
> designed, ie as a series of small upgrade scripts that each do just
> one step. I don't feel an urgent need to cater to the
> one-monster-script-that-handles-all-cases approach, because no one
> has offered any evidence that that's really a better way. How would
> you even write the conditional logic needed ... plpgsql DO blocks?
> Testing what? IIRC we don't expose any explicit knowledge of the
> old extension version number to the script.

We (PostGIS) do expose explicit knowledge of the old extension, and
for this reason I think the pattern-based logic should be
enabled explicitly in the postgis.control file. It could be even less
generic and more specific to a given extension need, if done
completely inside the control file. For PostGIS all we need at the
moment is something like (in the control file):

one_monster_upgrade_script = postgis--ANY--3.3.0.sql

--strk;