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.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
>
>     On Wednesday, September 8, 2021, Andrew Dunstan
>     <andrew@dunslane.net <mailto:andrew@dunslane.net>> wrote:
>
>
>         On 9/8/21 5:15 PM, ldh@laurent-hasson.com
>         <mailto:ldh@laurent-hasson.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