Weired FK problem

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