Re: Recording foreign key relationships for the system catalogs - Mailing list pgsql-hackers

From Joel Jacobson
Subject Re: Recording foreign key relationships for the system catalogs
Date
Msg-id 2f7b0a46-d086-421a-9f72-0c1af3aff3a7@www.fastmail.com
Whole thread Raw
In response to Re: Recording foreign key relationships for the system catalogs  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Recording foreign key relationships for the system catalogs
List pgsql-hackers
On Tue, Feb 2, 2021, at 04:27, Tom Lane wrote:
>Attachments:
>add-catalog-foreign-key-info-2.patch

Very nice.

I could only find one minor error,
found by running the regression-tests,
and then using the query below to compare "is_opt"
with my own "zero_values" in my tool
that derives its value from pg_catalog content.

--
-- Are there any observed oid columns with zero values
-- that are also marked as NOT is_opt by pg_get_catalog_foreign_keys()?
--
regression=# SELECT
  table_name,
  column_name
FROM pit.oid_columns
WHERE zero_values
INTERSECT
SELECT
  fktable::text,
  unnest(fkcols)
FROM pg_get_catalog_foreign_keys()
WHERE NOT is_opt;

Expected to return no rows but:

  table_name   | column_name
---------------+-------------
pg_constraint | confrelid
(1 row)

regression=# SELECT * FROM pg_get_catalog_foreign_keys() WHERE 'confrelid' = ANY(fkcols);
    fktable    |       fkcols        |   pktable    |      pkcols       | is_array | is_opt
---------------+---------------------+--------------+-------------------+----------+--------
pg_constraint | {confrelid}         | pg_class     | {oid}             | f        | t
pg_constraint | {confrelid,confkey} | pg_attribute | {attrelid,attnum} | t        | f
(2 rows)

Reading the new documentation, I interpret "is_opt=false" to be a negation of

   "the referencing column(s) are allowed to contain zeroes instead of a valid reference"

i.e. that none of the referencing columns (fkcols) are allowed to contain zeroes,
but since "confrelid" apparently can contain zeroes:

regression=# select * from pg_constraint where confrelid = 0 limit 1;
-[ RECORD 1 ]-+------------------
oid           | 12111
conname       | pg_proc_oid_index
connamespace  | 11
contype       | p
condeferrable | f
condeferred   | f
convalidated  | t
conrelid      | 1255
contypid      | 0
conindid      | 2690
conparentid   | 0
confrelid     | 0
confupdtype   |
confdeltype   |
confmatchtype |
conislocal    | t
coninhcount   | 0
connoinherit  | t
conkey        | {1}
confkey       |
confreftype   |
conpfeqop     |
conppeqop     |
conffeqop     |
conexclop     |
conbin        |

I therefore think is_opt should be changed to true for this row:
    fktable    |       fkcols        |   pktable    |      pkcols       | is_array | is_opt
---------------+---------------------+--------------+-------------------+----------+--------
pg_constraint | {confrelid,confkey} | pg_attribute | {attrelid,attnum} | t        | f

If this is fixed, I also agree this is ready to be committed.

/Joel

pgsql-hackers by date:

Previous
From: "tsunakawa.takay@fujitsu.com"
Date:
Subject: RE: [POC] Fast COPY FROM command for the table with foreign partitions
Next
From: Greg Nancarrow
Date:
Subject: Re: Parallel INSERT (INTO ... SELECT ...)