Re: Include extension path on pg_available_extensions

On Thu, Nov 6, 2025 at 9:29 AM Matheus Alcantara <matheusssilv97(at)gmail(dot)com>
wrote:

> Thanks for reviewing this!
>
> On Sun Nov 2, 2025 at 12:11 PM -03, Michael Banck wrote:
> > On Mon, Sep 15, 2025 at 09:18:25PM -0300, Matheus Alcantara wrote:
> >> postgres=# select * from pg_available_extensions;
> >> name | default_version | installed_version |
> comment | location
> >>
> ---------+-----------------+-------------------+--------------------------------------------------+---------------------------------------------------
> >> envvar | 1.0.0 | | Get the value of a
> server environment variable |
> /usr/local/my/extensions/share/postgresql/extension
> >> amcheck | 1.5 | | functions for
> verifying relation integrity | $system
> >> bloom | 1.0 | | bloom access method -
> signature file based index | $system
> >
> > I am not sure just adding the column at the end is best, I would have
> > put it before comment so that stays last, maybe somebody else has some
> > bikeshedding input here?
> >
> Yeah, I think that it looks better to keep the comment at the end. If no
> objections I'll swap the order of "comment" and "location" columns on
> the next version.
>
> --
> Matheus Alcantara
> EDB: http://www.enterprisedb.com
>
>
>
>
Hello!

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.
--
-- Manni Wood EDB: https://www.enterprisedb.com