From: Jan Wieck <JanWieck@Yahoo.com>
To: 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: Thu, 22 May 2003 06:56:27 -0400
Message-ID: <3ECCACDB.6030008@Yahoo.com> (raw)
References: <pan.2003.05.09.21.21.24.908732@urbanet.ch>
<200305141631.48642.dev@archonet.com>
<3EC272D9.5050203@ualg.pt>
<3EC91704.1080109@Yahoo.com>
<3ECC93F7.60509@ualg.pt>
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
and loop through the arrays - but it has to select all of them for
update (resulting effectively in a full table lock, bye bye concurrency)
because otherwise it'll see an older snapshot ignoring concurrent
transactions.
What you can do, if you don't want or can't give up the array in the
first place, set up another table that holds the primary key and the
arrays single values in rows. This table is managed by INSERT, UPDATE
and DELETE triggers of the table with the array. Now put the foreign key
constraint on that table instead and you have the desired behaviour with
real referential integrity.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
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: 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: <3ECCACDB.6030008@Yahoo.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