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
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
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.