Re: [PATCH] Support for foreign keys with arrays - Mailing list 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.bartolini@2ndQuadrant.it
|www.2ndQuadrant.it