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

(I'm the developer of ZomboDB and pgrx, which while not an extension per se, allows others to make extensions that then need upgrade scripts. So this topic is interesting to me.)

> On Mar 13, 2023, at 2:48 PM, Regina Obe <lr(at)pcorp(dot)us> wrote:
>
>>> I wonder if a solution to this problem might be to provide some kind
>>> of a version map file. Let's suppose that the map file is a bunch of
>>> lines of the form X Y Z, where X, Y, and Z are version numbers. The
>>> semantics could be: we (the extension authors) promise that if you
>>> want to upgrade from X to Z, it suffices to run the script that knows
>>> how to upgrade from Y to Z.
>>> This would address Tom's concern, because if you write a master
>>> upgrade script, you have to explicitly declare the versions to which
>>> it applies.
>>
>> This would just move the problem from having 1968 files to having to write
>> 1968 lines in control files,
>
> 1968 lines in one control file, is still much nicer than 1968 files in my
> book.
> From a packaging standpoint also much cleaner, as that single file gets
> replaced with each upgrade and no need to uninstall more junk from prior
> install.

I tend to agree with this. I like this mapping file idea. Allowing an extension to declare what to use (Z) to get from X to Y is great. In theory that's not much different than having a bunch of individual files, but in practice an extension likely could pare their actual file set down to just a few, and dealing with less files is a big win. And then the mapping file would allow the extension author to make the tree as complex as necessary.

(I have some hand-wavy ideas for pgrx and autogenerating upgrade scripts and a file like this could help quite a bit. Rust and rust-adjacent things prefer explicitness)

If this "wildcard in a filename" idea existed back in 2015 when I started ZomboDB I'm not sure I'd have used it, and I'm not sure I'd want to switch to using it now. The ambiguities are too great when what I want is something that's obvious.

Primarily, ZDB purposely doesn't support downgrade paths so I wouldn't want to use a pattern that implies it does.

ZomboDB has 137 releases over the past 8 years. Each one of those adds an upgrade script from OLDVER--NEWVER. Prior to the Rust rewrite, these were all hand-generated, and sometimes were just zero bytes. I've since developed tooling to auto-generate upgrade scripts by diffing the full schemas for OLDVER and NEWVER and emitting whatever DDL can move OLDVER to NEWVER. In practice this usually generates an upgrade script that just replaces 1 function... the "zdb.version()" function. Of course, I've had to hand-edit these on occasion as well -- this is not a perfect system.

It might just be the nature of our extensions, but I don't recall ever needing DO statements in an upgrade script. The extension.sql has a few, one of which is to optionally enable PostGIS support! haha ZDB is fairly complex too. Hundreds of functions, dozens of types, an IAM implementation, a dozen views, a few tables, some operators. I also don't see a lot of changes to ZDB's extension schema nowadays -- new releases are usually fixing some terrible bug.

(As an aside, I wish Postgres would show the line number in whatever .sql file when an ERROR occurs during CREATE EXTENSION or ALTER EXTENSION UPDATE. That'd be a huge QoL improvement for me -- maybe it's my turn to put a patch together)

Just some musings from a guy hanging out here on the fringes of Postgres extension development. Of the things I've seen in this thread I really like the mapping file idea and I don't have any original thoughts on the subject.

eric