Re: Duplicate tables information through metadata queries

On 9/8/21 6:45 PM, Dave Cramer wrote:
>
>
> On Wed, 8 Sept 2021 at 18:19, David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>> wrote:
>
> On Wednesday, September 8, 2021, Andrew Dunstan
> <andrew(at)dunslane(dot)net <mailto:andrew(at)dunslane(dot)net>> wrote:
>
>
> On 9/8/21 5:15 PM, ldh(at)laurent-hasson(dot)com
> <mailto: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
> <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') "
>
>  
>
>

"LEFT" here surely defeats the purpose.  Far better than to have this
clause at all would be to add " and d.classoid = 'pg_class'::regclass"
to the previous join condition.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com