Horton

Re: Potential issue in listExtensions()

Dilip Kumar <dilipbalaut@gmail.com>

View thread
From: Dilip Kumar <dilipbalaut@gmail.com>
To: Suraj Kharage <suraj.kharage@enterprisedb.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, magnus@hagander.net
Date: 2025-06-05T07:50:36Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. psql: fix order of join clauses when listing extensions

  2. psql: show default extension version in \dx output

On Thu, Jun 5, 2025 at 5:53 AM Suraj Kharage
<suraj.kharage@enterprisedb.com> wrote:
>
> 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.

Yes your understanding is correct and there is a possibility of the
bug you mentioned.

> ```
> "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 = 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?
>
Attached patch seems to be fixing the issue.



-- 
Regards,
Dilip Kumar
Google