On Wednesday, September 8, 2021, Dave Cramer <davecramer@postgres.rocks> wrote:
On Wednesday, September 8, 2021, Andrew Dunstan <andrew@dunslane.net> wrote:
On 9/8/21 5:15 PM, 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
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.
