In my application, I use PostgreSQL transactions with a SERIALIZABLE isolation level and nested save points (I call them sub-transactions). Sometimes transactions or sub-transactions do not go through due to serialization errors and then I try to repeat the actions that were performed inside them.
The problem is that when I try to repeat the actions from the sub-transaction (roll back to the last savepoint, create a new savepoint and execute the actions again), postgresql does not see the changes made in other threads, and therefore all subsequent attempts fall on the same serialization error .
For clarity, I will give a simple example (somewhat curtailed compared to real actions, but sufficient to reproduce the essence of the problem). In the first thread:
create table if not exists test(id int); truncate test; begin; set isolation level serializable; savepoint p1; select id from test where id = 1; # тут могут быть любые запросы, приводящие к ошибке сериализации rollback to p1; In the second thread:
insert into test(id) values (1); In the first thread:
savepoint p2; select id from test where id = 1; The last SELECT does not see the line inserted in another thread, and returns "0 lines". As I understand it, after the first SELECT has been executed, postgresql ignores any external changes that may change its results. But I do not want to ignore them. I want to forget about everything that happened inside the canceled sub-transaction and live on. And I do not want to overfulfill the entire transaction due to an error in a small sub-transaction.
Can you please tell me if there is a way to continue the execution of the transaction with the changes made in other threads?
select id from test where id = 1;insavepoint p2should not differ from the result insavepoint p1- Sergey