Received: from clio.trends.ca (root@clio.trends.ca [209.47.148.2]) by hub.org (8.10.1/8.11.0) with ESMTP id e9NGB3s15384; Mon, 23 Oct 2000 12:11:03 -0400 (EDT) (envelope-from janwieck@yahoo.com) Received: from jupiter.jw.home (IDENT:root@A922d.pppool.de [213.6.146.45]) by clio.trends.ca (8.10.2/8.10.2) with ESMTP id e9NFOIP03442; Mon, 23 Oct 2000 11:24:19 -0400 (EDT) Received: (from janwieck@localhost) by jupiter.jw.home (8.9.3/8.9.3) id HAA00859; Sun, 22 Oct 2000 07:26:12 -0500 From: Jan Wieck Message-Id: <200010221226.HAA00859@jupiter.jw.home> Subject: Re: foreign key introduces unnecessary locking ? In-Reply-To: <8F4C99C66D04D4118F580090272A7A23018D41@SECTORBASE1> from "Mikheev, Vadim" at "Oct 9, 2000 04:11:04 pm" To: "Mikheev, Vadim" Date: Sun, 22 Oct 2000 07:26:12 -0500 (EST) CC: "'Rini Dutta'" , pgsql-sql@hub.org, pgsql-hackers@hub.org, "Jan Wieck \\(E-mail\\)" X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200010/921 X-Sequence-Number: 7960 Mikheev, Vadim wrote: > Try this for both FK tables: > > create table tmp2(idx2 int4, col2 int4, constraint > tmpcon2 foreign key(col2) references tmp1(idx) INITIALLY DEFERRED); > > This will defer constraint checks till transaction commit... > though constraint triggers should use SnapshotDirty instead of > SELECT FOR UPDATE anyway. > > Did you consider this, Jan? > > Vadim Whenever the checks are done, the transaction inserting a new reference to the key must ensure that this key cannot get deleted until it is done and it's newly inserted reference is visible to others. Otherwise a referential action, preventing referenced key deletion (or other action) wouldn't see those and it would be possible to violate the constraint. I don't see any other way doing it than obtaining a lock. Using SnapshotDirty would mean, that one transaction could DELETE a reference, then another transaction removes the primary key (because using Dirty the DELETE is already visible), but now the first transaction rolls back. Voila, constraint violated. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #