Обсуждение: RE: [HACKERS] foreign key introduces unnecessary locking ?

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

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
possibleto 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 #