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


> 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


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

Dimitri Fontaine     PostgreSQL : Expertise, Formation et Support

Sent via pgsql-hackers mailing list (
To make changes to your subscription:

Reply via email to