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).

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:
  1. 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
  2. Supported actions:
    •  - NO ACTION
    •  - RESTRICT
  3. 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".
  4. 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
  5. 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;
  6. 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. 

As for limitations for the anyarray @> anyelem operator's limitations:
  1. 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
On Mon, Jun 26, 2017 at 6:44 PM, Alexander Korotkov <aekorotkov@gmail.com> wrote:
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.

What I plan to do next: 
  • 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
Attachment

pgsql-hackers by date:

Previous
From: Michael Paquier
Date:
Subject: Re: [HACKERS] Error while copying a large file in pg_rewind
Next
From: Peter Eisentraut
Date:
Subject: Re: [HACKERS] Get stuck when dropping a subscription duringsynchronizing table