From: Peter Eisentraut <peter_e@gmx.net>
To: Gianni Ciolli <gianni.ciolli@2ndquadrant.it>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Marco Nenciarini <marco.nenciarini@2ndQuadrant.it>
Cc: pgsql-hackers@postgresql.org, Noah Misch <noah@leadboat.com>
Subject: Re: [PATCH] Support for foreign keys with arrays
Date: Fri, 06 Apr 2012 09:21:17 +0300
Message-ID: <1333693277.32606.9.camel@vanquo.pezone.net> (raw)
In-Reply-To: <20120324100131.GA13561@leggeri.gi.lan>
References: <20111212004506.GB10399@tornado.leadboat.com>
<1326568728.2613.16.camel@greygoo.devise-it.lan>
<20120121204223.GA2388@tornado.leadboat.com>
<4F280EFE.1080604@2ndQuadrant.it>
<1328551482.3354.64.camel@greygoo.devise-it.lan>
<20120225020135.GA27204@tornado.leadboat.com>
<1331748188.6744.15.camel@greygoo.devise-it.lan>
<20120317033312.GB19556@tornado.leadboat.com>
<1332178899.2278.4.camel@greygoo.devise-it.lan>
<12609.1332442965@sss.pgh.pa.us>
<20120324100131.GA13561@leggeri.gi.lan>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: peter_e@gmx.net, gianni.ciolli@2ndquadrant.it, tgl@sss.pgh.pa.us, marco.nenciarini@2ndQuadrant.it, noah@leadboat.com
Subject: Re: [PATCH] Support for foreign keys with arrays
In-Reply-To: <1333693277.32606.9.camel@vanquo.pezone.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox