Date: Fri, 6 Apr 2012 11:39:38 -0400
From: Noah Misch <noah@leadboat.com>
To: Peter Eisentraut <peter_e@gmx.net>
Cc: Gianni Ciolli <gianni.ciolli@2ndquadrant.it>,
 Tom Lane <tgl@sss.pgh.pa.us>,
	Marco Nenciarini <marco.nenciarini@2ndQuadrant.it>,
	pgsql-hackers@postgresql.org
Subject: Re: [PATCH] Support for foreign keys with arrays
Message-ID: <20120406153938.GA22529@tornado.leadboat.com>
References: <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>
	<1333693277.32606.9.camel@vanquo.pezone.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1333693277.32606.9.camel@vanquo.pezone.net>
User-Agent: Mutt/1.5.18 (2008-05-17)

On Fri, Apr 06, 2012 at 09:21:17AM +0300, Peter Eisentraut wrote:
> 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.

Good points.  Your proposed naming works for me.

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

I'm ambivalent on this one.  ON DELETE CASCADE truly does the same thing
regardless of whether the FK incorporates an EACH column.  The current syntax
arises from that symmetry rather than a decision to dub its behavior as a
default.  That said, when a user wants CASCADE ELEMENT, your proposal would
more-rapidly divert him from wrongly using CASCADE ROW.

Thanks,
nm