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

> Sandro Santilli <strk(at)kbt(dot)io> writes:
> > On Mon, Jan 09, 2023 at 05:51:49PM -0500, Tom Lane wrote:
> >> ... you still need one script file for each supported upgrade step
>
> > That's exactly the problem we're trying to solve here.
> > The include support is nice on itself, but won't solve our problem.
>
> The script-file-per-upgrade-path aspect solves a problem that you have,
> whether you admit it or not; I think you simply aren't realizing that
because
> you have not had to deal with the consequences of your proposed feature.
> Namely that you won't have any control over what the backend will try to
do in
> terms of upgrade paths.
>

It would be nice if there were some way to apply at least a range match to
upgrades or explicitly state in the control file what paths should be
applied for an upgrade.
Ultimately as Sandro mentioned, it's the 1000 files issue we are trying to
address.

The only way we can fix that in the current setup, is to move to a minor
version mode which means we can
never do micro updates.

> As an example, suppose that a database has foo 4.0 installed, and the DBA
> decides to try to downgrade to 3.0. With the system as it stands, if
you've
> provided foo--4.0--3.0.sql then the conversion will go through, and
presumably
> it will work because you tested that that script does what it is intended
to. If
> you haven't provided any such downgrade script, then ALTER EXTENSION
> UPDATE will say "Sorry Dave, I'm afraid I can't do that" and no harm is
done.
>
In our case we've already addressed that in our script, because our upgrades
don't rely on what
extension model tells us is the version, ultimately what our
postgis..version() tells us is the true determinate (one for the lib file
and one for the script).
But you are right, that's a selfish way of thinking about it, cause we have
internal plumbing to handle that issue already and other projects probably
don't.

What I was hoping for was to having a section in the control file to say
something like

Upgrade patterns: 3.0.%--4.0.0.sql, 2.0.%--4.0.0.sql

(and perhaps some logic to guarantee no way to match two patterns)
So you wouldn't be able to have a set of patterns like

Upgrade patterns: %--4.0.0.sql, 3.0.%--4.0.0.sql, 2.0.%--4.0.0.sql

That would allow your proposed include something or other to work and for us
to be able to use that, and still reduce our
file footprint.

I'd even settle for absolute paths stated in the control file if we can
dispense with the need a file path to push you forward.

In that mode, your downgrade issue would never happen even with the way
people normally create scripts now.

> So I really think this is a case of "be careful what you ask for, you
might get it".
> Even if PostGIS is willing to put in the amount of infrastructure legwork
needed
> to make such a design bulletproof, I'm quite sure nobody else will manage
to
> use such a thing successfully. I'd rather spend our development effort on
a
> feature that has more than one use-case.
>
> regards, tom lane

I think you are underestimating the number of extensions that have this
issue and could benefit (agree not in the current incarnation of the patch).
It's not just PostGIS, it's pgRouting (has 56 files), h3 extension (has 37
files in last release, most of them a do nothing, except at the minor update
steps) that have the same issue (and both pgRouting and h3 do have little
bitty script updates that follows the best practice way of doing this).
For pgRouting alone there are 56 files for the last release (of which it can
easily be reduced to about 5 files if the paths could be explicitly stated
in the control file).

Yes all those extensions should dispense with their dreams of having micro
updates (I honestly wish they would).

Perhaps I'm a little obsessive, but it annoys me to see 1000s of files in my
extension folder, when I know even if I followed best practice I only need
like 5 files for each extension.

And as a packager to have to ship these files is even more annoying.

The reality is for micros, no one ships new functions (or at least shouldn't
be), so all functions just need to be replaced perhaps with a micro update
file you propose.

Heck if we could even have the option to itemize our own upgrade file paths
explicitly in the control file,

Like:

paths: 3.0.1--4.0.0 = 3--4.0.0.sql, 3.0.2--4.0.0 = 3--4.0.0.sql,
2--4.0.0.sql = 2.0.2--4.0.0.sql

I'd be happy, and PostgreSQL can do the math pretending there are files it
thinks it needs.

So if we could somehow rework this patch perhaps adding something in the
control to explicitly state the upgrade paths.

I think that would satisfy your concern? And I'd be eternally grateful.

Thanks,
Regina