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 #