I want to use savepoints to do subtransactions that I can rollback and retry within a transaction.
I have the following scenario when using repeatable read isolation on PostgreSQL 11.
START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SAVEPOINT x;
SELECT * FROM customer WHERE id = 1;
-- Another transaction updates this record.
-- Difficult application logic goes here to determine values for UPDATE.
UPDATE customer SET ... WHERE id = 1;
-- This fails (as expected) with a serialization error.
-- Rollback and try again.
ROLLBACK TO SAVEPOINT x;
SELECT * FROM customer WHERE id = 1;
-- Difficult application logic goes here to determine values for UPDATE.
UPDATE customer SET ... WHERE id = 1;
-- This fails unexpectedly with a serialization error.
My understanding is that rolling back to a savepoint restores the transaction to the state it was in when the savepoint was created.
I expected that I could ROLLBACK TO SAVEPOINT x
and retry the SELECT/application logic/UPDATE cycle. However the SELECT still returns the old data and I still get a serialization error even though the transaction had not seen the relevant record before the savepoint.
Of course, one could also argue that as we are still in the same transaction and we are using repeatable read isolation, the database should return the old data and thus fail the UPDATE with a serialization error.
I would like to know if this a feature or a bug? Is this behavior per the standard or is it PostgreSQL specific?