Some catalog tables have references to pg_attribute.attnum.
In the documentation, it only says "(references pg_attribute.attnum)"
but not which oid column to include in the two-column "foreign key".
This would not be a problem if there would only be one reference to
pg_class.oid,
but some catalog tables have multiple columns that references pg_class.oid.
For instance, pg_constraint has two columns (conkey, confkey) referencing
pg_attribute,
and three columns (conrelid, conindid, confrelid) referencing pg_class.
A user might wonder:
- Which one of these three columns should be used in combination with the
conkey/confkey elements to join pg_attribute?
If we would have array foreign key support, I would guess the "foreign keys"
should be:
FOREIGN KEY (confrelid, EACH ELEMENT OF confkey) REFERENCES
pg_catalog.pg_attribute (attrelid, attnum)
FOREIGN KEY (conrelid, EACH ELEMENT OF conkey) REFERENCES
pg_catalog.pg_attribute (attrelid, attnum)
It's of course harder to guess for a machine though, which would need a
separate human-produced lookup-table.
Could it be meaningful to clarify these multi-key relations in the
documentation?
As a bonus, machines could then parse the information out of catalogs.sgml.
Here is a list of catalogs referencing pg_attribute and with multiple pg_class
references:
table_name | array_agg
----------------------+---------------------------------------
pg_constraint | {confrelid,conindid,conrelid}
pg_index | {indexrelid,indrelid}
pg_partitioned_table | {partdefid,partrelid}
pg_trigger | {tgconstrindid,tgconstrrelid,tgrelid}
(4 rows)
Produced using query:
SELECT b.table_name, array_agg(DISTINCT b.column_name)
FROM pit.oid_joins AS a
JOIN pit.oid_joins AS b
ON b.table_name = a.table_name
WHERE a.ref_table_name = 'pg_attribute'
AND b.ref_table_name = 'pg_class'
GROUP BY b.table_name
HAVING cardinality(array_agg(DISTINCT b.column_name)) > 1
;
