Re: [HACKERS] GSoC 2017: Foreign Key Arrays

> Changelog (since the last version, v8):
Below are the versions mentioned in the changelog. v12 is the latest
version.

/Mark

On Sat, Jan 23, 2021 at 2:34 PM Mark Rofail <markm(dot)rofail(at)gmail(dot)com> wrote:

> Greetings,
>
> I am trying to revive this patch, Foreign Key Arrays. The original
> proposal from my GSoC 2017 days can be found here:
>
> https://www.postgresql.org/message-id/CAJvoCut7zELHnBSC8HrM6p-R6q-NiBN1STKhqnK5fPE-9%3DGq3g%40mail.gmail.com
>
> Disclaimer, I am not the original author of this patch, I picked up this
> patch in 2017 to migrate the original patch from 2012 and add a GIN index
> to make it usable as the performance without a GIN index is not usable
> after 100 rows.
> The original authors, Tom Lane and Marco Nenciarini, are the ones who did
> most of the heavy lifting. The original discussion can be found here:
>
> 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
>
> *In brief, it would be used as follows:*
> ```sql
> CREATE TABLE A ( atest1 int PRIMARY KEY, atest2 text );
> CREATE TABLE B ( btest1 int[], btest2 int );
> ALTER TABLE B ADD CONSTRAINT FKARRAY FOREIGN KEY (EACH ELEMENT OF
> btest1) REFERENCES A;
> ```
> and now table B references table A as follows:
> ```sql
> INSERT INTO B VALUES ('{10,1}', 2);
> ```
> where this row references rows 1 and 10 from A without the need of a
> many-to-many table
>
> *Changelog (since the last version, v8):*
> - v9 (made compatible with Postgresql 11)
> support `DeconstructFkConstraintRow`
> support `CloneFkReferencing`
> support `generate_operator_clause`
>
> - v10 (made compatible with Postgresql v12)
> support `addFkRecurseReferenced` and `addFkRecurseReferencing`
> support `CloneFkReferenced` and `CloneFkReferencing`
> migrate tests
>
> - v11(make compatible with Postgresql v13)
> drop `ConvertTriggerToFK`
> drop `select_common_type_2args` in favor of `select_common_type_from_oids`
> migrate tests
>
> - v12(made compatible with current master, 2021-01-23,
> commit a8ed6bb8f4cf259b95c1bff5da09a8f4c79dca46)
> add ELEMENT to `bare_label_keyword`
> migrate docs
>
> *Todo:*
> - re-add @>> operator which allows comparison of between array and element
> and returns true iff the element is within the array
> to allow easier select statements and lower overhead of explicitly
> creating an array within the SELECT statement.
>
> ```diff
> - SELECT * FROM B WHERE btest1 @> ARRAY[5];
> + SELECT * FROM B WHERE btest1 @>> 5;
> ```
>
> I apologize it took so long to get a new version here (years). However,
> this is not the first time I tried to migrate the patch, every time a
> different issue blocked me from doing so.
> Reviews and suggestions are most welcome, @Joel Jacobson
> <joel(at)compiler(dot)org> please review and test as previously agreed.
>
> /Mark
>
> On Tue, Oct 2, 2018 at 7:13 AM Michael Paquier <michael(at)paquier(dot)xyz>
> wrote:
>
>> On Sat, Aug 11, 2018 at 05:20:57AM +0200, Mark Rofail wrote:
>> > I am still having problems rebasing this patch. I can not figure it out
>> on
>> > my own.
>>
>> Okay, it's been a couple of months since this last email, and nothing
>> has happened, so I am marking it as returned with feedback.
>> --
>> Michael
>>
>