Re: [HACKERS] GSoC 2017: Foreign Key Arrays - Mailing list pgsql-hackers
| From | Mark Rofail |
|---|---|
| Subject | Re: [HACKERS] GSoC 2017: Foreign Key Arrays |
| Date | |
| Msg-id | CAJvoCutcMEYNFYK8Hdiui-M2y0ZGg=Be17fHgQ=8nHexZ6ft7w@mail.gmail.com Whole thread Raw |
| In response to | Re: [HACKERS] GSoC 2017: Foreign Key Arrays (Alvaro Herrera <alvherre@2ndquadrant.com>) |
| Responses |
Re: [HACKERS] GSoC 2017: Foreign Key Arrays
|
| List | pgsql-hackers |
I was unaware that there was a ver3 patch: https://www.postgresql.org/message-id/28617.1351095467%40sss.pgh.pa.us
I rebased this also (rebased version attached here).
I rebased this also (rebased version attached here).
There were considerable changes in syntax between v2 and v3, and different approaches in the implementations, so I have to restudy ri_triggers.c but at least the old patch gave me a good idea of what's going on.
As for the limitations of the patch:
- Only one "ELEMENT" column allowed in a multi-column key
- - e.g. FOREIGN KEY (c1, ELEMENT c2, ELEMENT c3) REFERENCES t1 (u1, u2, u3) will throw an error
- Supported actions:
- - NO ACTION
- - RESTRICT
- The use of count(distinct y) in the SQL statements if the referencing column is an array. Since its equality operator is different from the PK unique index equality operator this leads to a broken statement
- regression=# create table ff (f1 float8 primary key);
CREATE TABLE
regression=# create table cc (f1 numeric references ff);
CREATE TABLE
regression=# create table cc2 (f1 numeric[], foreign key(each element of f1) references ff);
ERROR: foreign key constraint "cc2_f1_fkey" cannot be implemented
DETAIL: Key column "f1" has element type numeric which does not have a default btree operator class that's compatible with class "float8_ops".
- regression=# create table ff (f1 float8 primary 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
- 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; - cross-type FKs are unsupported
These are the limitations I gathered from the previous mailing list: https://www.postgresql.org/message-id/flat/1343842863.5162.4.camel%40greygoo.devise-it.lan#1343842863.5162.4.camel@greygoo.devise-it.lan
I am pretty sure other limitations will arise.
I am confident that between the time the patch was implemented(2012) and now postgres has grown considerably, the array functions are now more robust and will help in resolving many issues.
I would like to point out that Limitation #5 is the first limitation we should eliminate as it deems the feature unbeneficial.
I would like to thank Marco Nenciarini, Gabriele, Gianni and Tom Lane, for their hard work in the previous patches and anyone else I forgot.
I am pretty sure other limitations will arise.
I am confident that between the time the patch was implemented(2012) and now postgres has grown considerably, the array functions are now more robust and will help in resolving many issues.
I would like to point out that Limitation #5 is the first limitation we should eliminate as it deems the feature unbeneficial.
I would like to thank Marco Nenciarini, Gabriele, Gianni and Tom Lane, for their hard work in the previous patches and anyone else I forgot.
As for limitations for the anyarray @> anyelem operator's limitations:
- since anyarray @< anyarray and anyarray @> anyelem have the same symbol when a statemnt like this is executed '{AAAAAAAAAA646'}' @> 'AAAAAAAAAA646' it's mapped to anyarray @< anyarray instead of anyarray @> anyelem
- but as Alexander pointed out
What I plan to do next:When types are not specified explicitly, then optimizer do its best on guessing them. Sometimes results are counterintuitive to user. But that is not bug, it's probably a room for improvement. And I don't think this improvement should be subject of this GSoC. Anyway, array FK code should use explicit type cast, and then you wouldn't meet this problem.
- located the SQL statements triggered at any insert or update and will now "convert" them to use GIN. However, NO ACTION and RESTRICT are the only actions supported right now
so that's how I will spend the next week.
Best Regards,
Mark Rofail
Mark Rofail
Attachment
pgsql-hackers by date: