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