Thread: Savepoint weirdness

Hi all,

Jason Godden pointed out some weird savepoint behaviour on IRC and i've
narrowed this down to a simpler case.

We see the following behaviour against HEAD:

template1=# create table foo(i int, j text);
CREATE TABLE
template1=# create unique index foo_idx on foo(i); -- not, creation of idx
CREATE INDEX
template1=# begin;
BEGIN
template1=# insert into foo values(1, 'one');
INSERT 584714 1
template1=# select * from foo;i |  j
---+-----1 | one
(1 row)

template1=# savepoint sp1;
SAVEPOINT
template1=# insert into foo values(2, 'two');
INSERT 584715 1
template1=# insert into foo values(3, 'three');
INSERT 584716 1
template1=# select * from foo;i |   j
---+-------1 | one2 | two3 | three
(3 rows)

template1=# savepoint sp2;
SAVEPOINT
template1=# update foo set j = upper(j);
UPDATE 3
template1=# select * from foo;i |   j
---+-------1 | ONE2 | TWO3 | THREE
(3 rows)

template1=# rollback to sp2;
ROLLBACK
template1=# select * from foo;i | j
---+---
(0 rows)



The rollback to sp2 should have taken us to a point where foo looked like:
i |   j
---+-------1 | one2 | two3 | three
(3 rows)

And, indeed, without an index on i, that's what we get. I've attached
output of when the index is around and not around. I've also confirmed
that other DELETE and INSERT (instead of UPDATE) after savepoint sp2 do
not cause this weirdness.

Gavin

Gavin Sherry <swm@linuxworld.com.au> writes:
> Jason Godden pointed out some weird savepoint behaviour on IRC and i've
> narrowed this down to a simpler case.

Can't reproduce it here --- I get the expected output, on two different
machines (HPUX and RHL8).  What are you testing on?  Do you see the same
problem after make distclean and rebuild?
        regards, tom lane

I wrote:
> Can't reproduce it here --- I get the expected output,

Disregard that --- I had managed to omit the create index command while
copying and pasting.

Man, that is bizarre ... the index shouldn't make any difference at all...
        regards, tom lane

Gavin Sherry <swm@linuxworld.com.au> writes:
> Jason Godden pointed out some weird savepoint behaviour on IRC and i've
> narrowed this down to a simpler case.

The answer turns out to be that GetSnapshotData is miscomputing snapshot
xmin and RecentGlobalXmin when inside a subtransaction: it omits our own
top transaction ID from the set of open transactions.  The presence of
the unique index makes a difference because in the unique-index-check
code, we check the existing rows using the bogus data, and actually end
up concluding that the original rows being updated are globally dead,
and marking them so.

I'm surprised that we didn't find this one much earlier :-(
        regards, tom lane

On Sun, 15 Aug 2004, Tom Lane wrote:

> Gavin Sherry <swm@linuxworld.com.au> writes:
> > Jason Godden pointed out some weird savepoint behaviour on IRC and i've
> > narrowed this down to a simpler case.
>
> The answer turns out to be that GetSnapshotData is miscomputing snapshot
> xmin and RecentGlobalXmin when inside a subtransaction: it omits our own
> top transaction ID from the set of open transactions.  The presence of
> the unique index makes a difference because in the unique-index-check
> code, we check the existing rows using the bogus data, and actually end
> up concluding that the original rows being updated are globally dead,
> and marking them so.

Yeah. I was scratching my head for a while wondering why a unique index
would make a difference. I was on the look out for something which screwed
up xmin but assumed it must have been within the unique check since that
is that triggered the problem for me (i'd tested delete and insert).

> I'm surprised that we didn't find this one much earlier :-(

Yeah. It came from Jason writing a proper application which used
savepoints.

Gavin