Re: Duplicate tables information through metadata queries

On Wednesday, September 8, 2021, Dave Cramer <davecramer(at)postgres(dot)rocks>
wrote:

>
>
> On Wed, 8 Sept 2021 at 18:19, David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
>> On Wednesday, September 8, 2021, Andrew Dunstan <andrew(at)dunslane(dot)net>
>> wrote:
>>
>>>
>>> On 9/8/21 5:15 PM, ldh(at)laurent-hasson(dot)com wrote:
>>> >
>>> > SELECT *
>>> > FROM pg_catalog.pg_class c
>>> > LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
>>> > LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND
>>> d.objsubid = 0)
>>> > WHERE c.relname = 'contact'
>>>
>>>
>>>
>>> Umm, that doesn't look right. For queries against pg_description you
>>> need to specify the classoid (in this case 'pg_class'::regclass) as well
>>> as the objoid (and possibly the objsubid). Remember, Oids are not unique
>>> across the whole catalog. I looks to me like here one rwo is picking up
>>> a description for an entry in some other catalog
>>>
>>>
>>> See https://www.postgresql.org/docs/devel/catalog-pg-description.html
>>>
>>>
>> Doh! I knew I was forgetting something. This is indeed a bug in the
>> JDBC driver. In the query results a few messages above one is in catalog
>> 1255 and the other (correct one) is in 1259.
>>
>
> The next line in the driver is
>
> + " LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND
> dc.relname='pg_class') "
>
>
>

I cannot test right now but the observed behavior indicates that isn’t
sufficient; and I would tend to agree since the left join isn’t going to
cause rows already selected on the outer side of the join to be removed -
it will just fill in additional details for the records that do match.

David J.