Re: Potential issue in listExtensions()

Thanks, Magnus and Dilip.
--

Thanks & Regards,
Suraj kharage,

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

On Thu, Jun 5, 2025 at 1:34 PM Magnus Hagander <magnus(at)hagander(dot)net> wrote:

>
>
> On Thu, Jun 5, 2025 at 9:50 AM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
>> On Thu, Jun 5, 2025 at 5:53 AM Suraj Kharage
>> <suraj(dot)kharage(at)enterprisedb(dot)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.
>>
>
> Yup, agreed. Simply, the new LEFT JOIN was added on the wrong line.
>
> > ```
>> > "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.
>>
>
> LGTM as well, applied.
>
> --
> Magnus Hagander
> Me: https://www.hagander.net/ <http://www.hagander.net/>
> Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
>