Thread: catalogs.sgml documentation ambiguity

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

;