Potential issue in listExtensions()

From: Suraj Kharage <suraj(dot)kharage(at)enterprisedb(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, magnus(at)hagander(dot)net
Subject: Potential issue in listExtensions()
Date: 2025-06-05 05:52:39
Message-ID: CAF1DzPVBrN-cmPB2zb7ZU=2J4vEF2fNdArGCG9w+9fnKq4v8tg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

Upstream commit d696406a9b255546bc1716d07199103abd8bb785 [1] added the
support for default extension version in \dx output and changed the query
that fetches the extension list.
The changed query seems problematic and might display duplicate entries of
extension if the same object oid exists in pg_description.

If I understand correctly, after oid wraparound, it is possible that the
existing oid from another catalog might be used again for other catalog
objects as per GetNewOidWithIndex().
If this is true, then it is possible that oid exists in pg_description for
an object will be used for extension oid, and we might get a duplicate
entry in \dx output.

```

*"FROM pg_catalog.pg_extension e " "LEFT JOIN pg_catalog.pg_namespace n ON
n.oid = e.extnamespace " "LEFT JOIN pg_catalog.pg_description d ON
d.objoid = e.oid " "LEFT JOIN pg_catalog.pg_available_extensions()
ae(name, default_version, comment) ON ae.name <http://ae.name> = e.extname
" "AND d.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass\n",*
```

We may need to handle this by moving the AND condition to the left join for
pg_catalog.pg_description, so that we retrieve only pg_extension entries
from pg_description.
Attaching the patch for the same.

Thoughts?

[1]
https://www.postgresql.org/message-id/CABUevEyTMyXC6OvCWkj+rPnHrfi8_Rw_+DD_jzgFFNPqgf+Oig@mail.gmail.com
--

Thanks & Regards,
Suraj kharage,

enterprisedb.com <https://www.enterprisedb.com/>

Attachment Content-Type Size
Fix_list_extensions.patch application/octet-stream 893 bytes

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David Rowley 2025-06-05 06:49:14 Re: Question Regarding Merge Append and Parallel Execution of Index Scans on Partitioned Table
Previous Message Michael Paquier 2025-06-05 05:07:44 Re: Wrong security context for deferred triggers?