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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026