From: Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>
To: 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: Tue, 27 Mar 2012 01:03:56 +1100
Message-ID: <4F70774C.4040206@2ndQuadrant.it> (raw)
In-Reply-To: <12609.1332442965@sss.pgh.pa.us>
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>
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
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: gabriele.bartolini@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: <4F70774C.4040206@2ndQuadrant.it>
* 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