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

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