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
