Re: GSoC 2017: Foreign Key Arrays

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(dot)5162(dot)4(dot)camel%40greygoo(dot)devise-it(dot)lan#1343842863(dot)5162(dot)4(dot)camel(at)greygoo(dot)devise-it(dot)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(at)gmail(dot)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