Search code examples
postgresqltransaction-isolation

with isolation level = serializable will Postgres automatically restart the failed transaction or do I have to do it myself

I run a stored procedure that goes

....
select xxxx
if xxxx !found 
   insert xxxxx
do stuff with xxx
...

clearly there is a race here. My naive expectation was that if I set the transaction isolation level correctly (serializable) then the race would be automatically solved (via a transparent restart, as other DB systems I have worked with do). This seems not to be the case.

I think I have to detect 40001 error myself and resubmit the failed transaction.

Is that correct? Are there flags I can set somewhere to say 'please do it by magic'?

Solution

  • Yes, you have to detect SQLSTATE 40001 and repeat the transaction yourself.

    The database doesn't have access to all the information required to repeat the transaction. The transaction log stores the physical changes made to the database, not the SQL statements.

    And if it was a long transaction and there has been a checkpoint since it started, PostgreSQL may not even have the transaction log any more.