Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 856AA723ED9
	for <pgsql-hackers@postgresql.org>;
	Sun, 20 Nov 2011 09:05:42 -0400 (AST)
Received: from mail-vx0-f174.google.com ([209.85.220.174])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <noah@leadboat.com>) id 1RS75Z-0004kh-Nr
	for pgsql-hackers@postgresql.org; Sun, 20 Nov 2011 13:05:42 +0000
Received: by vcbfk1 with SMTP id fk1so4730500vcb.19
	for <pgsql-hackers@postgresql.org>;
	Sun, 20 Nov 2011 05:05:29 -0800 (PST)
Received: by 10.52.34.211 with SMTP id b19mr11112262vdj.112.1321794329123;
	Sun, 20 Nov 2011 05:05:29 -0800 (PST)
Received: from tornado.leadboat.com (ip68-230-222-48.rd.hr.cox.net.
	[68.230.222.48])
	by mx.google.com with ESMTPS id k4sm9766443vdu.2.2011.11.20.05.05.27
	(version=SSLv3 cipher=OTHER); Sun, 20 Nov 2011 05:05:28 -0800 (PST)
Date: Sun, 20 Nov 2011 08:05:26 -0500
From: Noah Misch <noah@leadboat.com>
To: Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>,
	Marco Nenciarini <marco.nenciarini@2ndquadrant.it>
Subject: Re: [PATCH] Support for foreign keys with arrays
Message-ID: <20111120130526.GD12013@tornado.leadboat.com>
References: <4EB3DF02.4020604@2ndQuadrant.it>
	<20111117042841.GA20517@tornado.leadboat.com>
	<4EC8CA0F.8040405@2ndQuadrant.it>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4EC8CA0F.8040405@2ndQuadrant.it>
User-Agent: Mutt/1.5.18 (2008-05-17)
X-Archive-Number: 201111/1204
X-Sequence-Number: 198689

On Sun, Nov 20, 2011 at 10:36:15AM +0100, Gabriele Bartolini wrote:
> I would agree with what Tom is saying here, given that SQL specs do not  
> say anything about this feature. We could leave standard REFERENCES  
> keyword handling the array value as it is now. If a user wants to take  
> advantage of in-array referential integrity, we could implement the  
> special keyword "ARRAY REFERENCES" as Tom proposes (or a similar 
> keyword).

No objection to that.

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

I like this.

> CASCADE and ARRAY CASCADE are  
> synonyms, as they would work in individual elements (which is the action  
> that makes more sense anyway).

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.

Thanks,
nm