Re: GSoC 2017: Foreign Key Arrays

GSoC 2017 has come to a close. These three months have proved
extremely beneficial, it was my first interaction with an open source
community and hopefully not my last.

In short, this patch allows each element in an array to act as a foreign
key, with the following syntax:
CREATE TABLE PKTABLEFORARRAY ( ptest1 int PRIMARY KEY, ptest2 text );
CREATE TABLE FKTABLEFORARRAY ( ftest1 int, FOREIGN KEY (EACH ELEMENT OF
ftest1) REFERENCES PKTABLEFORARRAY, ftest2 int );

The initial patch was written by Marco Nenciarini, Gabriele Bartolini and
Gianni Ciolli, and modified by Tom Lan.[2]

The GSoC proposal was to overcome the performance issues that appear
whenever an UPDATE/DELETE was performed on the PK table; this fires a
Referential Integrity check on the FK table using sequential scan, which is
responsible for the performance issues.
We planned on replacing the sequential scan with an indexed scan under GIN
and to do that we introduced a new operator anyarray @>> anyelem that
returns true if the element is present in the array.

I'm proud to say that we have realised our initial goal, overcoming the
performance issues produced on RI checks for Foreign Key Arrays. Outlined
here [1].
The benchmarking test showed exactly how much rewarding the use of the GIN
index has proven to be.[3]

Having accomplished the initial goals, I compiled a comprehensive
limitation check list and started to work on each limitation.
Here's a summary of Foreign Key Arrays limitations:

The limitations of the patch:

- Supported actions:
✔ UPDATE/DELETE NO ACTION
✔ UPDATE/DELETE RESTRICT
✔ DELETE CASCADE
✗ UPDATE CASCADE
✗ UPDATE/DELETE SET NULL
✗ UPDATE/DELETE SET DEFAULT

✗ Only one "ELEMENT" column allowed in a multi-column key

✗ undesirable dependency on default opclass semantics in the patch, which
is that it supposes it can use array_eq() to detect whether or not the
referencing column has changed. But I think that can be fixed without
undue pain by providing a refactored version of array_eq() that can be told
which element-comparison function to use

-- Attempted limitations
✗ presupposes that count(distinct y) has exactly the same notion of
equality that the PK unique index has. In reality, count(distinct) will
fall back to the default btree opclass for the array element type.

-- Resolved limitations

✔ fatal performance issues. If you issue any UPDATE or DELETE against the
PK table, you get a query like this for checking to see if the RI
constraint would be violated:
SELECT 1 FROM ONLY fktable x WHERE $1 = ANY (fkcol) FOR SHARE OF x;
Changed into SELECT 1 FROM ONLY fktable x WHERE $1 @> fkcol FOR SHARE OF x;

✔ coercion is now supported.
CREATE TABLE PKTABLEFORARRAY ( ptest1 int2 PRIMARY KEY, ptest2 text );
CREATE TABLE FKTABLEFORARRAY ( ftest1 int4[], FOREIGN KEY (EACH ELEMENT OF
ftest1) REFERENCES PKTABLEFORARRAY, ftest2 int );

✔ supported DELTE CASCADE action
--
------------------------------------------------------------------------------------------------------------------------

The final patch v5 is attached here.
I also attached a diff file to highlight my changes to the old rebased
patch v3

Thank you, everyone, the Postgres community for your support.

Best Regards,
Mark Rofail

--
------------------------------------------------------------------------------------------------------------------------
[1]
https://www.postgresql.org/message-id/CAJvoCuv=EeXMs7My-8AKFf1WmvXO+M_ngUEP9B=7Xaxr4EqFeg@mail.gmail.com
[2]
https://www.postgresql.org/message-id/1343842863.5162.4.camel%40greygoo.devise-it.lan
[3]
https://www.postgresql.org/message-id/CAJvoCusMuLnYZUbwTBKt%2Bp6bB9GwiTqF95OsQFHXixJj3LkxVQ%40mail.gmail.com