Re: Include extension path on pg_available_extensions

Hi,

On Mon, Nov 10, 2025 at 07:48:03PM -0300, Matheus Alcantara wrote:
> On Mon Nov 10, 2025 at 3:25 PM -03, Manni Wood wrote:
> > I have a small bikeshedding comment around making "location" the 4th column
> > returned for "select * from pg_available_extensions", as opposed to leaving
> > "comment" the 4th column returned for "select * from
> > pg_available_extensions".
> >
> > If a bit of software runs "select * from pg_available_extensions" and
> > fetches the contents of the 4th column, that column will return "comment"
> > for current versions of postgres but "location" for patched versions of
> > postgres.
> >
> > In many ways, this could be considered a feature and not a bug, because we
> > should be encouraged to write our SQL like so:
> >
> > select name, default_version, installed_version, comment from
> > pg_available_extensions
> >
> > and not like so:
> >
> > select * from pg_available_extensions
> >
> > I'm curious to know if this is a legitimate consideration or not.
> >
> > Also, there were no surprises when I compiled and tested this: the location
> > shows correctly for a superuser, and "<insufficient privilege>" shows
> > correctly for a non-superuser.
> >
> Good point, I think that it's a legitimate consideration. That being
> said I would get back to prefer to keep the location as the latest
> column to avoid such issues even if SELECT * is not something that users
> should do in practice, but I think that it's worth to avoid break any
> application with such change.

When the trusted column got added to the pg_availe_extensions view in
50fc694, it wasn't added to the end, but next to superuser, where it
logically makes sense IMO:

|@@ -317,7 +317,8 @@ CREATE VIEW pg_available_extensions AS
|
| CREATE VIEW pg_available_extension_versions AS
| SELECT E.name, E.version, (X.extname IS NOT NULL) AS installed,
|- E.superuser, E.relocatable, E.schema, E.requires, E.comment
|+ E.superuser, E.trusted, E.relocatable,
|+ E.schema, E.requires, E.comment
| FROM pg_available_extension_versions() AS E
| LEFT JOIN pg_extension AS X
| ON E.name = X.extname AND E.version = X.extversion;

As far as I know, Postgres does not guarantee stable system catalogs
between major versions, so I don't think users should or could rely on
stable column ordering, really.

Michael