Re: Extensions vs PGXS' MODULE_PATHNAME handling

Tom Lane <tgl@sss.pgh.pa.us> writes:
> 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.

Makes sense.

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

Check.

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

I like this primary key because that's also the one for debian stable
distributions :)  Joking apart, aren't we missing the encoding somewhere?

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

Let's go with two views.  Once we have that it's easy enough to LEFT
JOIN if we want a summarized view.  Maybe we could even revive \dX.
Without pattern it would show the short form (pg_available_extension)
and given a pattern pg_available_extension_versions.

> 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

Agreed.

>     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

What about having this chain column be an array of version strings?  If
you want to see it this way, use array_to_string(path, '--')…

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

The ordering is not clearly apparent, but I don't think it matters.

> (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?)

Removing NULL path rows seems the best option to me.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support