Re: [HACKERS] GSoC 2017: Foreign Key Arrays
| От | Mark Rofail |
|---|---|
| Тема | Re: [HACKERS] GSoC 2017: Foreign Key Arrays |
| Дата | |
| Msg-id | CAJvoCuuEgQ+pjv02rjvQN4o9s4_H3HJMdZdQwss2b0c0-xagEw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: [HACKERS] GSoC 2017: Foreign Key Arrays (Mark Rofail <markm.rofail@gmail.com>) |
| Ответы |
Re: [HACKERS] GSoC 2017: Foreign Key Arrays
(Mark Rofail <markm.rofail@gmail.com>)
|
| Список | pgsql-hackers |
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:
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:
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.
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
[3] https://www.postgresql.org/message-id/CAJvoCusMuLnYZUbwTBKt%2Bp6bB9GwiTqF95OsQFHXixJj3LkxVQ%40mail.gmail.com-- ------------------------------------------------------------------------------------------------------------------------
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
Вложения
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Andres FreundДата:
Сообщение: Re: [HACKERS] POC: Sharing record typmods between backends