Re: Duplicate tables information through metadata queries

On Wed, 8 Sept 2021 at 20:01, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:

>
> 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.
>
> Thanks!

fix duplicate table names by davecramer · Pull Request #2245 ·
pgjdbc/pgjdbc (github.com) <https://github.com/pgjdbc/pgjdbc/pull/2245>

I'd like to add a test case that would break otherwise,

Dave