Re: Duplicate tables information through metadata queries

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') "

Dave

>
> David J.
>
>