Thread: Weired FK problem
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) #
> 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) #
> > 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, Pennsylvania19026
> -----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
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) #
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, Pennsylvania19026
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) #
> 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, Pennsylvania19026