Re: [PATCH] Support for foreign keys with arrays

From: Gabriele Bartolini <gabriele(dot)bartolini(at)2ndQuadrant(dot)it>
To: Noah Misch <noah(at)leadboat(dot)com>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Marco Nenciarini <marco(dot)nenciarini(at)2ndquadrant(dot)it>
Subject: Re: [PATCH] Support for foreign keys with arrays
Date: 2011-11-20 09:36:15
Message-ID: 4EC8CA0F.8040405@2ndQuadrant.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi Noah,

thanks for your unvaluable review, rich of useful and thorough comments
and notes. Marco and myself will add your proposed tests as soon as
possible (most likely after the Italian PGDay which is this week).
However, given the feedback received from other developers too
(including Tom), I would first concentrate on defining the syntax and
how referential integrity actions should work.

Il 17/11/11 05:28, Noah Misch ha scritto:
> Removing values from the array seems best to me. There's no doubt
> about what ON UPDATE CASCADE should do, and having ON DELETE CASCADE
> excise individual array elements is consistent with that. It's less
> clear for SET NULL, but I'd continue with a per-element treatment. I'd
> continue to forbid SET DEFAULT. However, Jeff Davis did expect ON
> DELETE CASCADE to remove entire rows:
> http://archives.postgresql.org/message-id/1288119207.15279.24.camel@jdavis-ux.asterdata.local
> So, perhaps the behavior needs to be user-selectable.
I would agree with what Tom is saying here, given that SQL specs do not
say anything about this feature. We could leave standard REFERENCES
keyword handling the array value as it is now. If a user wants to take
advantage of in-array referential integrity, we could implement the
special keyword "ARRAY REFERENCES" as Tom proposes (or a similar keyword).

Consequently, we need to agree on what the actions on delete and update
operations are. In case of ARRAY REFERENCES, I would be inclined to
leave the same meaning of ROW scope actions to CASCADE and SET NULL
actions, while disallowing the SET DEFAULT action (as Noah suggests
too). At the same time, I would add two actions for ARRAY REFERENCES
which will be processing array elements:

* ARRAY CASCADE
* ARRAY SET NULL

(Of course if you are welcome to propose a better naming convention).
This table summarises the scope of the actions.

--------------- --------- ---------
| ON | ON |
Action | DELETE | UPDATE |
--------------- --------- ---------
CASCADE | Row | Element |
SET NULL | Row | Row |
ARRAY CASCADE | Element | Element |
ARRAY SET NULL | Element | Element |
SET DEFAULT | Error | Error |
NO ACTION | - | - |
RESTRICT | - | - |
--------------- --------- ---------

For instance, with an "ARRAY REFERENCES ... ON DELETE CASCADE", I would
expect that the whole row is deleted (as Jeff et al. say). However, if I
specify "ARRAY REFERENCES ... ON DELETE ARRAY CASCADE", I would expect
that elements in the referencing array are removed.
Similary the "ARRAY REFERENCES ... ON DELETE SET NULL" will set the row
to NULL, whereas "ARRAY REFERENCES ... ON DELETE ARRAY SET NULL" will
set individual elements in the referencing array to NULL.

In case of updates, SET NULL and ARRAY SET NULL works the same (updating
the whole row or the single elements). CASCADE and ARRAY CASCADE are
synonyms, as they would work in individual elements (which is the action
that makes more sense anyway).

I believe that, before we proceed with one implementation or another, it
is important we discuss this sort of things and agree on a possible
long-term path (so that we can organise intermediate deliverables).

Thanks,
Gabriele

--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gabriele(dot)bartolini(at)2ndQuadrant(dot)it | www.2ndQuadrant.it

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Pavel Stehule 2011-11-20 11:16:56 Re: proposal: better support for debugging of overloaded functions
Previous Message Peter Geoghegan 2011-11-20 04:13:36 Re: Inlining comparators as a performance optimisation