RE: [HACKERS] Weired FK problem

> -----Original Message-----
> From: owner-pgsql-hackers(at)postgreSQL(dot)org
> [mailto:owner-pgsql-hackers(at)postgreSQL(dot)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(at)tpf(dot)co(dot)jp