Postgres Pro
Downloads
Home   >   mailing lists

Re: [HACKERS] GSoC 2017: Foreign Key Arrays - Mailing list pgsql-hackers

From Zhihong Yu
Subject Re: [HACKERS] GSoC 2017: Foreign Key Arrays
Date
Msg-id CALNJ-vQUFMtJ5Xz69oer5vngYKmmWmC-Fs-x6hpEYCMrdKA_9w@mail.gmail.com
Whole thread Raw
In response to Re: [HACKERS] GSoC 2017: Foreign Key Arrays  (Mark Rofail <markm.rofail@gmail.com>)
Responses Re: [HACKERS] GSoC 2017: Foreign Key Arrays
List pgsql-hackers
Tree view
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.rofail@gmail.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.rofail@gmail.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:

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.5162.4.camel%40greygoo.devise-it.lan#1343842863.5162.4.camel@greygoo.devise-it.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 please review and test as previously agreed.
 
/Mark

On Tue, Oct 2, 2018 at 7:13 AM Michael Paquier <michael@paquier.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

pgsql-hackers by date:

Previous
From: Zhihong Yu
Date:
Subject: Re: POC: postgres_fdw insert batching
Next
From: Tomas Vondra
Date:
Subject: Re: POC: postgres_fdw insert batching