Received: from sectorbase2.sectorbase.com ([208.48.122.131]) by hub.org (8.10.1/8.10.1) with SMTP id e99NMxJ34897; Mon, 9 Oct 2000 19:22:59 -0400 (EDT) Received: by sectorbase2.sectorbase.com with Internet Mail Service (5.5.2650.21) id <4GW89TPS>; Mon, 9 Oct 2000 16:07:37 -0700 Message-ID: <8F4C99C66D04D4118F580090272A7A23018D41@SECTORBASE1> From: "Mikheev, Vadim" To: "'Rini Dutta'" , pgsql-sql@hub.org Cc: pgsql-hackers@hub.org, "Jan Wieck (E-mail)" Subject: RE: foreign key introduces unnecessary locking ? Date: Mon, 9 Oct 2000 16:11:04 -0700 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2650.21) Content-Type: text/plain; charset="iso-8859-1" X-Archive-Number: 200010/245 X-Sequence-Number: 7270 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 > When two tables (table2 and table3) have foreign keys > referring to a common table(table1), I am unable to > have 2 concurrent transactions - one performing insert > on table1 and the other on table2, when the records > being inserted have the same foreign key. > > If I use JDBC, one of the transactions aborts. > If I open 2 psql sessions and try the same, one just > waits and does not show the prompt until the other > transaction has been committed or aborted. > > For example, > create table tmp1(idx int4, data int4); > create table tmp2(idx2 int4, col2 int4, constraint > tmpcon2 foreign key(col2) references tmp1(idx)); > create table tmp3(idx3 int4, col3 int4, constraint > tmpcon3 foreign key(col3) references tmp1(idx)); > insert into tmp1 values(1, 1); > > Transaction 1 : > begin work; > insert into tmp2 values(2, 1); > > Transaction2 : > begin work; > insert into tmp3 values(3,1); > > Since such transactions are common for me, for the > time-being I have dropped the foreign key constraint. > Any ideas ? > > Rini > > __________________________________________________ > Do You Yahoo!? > Yahoo! Photos - 35mm Quality Prints, Now Get 15 Free! > http://photos.yahoo.com/ >