From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Oleg Bartunov <oleg@sai.msu.su>
Cc: Jan Wieck <JanWieck@Yahoo.com>
Cc: Luis Sousa <llsousa@ualg.pt>
Cc: Richard Huxton <dev@archonet.com>
Cc: Filipe Bonjour <fbonjour@urbanet.ch>
Cc: pgsql-sql@postgresql.org
Subject: Re: Can arrays reference primary keys in CREATE TABLE?
Date: Fri, 23 May 2003 08:17:32 -0700 (PDT)
Message-ID: <20030523081208.D69228-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <Pine.GSO.4.55.0305231051580.28360@ra.sai.msu.su>
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).
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-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, oleg@sai.msu.su, JanWieck@Yahoo.com, llsousa@ualg.pt, dev@archonet.com, fbonjour@urbanet.ch
Subject: Re: Can arrays reference primary keys in CREATE TABLE?
In-Reply-To: <20030523081208.D69228-100000@megazone23.bigpanda.com>
* 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