Date: Fri, 23 May 2003 08:17:32 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Oleg Bartunov <oleg@sai.msu.su>
Cc: Jan Wieck <JanWieck@Yahoo.com>, Luis Sousa <llsousa@ualg.pt>,
	Richard Huxton <dev@archonet.com>,
	Filipe Bonjour <fbonjour@urbanet.ch>, <pgsql-sql@postgresql.org>
Subject: Re: Can arrays reference primary keys in CREATE TABLE?
In-Reply-To: <Pine.GSO.4.55.0305231051580.28360@ra.sai.msu.su>
Message-ID: <20030523081208.D69228-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Fri, 23 May 2003, Oleg Bartunov wrote:

> On Thu, 22 May 2003, Jan Wieck wrote:
>
> > Luis Sousa wrote:
> > > Jan Wieck wrote:
> > >> You used a trigger to check something, but unless you have a full set of
> > >> said triggers and they do the locking as well, you do not have
> > >> referential integrity.
> > >>
> > >>
> > >> Jan
> > >>
> > >>
> > > I guess you are right.
> > > I'll have to write a trigger for UPDATE or DELETE. How can I do the lock
> > > only on that record ?
> >
> > That exactly is the problem with arrays here. You can't index an array
> > in a way so that you can effectively select all rows who's array
> > contains a particular value. Your trigger can only select all of them
>
> there is contrib/intarray which could be used for indexed access to
> array of integers. In principle, it should be not difficult to
> generalize this module to support many types.

Yeah, that should get around locking all the rows.  It will still mean
that deadlocks are even more likely to occur with such tables than with
the normal constraint conditions however (locking the row containing one
element we're looking for effectively locks for all of those values).

As a side note, there are some other issues with making the constraint
work for non-no action constraints.  I'm not sure if SET NULL or SET
DEFAULT can be meaningfully done at all with the current array support,
nor exactly what CASCADE means for delete (does it remove the array
element or the entire row).