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



Thu, 9 Dec 1999 20:43:17 +0100 (MET)
wieck@debis.com (Jan Wieck)
Weired FK problem
>     Why  are  the  visibilities  different  between  INSERTED and
>     DELETED tuples?

    There's something weired going on.  As far as I read the code
    in tqual.c, all changes done  by  transactions  that  started
    before  and  committed  after  my  own  transaction should be
    invisible.

    In the case that works now (PK deleted while FK is inserted),
    HeapTupleSatisfiesSnapshot()  tells,  that  the  PK  tuple is
    still alive.  But then it should be locked (for update),  the
    process  blocks,  and  when  the  deleter  commits it somehow
    magically doesn't make it into the SPI return set.

    Anyway,  this  visibility  mechanism  can  never  work   with
    referential integrity constraints.

    At  least the RI trigger procedures need some way to override
    this snapshot qualification temporary, so  the  check's  will
    see  what's  committed,  regardless  who  did  it  and when -
    committed is committed, basta.


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



Thu, 9 Dec 1999 22:08:55 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Weired FK problem
> >     Why  are  the  visibilities  different  between  INSERTED and
> >     DELETED tuples?
> 
>     There's something weired going on.  As far as I read the code
>     in tqual.c, all changes done  by  transactions  that  started
>     before  and  committed  after  my  own  transaction should be
>     invisible.
> 
>     In the case that works now (PK deleted while FK is inserted),
>     HeapTupleSatisfiesSnapshot()  tells,  that  the  PK  tuple is
>     still alive.  But then it should be locked (for update),  the
>     process  blocks,  and  when  the  deleter  commits it somehow
>     magically doesn't make it into the SPI return set.
> 
>     Anyway,  this  visibility  mechanism  can  never  work   with
>     referential integrity constraints.
> 
>     At  least the RI trigger procedures need some way to override
>     this snapshot qualification temporary, so  the  check's  will
>     see  what's  committed,  regardless  who  did  it  and when -
>     committed is committed, basta.

I stared at your first e-mail for quite some time, and couldn't figure
out what was happening.  This second e-mail clears it up.  The code:

    (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;

When S1 does the INSERT and commit, it sees the row still in T1, so the
commit works.  When the commit completes, the delete is performed.

My guess is that the T1 delete by S2 started before the S1 committed,
and that is why it doesn't see the actual insert from S1.

Maybe we can talk on IRC about this.  It looks like a tough issue, and I
don't understand most of it.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 9 Dec 1999 16:58:02 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Weired FK problem
> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Jan Wieck
> 
> >     Why  are  the  visibilities  different  between  INSERTED and
> >     DELETED tuples?
> 
>     There's something weired going on.  As far as I read the code
>     in tqual.c, all changes done  by  transactions  that  started
>     before  and  committed  after  my  own  transaction should be
>     invisible.
> 
>     In the case that works now (PK deleted while FK is inserted),
>     HeapTupleSatisfiesSnapshot()  tells,  that  the  PK  tuple is
>     still alive.  But then it should be locked (for update),  the
>     process  blocks,  and  when  the  deleter  commits it somehow
>     magically doesn't make it into the SPI return set.
> 
>     Anyway,  this  visibility  mechanism  can  never  work   with
>     referential integrity constraints.
> 
>     At  least the RI trigger procedures need some way to override
>     this snapshot qualification temporary, so  the  check's  will
>     see  what's  committed,  regardless  who  did  it  and when -
>     committed is committed, basta.
>

There's no user level method which allows to see being inserted
tuples of other backends now.
As Vadim suggested before in a discussion between you,
SnapshotDirty is needed to see uncommitted tuples of other
backends.
IIRC,duplicate index check for unique indexes is a unique case
that uses this dirty read technique currently. 

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Fri, 10 Dec 1999 08:52:11 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] Weired FK problem
Hiroshi Inoue wrote:

> > -----Original Message-----
> > From: owner-pgsql-hackers@postgreSQL.org
> > [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Jan Wieck
> >
> >     At  least the RI trigger procedures need some way to override
> >     this snapshot qualification temporary, so  the  check's  will
> >     see  what's  committed,  regardless  who  did  it  and when -
> >     committed is committed, basta.
> >
>
> There's no user level method which allows to see being inserted
> tuples of other backends now.
> As Vadim suggested before in a discussion between you,
> SnapshotDirty is needed to see uncommitted tuples of other
> backends.
> IIRC,duplicate index check for unique indexes is a unique case
> that uses this dirty read technique currently.

    Thanks  -  yes  that  was  some  issue  at the time I totally
    underestimated the entire complexity  and  (silly  as  I  am)
    thought RI could be implemented with rules.

    Anyway,  the  locking, RI triggers do internally by doing all
    their internal SELECT's with FOR UPDATE, seems to help  much.
    Actually  I'm  playing  with  another  global  bool, that the
    triggers set. It simply  causes  HeapTupleSatisfiesSnapshot()
    to  forward  the  check  into  HeapTupleSatisfiesNow(). It is
    reset on every transaction start  and  after  any  AFTER  ROW
    trigger call. So far it seems to do the job perfectly.

    What  I  found  out  so  far  is  this: The only problem, the
    locking wasn't able to catch, is the case, where an IMMEDIATE
    RESTRICT  trigger  successfully  checked,  that no references
    exist, while another transaction was inserting  exactly  that
    and still saw the PK alive. Looking up with snapshot NOW does
    the trick,  because  it  sees  anything  committed,  and  the
    locking  guarantees  that  this  lookup  is delayed until the
    other ones transaction ended.


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



Fri, 10 Dec 1999 01:27:41 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Weired FK problem
Looks like it works.  I just tried a related item:

>     Now the other way round:
> 
>     (S1)    insert into t1 values (1);
>     (S1)    begin;

>     (S2)    delete from t1 where a = 1;
>     (S1)    insert into t2 values (1);

I swapped the above two items, and the INSERT properly failed the
contraint.

> 
>     (S2)    -- Session is now blocked
> 
>     (S1)    commit;
> 
>     (S2)    -- Session continues without error

I was a little unsure how trigger visibility was going to handle cases
where the constraint failure happened after the other transaction
started, but it seems to work fine.

It is only the trigger that has full visibility, not the statements in
the query, right?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 10 Dec 1999 14:17:28 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Weired FK problem
Bruce Momjian wrote:

>
> Looks like it works.  I just tried a related item:
>
> >     Now the other way round:
> >
> >     (S1)    insert into t1 values (1);
> >     (S1)    begin;
>
> >     (S2)    delete from t1 where a = 1;
> >     (S1)    insert into t2 values (1);
>
> I swapped the above two items, and the INSERT properly failed the
> contraint.
>
> >
> >     (S2)    -- Session is now blocked
> >
> >     (S1)    commit;
> >
> >     (S2)    -- Session continues without error
>
> I was a little unsure how trigger visibility was going to handle cases
> where the constraint failure happened after the other transaction
> started, but it seems to work fine.

    I  already committed the visibility overriding by RI triggers
    for time qualification. Maybe you're seeing  the  results  of
    this little hack.

> It is only the trigger that has full visibility, not the statements in
> the query, right?

    That's  the  behaviour  I  wanted to get from it. RI triggers
    need to see what's committed and what their  own  transaction
    did so far. That's HeapTupleSatisfiesNow().

    Since they lock everything they access, they simply force the
    old (pre MVCC) behaviour - wait if something is  actually  in
    use  until the other transaction ends. No snapshots, no pain.


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



Fri, 10 Dec 1999 20:41:50 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Weired FK problem
>     That's  the  behaviour  I  wanted to get from it. RI triggers
>     need to see what's committed and what their  own  transaction
>     did so far. That's HeapTupleSatisfiesNow().
> 
>     Since they lock everything they access, they simply force the
>     old (pre MVCC) behaviour - wait if something is  actually  in
>     use  until the other transaction ends. No snapshots, no pain.

Sounds good.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 10 Dec 1999 17:23:38 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Weired FK problem