Re: Extensions vs PGXS' MODULE_PATHNAME handling

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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