catalogs.sgml documentation ambiguity

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

catalogs.sgml documentation ambiguity

Joel Jacobson-3
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
;