Message-ID: <1339916075.15719.38.camel@jdavis>
Subject: Re: [PATCH] Support for foreign keys with arrays
From: Jeff Davis <pgsql@j-davis.com>
To: Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>, Marco Nenciarini
	<marco.nenciarini@2ndquadrant.it>
Date: Sat, 16 Jun 2012 23:54:35 -0700
In-Reply-To: <4EB3DF02.4020604@2ndQuadrant.it>
References: <4EB3DF02.4020604@2ndQuadrant.it>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit
Mime-Version: 1.0

On Fri, 2011-11-04 at 13:48 +0100, Gabriele Bartolini wrote:
> This patch adds basic support of arrays in foreign keys, by allowing to 
> define a referencing column as an array of elements having the same type 
> as the referenced column in the referenced table.
> Every NOT NULL element in the referencing array is matched against the 
> referenced table.

I'm trying to find commonalities between this feature and my future
RANGE FOREIGN KEY feature (not past the hand-waving stage yet).

The first thing I observe is that my idea for range foreign keys is
almost the opposite of your idea for array FKs.

I was imagining a range FK to mean that the referencing side is
contained by the referenced side. This is the common definition in the
temporal world, because the valid period for the referencing row must be
within the valid period for the row it references (same for transaction
time). The referenced side must be a range, and the referencing side
must be either a range of the same type or the subtype of the range.

Other similar definitions exist by replacing "contained by" with some
other operator, though the use cases for those aren't as clear to me.

This definition works for arrays and possibly many other types
(geometry?) as well. It looks like this is orthogonal from your work,
but it does seem like it has potential for confusion in the future.

Thoughts?

Regards,
	Jeff Davis