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