Re: Add primary keys to system catalogs - Mailing list pgsql-hackers

On Mon, Jan 18, 2021, at 18:23, Tom Lane wrote:

> I realized that there's a stronger roadblock for

> treating catalog interrelationships as SQL foreign keys.  Namely,

> that we always represent no-reference situations with a zero OID,

> whereas it'd have to be NULL to look like a valid foreign-key case.

Another roadblock is perhaps the lack of foreign keys on arrays,

which would be needed by the following references:

SELECT * FROM oidjoins WHERE column_type ~ '(vector|\[\])$' ORDER BY 1,2,3;

      table_name      |  column_name   | column_type | ref_table_name | ref_column_name

----------------------+----------------+-------------+----------------+-----------------

pg_constraint        | conexclop      | oid[]       | pg_operator    | oid

pg_constraint        | conffeqop      | oid[]       | pg_operator    | oid

pg_constraint        | confkey        | int2[]      | pg_attribute   | attnum

pg_constraint        | conkey         | int2[]      | pg_attribute   | attnum

pg_constraint        | conpfeqop      | oid[]       | pg_operator    | oid

pg_constraint        | conppeqop      | oid[]       | pg_operator    | oid

pg_extension         | extconfig      | oid[]       | pg_class       | oid

pg_index             | indclass       | oidvector   | pg_opclass     | oid

pg_index             | indcollation   | oidvector   | pg_collation   | oid

pg_index             | indkey         | int2vector  | pg_attribute   | attnum

pg_partitioned_table | partattrs      | int2vector  | pg_attribute   | attnum

pg_partitioned_table | partclass      | oidvector   | pg_opclass     | oid

pg_partitioned_table | partcollation  | oidvector   | pg_collation   | oid

pg_policy            | polroles       | oid[]       | pg_authid      | oid

pg_proc              | proallargtypes | oid[]       | pg_type        | oid

pg_proc              | proargtypes    | oidvector   | pg_type        | oid

pg_statistic_ext     | stxkeys        | int2vector  | pg_attribute   | attnum

pg_trigger           | tgattr         | int2vector  | pg_attribute   | attnum

(18 rows)

I see there is an old thread with work in this area,  "Re: GSoC 2017: Foreign Key Arrays":

https://www.postgresql.org/message-id/flat/76a8d3d8-a22e-3299-7c4e-6e115dbf3762%40proxel.se#a3ddc34863465ef83adbd26022cdbcc0

The last message in the thread is from 2018-10-02:

>On Tue, 2 Oct, 2018 at 05:13:26AM +0200, Michael Paquier wrote:

>>On Sat, Aug 11, 2018 at 05:20:57AM +0200, Mark Rofail wrote:

>> I am still having problems rebasing this patch. I can not figure it out on

>> my own.

>Okay, it's been a couple of months since this last email, and nothing

>has happened, so I am marking it as returned with feedback.

>--

>Michael

Personally, I would absolutely *love* FKs on array columns.

I always feel shameful when adding array columns to tables,

when the elements are PKs in some other table.

It's convenient and often the best design,

but it feels dirty knowing there are no FKs to help detect application bugs.

Let's hope the current FKs-on-catalog-discussion can ignite the old Foreign Key Arrays thread.

/Joel