Hi Mark,
On Mon, Feb 8, 2021, at 09:40, Mark Rofail wrote:
>Attachments:
>fk_arrays_elem_v1.patch
>anyarray_anyelement_operators-v1.patch
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