I need someone to enlighten me!
Have this setup
create table t1 (a int4 primary key);
create table t2 (b int4 references t1 match full
on delete restrict
on update restrict);
Now I use two sessions:
(S1) insert into t1 values (1);
(S1) begin;
(S1) delete from t1 where a = 1;
(S2) insert into t2 values (1);
(S2) -- Session is now blocked
(S1) commit;
(S2) -- Bails out with the correct violation message.
Now the other way round:
(S1) insert into t1 values (1);
(S1) begin;
(S1) insert into t2 values (1);
(S2) delete from t1 where a = 1;
(S2) -- Session is now blocked
(S1) commit;
(S2) -- Session continues without error
The interesting thing is, that in both cases the trigger
procs use a
SELECT oid FROM ... FOR UPDATE ...
In the first case, where the primary key has been deleted
first, the triggers SELECT does not find the deleted row
anymore. But in the second case, the freshly inserted
referencing row doesn't show up.
Why are the visibilities different between INSERTED and
DELETED tuples?
I tried to acquire an exclusive table lock before beginning
the scan, to increment the command counter at various
different places, but nothing helped so far. The inserted row
is invisible for this trigger invocation. The next command
in the transaction can see it, but that's too late.
What state must be changed by the trigger to make it visible?
What confuses me totally is the fact, that S2 does block
already at the attempt to delete from t1, not down in the
trigger. This is because S1 executed a SELECT FOR UPDATE due
to the insertion check trigger on t2. So S2 has no active
scans or the like on the FK table at the time S2 blocks. I
think it's a general bug in the visibility code - no?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #