Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 1B5AD14067B5
	for <pgsql-hackers@postgresql.org>;
	Sat, 10 Dec 2011 04:48:07 -0400 (AST)
Received: from delta01.pnet.xcon.it ([62.48.53.16])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <gabriele.bartolini@2ndQuadrant.it>)
	id 1RZIbG-0005VL-9p
	for pgsql-hackers@postgresql.org; Sat, 10 Dec 2011 08:48:08 +0000
Received: from localhost (localhost [127.0.0.1])
	by delta01.pnet.xcon.it (Postfix) with ESMTP id C04B310340D23;
	Sat, 10 Dec 2011 09:47:51 +0100 (CET)
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 x0nSOzHrsSAI; Sat, 10 Dec 2011 09:47:51 +0100 (CET)
Received: from Gabriele.local
	(host118-130-dynamic.52-79-r.retail.telecomitalia.it [79.52.130.118])
	(Authenticated sender: gabriele.bartolini@devise.it)
	by delta01.pnet.xcon.it (Postfix) with ESMTPSA id 54DEF10340D1C;
	Sat, 10 Dec 2011 09:47:51 +0100 (CET)
Message-ID: <4EE31CB9.90005@2ndQuadrant.it>
Date: Sat, 10 Dec 2011 09:47:53 +0100
From: Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.7;
	rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
To: Noah Misch <noah@leadboat.com>
CC: PostgreSQL-development <pgsql-hackers@postgresql.org>,
	Marco Nenciarini <marco.nenciarini@2ndquadrant.it>
Subject: Re: [PATCH] Support for foreign keys with arrays
References: <4EB3DF02.4020604@2ndQuadrant.it>
	<20111117042841.GA20517@tornado.leadboat.com>
	<4EC8CA0F.8040405@2ndQuadrant.it>
	<20111120130526.GD12013@tornado.leadboat.com>
In-Reply-To: <20111120130526.GD12013@tornado.leadboat.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201112/395
X-Sequence-Number: 199647

Hi Noah,

thanks for your feedback.

Il 20/11/11 14:05, Noah Misch ha scritto:
> What about making ON UPDATE CASCADE an error?  That way, we can say that ARRAY
> <action>  always applies to array elements, and plain<action>  always applies to
> entire rows.
>
> SET DEFAULT should now be fine to allow.  It's ARRAY SET DEFAULT, in your new
> terminology, that wouldn't make sense.

I have tried to gather your ideas with Gianni's and come to a 
compromise, which I hope you can both agree on.

The reason why I would be inclined to leave CASCADE act on rows (rather 
than array elements as Gianni suggests) is for backward compatibility 
(people that are already using referential integrity based on array 
values). For the same reason, I am not sure whether we should raise an 
error on update, but will leave this for later.

So, here is a summary:

--------------- --------- ---------
                |   ON    |   ON    |
Action         | DELETE  | UPDATE  |
--------------- --------- ---------
CASCADE        |   Row   |  Error  |
SET NULL       |   Row   |   Row   |
SET DEFAULT    |   Row   |   Row   |
ARRAY CASCADE  | Element | Element |
ARRAY SET NULL | Element | Element |
NO ACTION      |    -    |    -    |
RESTRICT       |    -    |    -    |
--------------- --------- ---------

If that's fine with you guys, Marco and I will refactor the development 
based on these assumptions.

Thanks,
Gabriele

-- 
  Gabriele Bartolini - 2ndQuadrant Italia
  PostgreSQL Training, Services and Support
  gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it