Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id CFB071DB08BE for ; Mon, 26 Mar 2012 11:04:17 -0300 (ADT) Received: from delta01.pnet.xcon.it ([62.48.53.16]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SCAWs-0000AI-4L for pgsql-hackers@postgresql.org; Mon, 26 Mar 2012 14:04:16 +0000 Received: from localhost (localhost [127.0.0.1]) by delta01.pnet.xcon.it (Postfix) with ESMTP id ABC36102E091F; Mon, 26 Mar 2012 16:04:00 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at delta01.pnet.xcon.it Received: from delta01.pnet.xcon.it ([127.0.0.1]) by localhost (delta01.pnet.xcon.it [127.0.0.1]) (amavisd-new, port 10024) with LMTP id ZBwE9musnT7m; Mon, 26 Mar 2012 16:04:00 +0200 (CEST) Received: from Gabriele.local (115-64-132-168.static.tpgi.com.au [115.64.132.168]) (Authenticated sender: gabriele.bartolini@devise.it) by delta01.pnet.xcon.it (Postfix) with ESMTPSA id D4C5810355DC4; Mon, 26 Mar 2012 16:03:57 +0200 (CEST) Message-ID: <4F70774C.4040206@2ndQuadrant.it> Date: Tue, 27 Mar 2012 01:03:56 +1100 From: Gabriele Bartolini User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.7; rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2 MIME-Version: 1.0 To: Tom Lane CC: Marco Nenciarini , pgsql-hackers@postgresql.org, Noah Misch Subject: Re: [PATCH] Support for foreign keys with arrays References: <4EC8CA0F.8040405@2ndQuadrant.it> <20111120130526.GD12013@tornado.leadboat.com> <4EE31CB9.90005@2ndQuadrant.it> <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> In-Reply-To: <12609.1332442965@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------050002080800000405050802" X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201203/1551 X-Sequence-Number: 205433 This is a multi-part message in MIME format. --------------050002080800000405050802 Content-Type: text/plain; charset=ISO-8859-15; format=flowed Content-Transfer-Encoding: 7bit Hello Tom, > I started to look at this patch a bit. I'm quite confused by the fact > that some, but not all, of the possible FK action types now come in an > EACH variant. This makes no sense at all to me. ISTM that EACH is a > property of the FK constraint as a whole, that is that it says the > constraint is from array elements on the referencing side to column > values on the referenced side, rather than the normal case of column > values to column values. The specification that Gianni posted applies only to v5 of the patch. The original idea was indeed to have the whole foreign key to be defined with an EACH property (initially we were actually thinking of the ARRAY keyword following your advice, then for grammar reasons we opted for EACH). However, during the actual development we faced some difficulties with multi-column foreign keys. Through discussions on this list and with the reviewer we opted to allow the EACH keyword at column level. We started with the case where at most one column is EACH, which is easier to understand. The case of two or more EACH columns in the same foreign key has been left open for future development. > Why would the possible actions be affected, and why only these? We had to add the EACH variant to two actions (EACH CASCADE and EACH SET NULL), in order to leave users the flexibility to choose the operation to be performed in case of delete or update of one or more elements from the referenced table. Some users indeed might prefer that, in case a referenced row is deleted, the whole row is deleted (therefore they'd use the standard CASCADE action). Others mights simply require that references to that row is removed from the referencing array (therefore they'd use the variant EACH CASCADE action). The same concept applies for SET NULL (the whole array is set to NULL) and EACH SET NULL (referencing elements are set to NULL). Thank you. Cheers, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it --------------050002080800000405050802 Content-Type: text/html; charset=ISO-8859-15 Content-Transfer-Encoding: quoted-printable Hello Tom,

> I started to look at this patch a bit.=A0 I'm quite confused by the fact
> th= at some, but not all, of the possible FK action types now come in an
> EA= CH variant.=A0 This makes no sense at all to me.=A0 ISTM that EACH i= s a
> property of the FK constraint as a whole, that is that it says the
> constraint is from array elements on the referencing side to column
> va= lues on the referenced side, rather than the normal case of column
> va= lues to column values.

The specification that Gianni posted applies only to v5 of the patch.
The original idea was indeed to have the whole foreign key to be defined with an EACH property (initially we were actually thinking of the ARRAY keyword following your advice, then for grammar reasons we opted for EACH).
However= , during the actual development we faced some difficulties with multi-column foreign keys.
Through discussions on this list and with the reviewer we opted to allow the EACH keyword at column level.
We star= ted with the case where at most one column is EACH, which is easier to understand.
The case of two or more EACH columns in the same foreign key has been left open for future development.

> Wh= y would the possible actions be affected, and why only these?

We=A0 h= ad to add the EACH variant to two actions (EACH CASCADE and EACH SET=A0 NULL), in order to leave users the flexibility to choose the operation=A0 to be performed in case of delete or update of one o= r more elements from=A0 the referenced table.
Some=A0= users indeed might prefer that, in case a referenced row is deleted,=A0 the whole row is deleted (therefore they'd use the standard CASCADE=A0 action). Others mights simply require that references to that row is=A0 removed from the referencing array (therefore they'd use the variant=A0 EACH CASCADE action). The same concept applies for SET NULL (the whole=A0 array is set to NULL) and EACH SET NULL (referencing elements are set to=A0 NULL).

Thank you.

Cheers,
Gabriele
--=20
 Gabriele Bartolini - 2ndQuadrant Italia
 PostgreSQL Training, Services and Support
 gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.i=
t
--------------050002080800000405050802--