Does PostgreSQL ever retry failed transactions internally?

Question

I am writing an application in Python and PostgreSQL (using psycopg2) and in one section we are using the following pattern.

As separate python commands which I assume translates to separate calls to PostgreSQL

1) BEGIN

2) create temporary table AAA as select ... from ...

3) create temporary table BBB as select ... from AAA

4) create temporary table CCC as select ... from BBB

5) insert into YYY select ... from CCC

6) END

At no point do we ever read any data back from the transaction making it a blind write. If this series of statements would cause a conflict with another transaction and fail would PostgreSQL restart it internally or would that revert to me the same as if I was mixing in select statements into the above code.

Would it make a difference if I converted all the temporary statements to common table expressions using the "with" construct? From the perspective of needing to do manual transaction restarts that is.

At what point if ever does PostgreSQL ever restart transactions internally, does multiple statements like that work? Will it work for a single statement as with "with"? Will it never work?

ps. I can not release the full code as it apart from being several pages long is the property of my employer.

• user1822: There won't ever be a conflict as temporary tables are always "local" to a connection. You can have hundreds of concurrent connections (transactions) all creating temp tables with the same name. But in general I would avoid the usage of temp tables. Just rewrite that as a single query. Most of the time that will be faster
• user1822: If you have an error during your transaction, you will always have to rollback it. Postgres does not "retry" anything.
• lijat: @a_horse_with_no_name the conflicts would be expected to come at the end when doing an insert into a non temporary table
• user1822: In that case you'll get an error and have to rollback the transaction

Answer 1

PostgreSQL does not retry transactions. It retains no memory of the SQL statements that were previously issued. You must retry the transactions yourself.

• lijat: I like this answer, it would be improved further if you fleshed it out or provided a source for this. Is that something you can do?
• Laurenz Albe: There is not a single place in the documentation that spells that out, and it is also difficult to pinpoint the non-existence of a certain functionality in the source code. Perhaps you can explain what you mean by a "conflict" - I thought you were referring to a serialization failure.
• lijat: Yes that is what I was reffering to. Specifically one where no read data has left the database.

Answer 2

AFAIK PostgreSQL does not retry failed transaction. Even if this would be the case you can never be 100% sure your transaction will succeed: coding your application with any DBMS assuming transaction will never fail in the database would be wrong.

the proof is in the pudding

I know that other database may in some very special cases retry statement (not transaction): for example Oracle see https://asktom.oracle.com/pls/asktom/f?p=100:11:0%3a%3a%3a%3aP11_QUESTION_ID:11504247549852#3950826900346316085.

• lijat: I agree that there is no absolute certainty and that to be entirely robust I would need to check myself, if so only to protect against coding errors. But that might come down to a cost benefit analysis of if it is worth the effort given the risk of disaster if this fails. +1 for the links especially the oracle one.