Mime-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset=UTF-8
Date: Tue, 11 Nov 2025 08:24:56 -0300
Message-Id: <DE5TTKAEUEFC.21WEXHZJQPAYN@gmail.com>
Cc: "Manni Wood" <manni.wood@enterprisedb.com>, "Pg Hackers"
 <pgsql-hackers@postgresql.org>
Subject: Re: Include extension path on pg_available_extensions
From: "Matheus Alcantara" <matheusssilv97@gmail.com>
To: "Michael Banck" <mbanck@gmx.net>, "Matheus Alcantara"
 <matheusssilv97@gmail.com>
References: <DCTSKQAVOXIG.3A05JM1YORARK@gmail.com>
 <690774ba.170a0220.27c5b9.5e61SMTPIN_ADDED_BROKEN@mx.google.com>
 <DE1PVW3JOK3K.I4RXDSRSHBVR@gmail.com>
 <CAKWEB6p0WB_n-cVwXzE-JCxu_EVpg6oMDXVdjZ8hkz2p+NYmCA@mail.gmail.com>
 <DE5DQ1IMPT0O.SSE8M7YREHEP@gmail.com>
 <691270f8.a70a0220.2ef5c1.bdb9SMTPIN_ADDED_BROKEN@mx.google.com>
In-Reply-To: <691270f8.a70a0220.2ef5c1.bdb9SMTPIN_ADDED_BROKEN@mx.google.com>
Archived-At: 
 <https://www.postgresql.org/message-id/DE5TTKAEUEFC.21WEXHZJQPAYN%40gmail.com>
Precedence: bulk

On Mon Nov 10, 2025 at 8:10 PM -03, Michael Banck wrote:
>> > I have a small bikeshedding comment around making "location" the 4th c=
olumn
>> > returned for "select * from pg_available_extensions", as opposed to le=
aving
>> > "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 "comme=
nt"
>> > for current versions of postgres but "location" for patched versions o=
f
>> > postgres.
>> >
>> > In many ways, this could be considered a feature and not a bug, becaus=
e 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
> |=20
> | 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 =3D X.extname AND E.version =3D 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.

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