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

Re: Tom Lane
> The existing logic to find multi-step upgrade paths is going to make
> this a very pressing problem. For example, if you provide both
> extension--%--2.0.sql and extension--%--2.1.sql, it's not at all
> clear whether the code would try to use both of those or just one
> to get from 1.x to 2.1.

find_update_path already includes Dijkstra to avoid that problem.

> 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.

I was just talking to strk about that on #postgis and where's what I
took away from the discussion:

The main objection seems to be that 500 identical (linked) files
aren't how the extension system is supposed to be used, and that this
proposal merely changes that to a single foo--%--1.2.3.sql file which
is again not how the system is supposed to be used. Instead, we should
try to find a small set of files that just change what needs to be
changed between the versions.

First, it's 580 files because the same problem exists for 7 extensions
shipped in postgis, so we are effectively talking about 80 files per
extension.

So the question is, can we reduce that 80 to some small number. It
turns out the answer is "no" for several reasons:

* parallel branches: postgis maintains several major branches, and
people can upgrade at any time. If we currently have 3.3.4 and
3.4.0, we will likely have a 3.3.4--3.4.0.sql file. Now when 3.3.5
and 3.4.1 are released, we add 3.3.4--3.3.5 in the 3.3 branch, and a
3.4.0--3.4.1 in the 3.4 branch.

But we will also have to provide a new 3.3.5--3.4.0 (or
3.3.5--3.4.1) file *in the 3.4 branch*. That means even if we decide
that upgrades always need to go through a 3.4.0 pivot version, we
have to update the 3.4 branch each time 3.3 is updated, because
history isn't single-branch linear. Lots of extra files, which need
to be stored in a non-natural location.

If we wanted to use the "proper" update files here, we'd be left
with something like (number of major versions)*(number of all minor
versions in total) number of files, spread over several major
versions, all interconnected.

* Even if we disregard topology, we still have to provide *some*
start-updates-here file for each minor version released ever before.
Hence we are at 80. (The number might be lower for smaller
extensions like address_standardizer, but postgis itself would
change in each version, plus many of the other extensions in there.)

Christoph