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:


(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).


 Gabriele Bartolini - 2ndQuadrant Italia
 PostgreSQL Training, Services and Support
 gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to