Re: [HACKERS] GSoC 2017: Foreign Key Arrays

Hi, Mark:

+ CREATE TABLE races (

Maybe 'racings' is a better name for the table (signifying the activities).

+ if (ARR_NDIM(arr) != 1 ||
+ ARR_DIMS(arr)[0] != numkeys ||
+ ARR_HASNULL(arr) ||
+ ARR_ELEMTYPE(arr) != CHAROID)
+ elog(ERROR, "confreftype is not a 1-D char array");

For the case of ARR_DIMS(arr)[0] != numkeys (while other conditions are
satisfied), maybe refine the error message mentioning numkeys so that the
user would get better idea.

+ * XXX this restriction is pretty annoying, considering the
effort
+ * that's been put into the rest of the RI mechanisms to make
them

Is the above going to be addressed in subsequent patches ?

+SplitFKColElems(List *fkcolelems, List **names, List **reftypes)

Maybe add assertion that names and reftypes are not NULL.

+ * If a foreign key, the reference semantics for each column
+ */
+ char confreftype[1];

It would be nice to expand 'semantics' a bit to make the comment clearer.
e.g. mention 'Foreign key column reference semantics codes'

Thanks

On Sat, Jan 23, 2021 at 5:37 AM Mark Rofail <markm(dot)rofail(at)gmail(dot)com> wrote:

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