Re: [HACKERS] Weired FK problem

Hiroshi Inoue wrote:

> > -----Original Message-----
> > From: owner-pgsql-hackers(at)postgreSQL(dot)org
> > [mailto:owner-pgsql-hackers(at)postgreSQL(dot)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(at)debis(dot)com (Jan Wieck) #