Re: GSoC 2017: Foreign Key Arrays

>
> >2) To me it was not obvious that ON DELETE CASCADE would delete
>>
> the whole rows rather than delete the members from the array, and
>> this kind of misunderstanding can lead to pretty bad surprises in
>> production. I am leaning towards not supporting CASCADE.
>>
>> I would say so too, maybe we should remove ON DELETE CASCADE until we
>> have supported all remaining actions.
>
> Delete Cascade support is now removed

> == The @>> operator
>> I would argue that allocating an array of datums and building an array
>> would have the same complexity
>>
>
> I am not sure what you mean here. Just because something has the same
> complexity does not mean there can't be major performance differences.

I have spend a lot of time working on this operator and would like to
benefit from it. How should I go about this ? Start a new patch ?

> = New stuff
>
Everything is now working correctly

== Functional review
>
> >1) MATCH FULL does not seem to care about NULLS in arrays. In the
> example below I expected both inserts into the referring table to fail.
>
>
> It seems in your example the only failed case was: INSERT INTO fk VALUES
> (NULL, '{1}');
> which shouldn't work, can you clarify this?

I think that if you use MATH FULL the query should fail if you have a NULL
> in the array.

== Code review
>
> >I think the code in RI_Initial_Check() would be cleaner if you
> used "CROSS JOIN LATERAL unnest(col)" rather than having unnest() in
> the target list. This way you would not need to rename all columns
> and the code paths for the array case could look more like the code
> path for the normal case.
>
> Can you clarify what you mean a bit more?

I think the code would look cleaner if you generate the following query:
> SELECT fk.x, fk.ys FROM ONLY t2 fk CROSS JOIN LATERAL
> pg_catalog.unnest(ys) a2 (v) LEFT OUTER JOIN ONLY t1 pk ON pk.x = fk.x AND
> pk.y = a2.v WHERE [...]
> rather than:
> SELECT fk.k1, fk.ak2 FROM (SELECT x k1, pg_catalog.unnest(ys) k2, ys ak2
> FROM ONLY t2) fk LEFT OUTER JOIN ONLY t1 pk ON pk.x = fk.k1 AND pk.y =
> fk.k2 WHERE [...]

So the two main issues we remain to resolve are MATCH FULL and the
RI_Initial_Check() query refactoring. The problem is that I am not one of
the original authors and have not touched this part of the code.
I understand the problem but it will take some time for me to understand
how to resolve everything.

Best Regards,
Mark Rofail