pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mikheev, Vadim <vmikheev@SECTORBASE.COM>
To: 'Rini Dutta' <rinid@rocketmail.com>
To: pgsql-sql@hub.org
Cc: pgsql-hackers@hub.org, "Jan Wieck (E-mail)" <janwieck@yahoo.com>
Subject: RE: foreign key introduces unnecessary locking ?
Date: Mon, 9 Oct 2000 16:11:04 -0700
Message-ID: <8F4C99C66D04D4118F580090272A7A23018D41@SECTORBASE1> (raw)

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/
> 



view thread (6+ messages)  latest in thread

Message-ID: <8F4C99C66D04D4118F580090272A7A23018D41@SECTORBASE1>
Permalink:  ../8F4C99C66D04D4118F580090272A7A23018D41@SECTORBASE1/
Also on:    postgresql.org/message-id/8F4C99C66D04D4118F580090272A7A23018D41@SECTORBASE1

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: vmikheev@SECTORBASE.COM, rinid@rocketmail.com, pgsql-sql@hub.org, janwieck@yahoo.com
  Subject: Re: foreign key introduces unnecessary locking ?
  In-Reply-To: <8F4C99C66D04D4118F580090272A7A23018D41@SECTORBASE1>
  References: <8F4C99C66D04D4118F580090272A7A23018D41@SECTORBASE1>

* 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