Re: Extensions vs PGXS' MODULE_PATHNAME handling

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> Also, I've been looking at the pg_available_extensions issue a bit.
>> I don't yet have a proposal for exactly how we ought to redefine it,
>> but I did notice that the existing code is terribly confused by
>> secondary control files: it doesn't realize that they're not primary
>> control files, so you get e.g. hstore and hstore-1.0 as separate
>> listings.

> I'd think that's it's a good idea if dealt with "correctly" because now
> that ALTER EXTENSION UPDATE can deal with more than one target VERSION
> I expect the view to show each available update here.

Thinking about this some more ... it seems like we now need two separate
views, because there is some information that could change per-version,
and some that really only makes sense at the per-extension level.

For instance, we could have pg_available_extensions that produces a row
per primary control file, with columns

name (view's effective primary key)
default_version
installed_version (NULL if not installed)
comment (if one is present in primary control file)

and pg_available_extension_versions that produces a row per install
script, with columns

name
version ((name, version) is primary key)
comment
requires
relocatable
schema

where the last four columns can vary across versions due to secondary
control files.

Or we could combine these into just one view with pkey (name, version),
but then the default_version and installed_version columns would be the
same across all rows with the same extension name, which seems confusing
and unnormalized.

> If possible adding the "update chain sequence" information as computed
> in the code would be great. Because we can't ask people to figure that
> out all by themselves, the best way to check your upgrading setup is
> fine would be to run SELECT * FROM pg_available_extensions; and read the
> result.

I think this is probably a good thing to provide but it shouldn't go in
either of the above views, on two grounds: (1) it's going to be
relatively expensive to compute, and most people won't need it; (2)
the views could only sensibly cover paths from current version to listed
version, which isn't good enough. What an extension author actually
wants to know is "have I introduced any undesirable update paths
anywhere?" I suggest instead that we invent a SRF, say
pg_extension_update_paths(extension_name text) returns setof record,
that returns a row for each pair of distinct version names found in
the extension's install and update scripts, with columns

source version name
target other version name
path update path from source to target, or NULL if none

The output might look like this:

1.0 1.1 1.0--1.1
1.1 1.2 1.1--1.2
unpackaged 1.0 unpackaged--1.0
1.0 1.2 1.0--1.1--1.2
1.0 unpackaged
1.1 1.0
1.1 unpackaged
1.2 1.1
1.2 1.0
1.2 unpackaged
unpackaged 1.1 unpackaged--1.0--1.1
unpackaged 1.2 unpackaged--1.0--1.1--1.2

where the first three rows correspond to available update scripts and
the rest are synthesized.

(Looking at this, it looks like it could get pretty bulky pretty
quickly. Maybe we should eliminate all rows in which the path would be
NULL? Or just eliminate rows in which the target doesn't have an
install script, which would remove the three rows with target =
unpackaged in the above example?)

Thoughts?

regards, tom lane