X-Original-To: pgsql-sql@postgresql.org Received: from localhost (unknown [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 4A52C9235D0 for ; Thu, 22 May 2003 10:44:50 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 46255-10 for ; Thu, 22 May 2003 10:44:17 -0400 (EDT) Received: from europa.janwieck.net (h00045a2e4e7c.ne.client2.attbi.com [24.131.181.55]) by developer.postgresql.org (Postfix) with ESMTP id 4C8A0925E90 for ; Thu, 22 May 2003 06:57:50 -0400 (EDT) Received: from Yahoo.com (linksys.janwieck.net [192.168.192.1]) (authenticated) by europa.janwieck.net (8.11.6/8.11.6) with ESMTP id h4MAv0a22785; Thu, 22 May 2003 06:57:08 -0400 Message-ID: <3ECCACDB.6030008@Yahoo.com> Date: Thu, 22 May 2003 06:56:27 -0400 From: Jan Wieck Organization: Unorganized User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.0.2) Gecko/20030208 Netscape/7.02 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Luis Sousa Cc: Richard Huxton , Filipe Bonjour , pgsql-sql@postgresql.org Subject: Re: Can arrays reference primary keys in CREATE TABLE? References: <200305141631.48642.dev@archonet.com> <3EC272D9.5050203@ualg.pt> <3EC91704.1080109@Yahoo.com> <3ECC93F7.60509@ualg.pt> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/415 X-Sequence-Number: 13580 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 #