Hi Hackers!
When using psql interactively one might be tempted to guard potentially destructive commands such as "UPDATE / DELETE / DROP <object>" by starting the input line with an explicit "BEGIN; ...". This has the added benefit that then you invoke the command by reverse-searching the command history, you get it together with the guarding transaction open statement.
This, however, is not 100% safe as I've found out a few days ago. Should the connection to the server get lost, the first of semicolon-separated statements, "BEGIN;" will only trigger connection reset, and if that is successful the following command(s) are going to be executed on the newly opened connection, but without the transaction guard.
I'm not the first one to discover that, a search in archives gives at least 3 results:
https://www.postgresql.org/message-id/flat/1097174870.9273.8.camel%40ipso.snappymail.ca#1097174870.9273.8.camel@ipso.snappymail.cahttps://www.postgresql.org/message-id/flat/4BF6A496.2090106%40comgate.chttps://www.postgresql.org/message-id/flat/CAD3a31U%2BfSBsq%3Dtxw2G-D%2BfPND_UN-nSojrGyaD%2BhkYUzvxusQ%40mail.gmail.comThe second one even resulted in a TODO item:
Prevent psql from sending remaining single-line multi-statement queries
after reconnection
I was thinking that simply adding a bool flag in the pset struct, to indicate that connection was reset during attempt to execute the last query would do the trick, but it only helps in exactly the case described above.
Since this is already an improvement, I'm attaching a patch.
If on the other hand, someone is pasting into psql's terminal a block of commands enclosed in BEGIN/COMMIT, the same bug is triggered: BEGIN doesn't have effect and the rest of commands run outside of transaction.
Is it possible at all to protect against the latter case? How?
