Thread

Re: Include extension path on pg_available_extensions

Matheus Alcantara <matheusssilv97@gmail.com> — 2025-11-11T11:24:56Z

On Mon Nov 10, 2025 at 8:10 PM -03, Michael Banck 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.
>> >
>> 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.
>
Thanks for pointing this, I didn't know about this detail. I'm not
against swapping the orders of "comment" and "location" columns, I also
think that it would look better, I'm just afraid of breaking any
compatibility with anything, but it seems that it's not the case.

-- 
Matheus Alcantara
EDB: http://www.enterprisedb.com