Horton

Re: [PATCH] Support for foreign keys with arrays

Peter Eisentraut <peter_e@gmx.net>

View thread
From: Peter Eisentraut <peter_e@gmx.net>
To: Gianni Ciolli <gianni.ciolli@2ndquadrant.it>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Marco Nenciarini <marco.nenciarini@2ndQuadrant.it>, pgsql-hackers@postgresql.org, Noah Misch <noah@leadboat.com>
Date: 2012-04-06T06:21:17Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Allow LEAKPROOF functions for better performance of security views.

  2. Improve labeling of pg_test_fsync open_sync test output.

On lör, 2012-03-24 at 10:01 +0000, Gianni Ciolli wrote:
> ON (DELETE | UPDATE) actions for EACH foreign keys
> ==================================================
> 
> ------------------ ----------- -----------
>                   |    ON     |    ON     |
> Action            |  DELETE   |  UPDATE   |
> ------------------ ----------- -----------
> CASCADE           |    Row    | Forbidden |
> SET NULL          |    Row    |    Row    |
> SET DEFAULT       |    Row    |    Row    |
> EACH CASCADE      |  Element  |  Element  |
> EACH SET NULL     |  Element  |  Element  |
> EACH SET DEFAULT  | Forbidden | Forbidden |
> NO ACTION         |     -     |     -     |
> RESTRICT          |     -     |     -     |
> ------------------ --------- -------------
> 
I took another fresh look at this feature after not having looked for a
month or two.  I think the functionality is probably OK, but I find the
interfaces somewhat poorly named.  Consider, "PostgreSQL adds EACH
foreign keys" -- huh?  I think they key word ELEMENT would be more
descriptive and precise, and it also leaves the door open to other kind
of non-atomic foreign key relationships outside of arrays.  EACH has no
relationship with arrays.  It might as well refer to each row.

On the matter of the above chart, there has been a long back and forth
about whether the row or the element case should be the default.  Both
cases are probably useful, but unfortunately you have now settled on
making maximum destruction the default.  Additionally, we would now have
the case that sometimes, depending on some configuration elsewhere, an
ON DELETE CASCADE deletes more than what was actually involved in the
foreign key.  What I'd suggest is to make both cases explicit.  That is,
forbid ON DELETE CASCADE altogether and make people write ON DELETE
CASCADE ROW or ON DELETE CASCADE ELEMENT.  In addition to making things
more explicit and safer, it would again leave the door open to other
kinds of relationships later.