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