Re: Recording foreign key relationships for the system catalogs

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