Danger of automatic connection reset in psql

From: Oleksandr Shulgin <oleksandr(dot)shulgin(at)zalando(dot)de>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Danger of automatic connection reset in psql
Date: 2016-10-20 10:28:38
Message-ID: CACACo5SQDyL_wwcgQZQsdHM=7+Jmr=Zuq1PX3umTdkjMBby4uQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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(dot)9273(dot)8(dot)camel%40ipso(dot)snappymail(dot)ca#1097174870(dot)9273(dot)8(dot)camel(at)ipso(dot)snappymail(dot)ca
https://www.postgresql.org/message-id/flat/4BF6A496.2090106%40comgate.c
https://www.postgresql.org/message-id/flat/CAD3a31U%2BfSBsq%3Dtxw2G-D%2BfPND_UN-nSojrGyaD%2BhkYUzvxusQ%40mail.gmail.com

The 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?

--
Alex

Attachment Content-Type Size
0001-psql-stop-sending-commands-after-connection-reset.patch text/x-patch 2.2 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Rushabh Lathia 2016-10-20 10:43:33 Re: Gather Merge
Previous Message Jeevan Chalke 2016-10-20 09:38:28 Re: Aggregate Push Down - Performing aggregation on foreign server