Re: [HACKERS] GSoC 2017: Foreign Key Arrays

Hi Mark,

On Mon, Feb 8, 2021, at 09:40, Mark Rofail wrote:
>Attachments:
>fk_arrays_elem_v1.patch
>anyarray_anyelement_operators-v1.patch

Nice work!

I have successfully tested both patches against e7f42914854926c2afbb89b9cd0e381fd90766be
by cloning all pg_catalog tables, and adding foreign keys
on all columns, including array columns of course.

Here is what e.g. pg_constraint which has quite a few array oid columns looks like with foreign keys:

joel=# \d catalog_clone.pg_constraint
Table "catalog_clone.pg_constraint"
Column | Type | Collation | Nullable | Default
---------------+------------+-----------+----------+---------
oid | jsonb | | not null |
conname | name | | |
.
.
.
Foreign-key constraints:
"pg_constraint_conexclop_fkey" FOREIGN KEY (EACH ELEMENT OF conexclop) REFERENCES catalog_clone.pg_operator(oid)
"pg_constraint_conffeqop_fkey" FOREIGN KEY (EACH ELEMENT OF conffeqop) REFERENCES catalog_clone.pg_operator(oid)
"pg_constraint_conpfeqop_fkey" FOREIGN KEY (EACH ELEMENT OF conpfeqop) REFERENCES catalog_clone.pg_operator(oid)
"pg_constraint_conppeqop_fkey" FOREIGN KEY (EACH ELEMENT OF conppeqop) REFERENCES catalog_clone.pg_operator(oid)
"pg_constraint_conrelid_conkey_fkey" FOREIGN KEY (conrelid, EACH ELEMENT OF conkey) REFERENCES catalog_clone.pg_attribute(attrelid, attnum)

Here is my test function that adds foreign keys on catalog tables:

https://github.com/truthly/pg-pit/blob/master/FUNCTIONS/test_referential_integrity.sql

If you want to try it yourself, it is run as part of pit's test suite:

$ git clone https://github.com/truthly/pg-pit.git
$ cd pg-pit
$ make
$ make install
$ make installcheck

============== running regression test queries ==============
test referential_integrity ... ok 1925 ms

/Joel