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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Tue, Jan 10, 2023 at 6:50 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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.

> I mean, there is nothing to prevent the extension author from writing
> a script which ensures that the extension membership after the
> downgrade is exactly what it should be for version 3.0, regardless of
> what it was before. SQL is Turing-complete.

It may be Turing-complete, but I seriously doubt that that's sufficient
for the problem I'm thinking of, which is to downgrade from an extension
version that you never heard of at the time the script was written.
In the worst case, that version might even contain objects of types
you never heard of and don't know how to drop.

You can imagine various approaches to deal with that; for instance,
maybe we could provide some kind of command to deal with dropping an
object identified by classid and objid, which the upgrade script
could scrape out of pg_depend. After writing even more code to issue
those drops in dependency-aware order, you could get on with modifying
the objects you do know about ... but maybe those now have properties
you never heard of and don't know how to adjust.

Whether this is all theoretically possible is sort of moot. What
I am maintaining is that no extension author is actually going to
write such a script, indeed they probably won't trouble to write
any downgrade-like actions at all. Which makes the proposed design
mostly a foot-gun.

> But that to one side, there is clearly a problem here, and I think
> PostgreSQL ought to provide some infrastructure to help solve it, even
> if the ultimate cause of that problem is that the PostGIS folks
> managed their extension versions in some less-than-ideal way. I can't
> shake the feeling that you're just holding your breath here and hoping
> the problem goes away by itself, and judging by the responses, that
> doesn't seem like it's going to happen.

I'm not unsympathetic to the idea of trying to support multiple upgrade
paths in one script. I just don't like this particular design for that,
because it requires the extension author to make promises that nobody
is actually going to deliver on.

regards, tom lane