Re: Danger of automatic connection reset in psql

On Tue, Nov 8, 2016 at 9:53 AM, Oleksandr Shulgin
<oleksandr(dot)shulgin(at)zalando(dot)de> wrote:
> On Mon, Nov 7, 2016 at 9:31 PM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> wrote:
>>
>> On 11/4/16 4:04 AM, Oleksandr Shulgin wrote:
>>>
>>> The psql process even exits with an error code 2, which might be not
>>> that expected. We could stop reading the file and reset connection
>>> afterwards, but this is probably not that easy to achieve (think of
>>> nested \i calls).
>>
>>
>> Well, if you stop reading from the file then I don't think more \i's will
>> matter, no? I'd certainly like to see improvement here, because the
>> difference in behavior with \i is annoying.
>
>
> What I mean is you need a longjump out of the innermost \i back to the
> toplevel interactive prompt. This might be not a problem since this is what
> already happens upon receiving SIGINT, I believe.
>
>> On the bigger question of how to better protect all these cases (cut &
>> paste, etc), I think the only robust way to do that is for psql to track
>> intended transaction status itself. With the amount of parsing it's already
>> doing, maybe that wouldn't be that hard to add. It looks like this might
>> require extra libpq calls to constantly check in on server status; I'm a bit
>> surprised that result objects don't include that info.
>
>
> This doesn't have to be solely about transaction status, though for
> something immediately destructive such as DELETE or UPDATE one should expect
> a transaction guard. But for example, pasting something like the following
> two lines
>
> SET search_path = 'fancy_new_schema', 'old_boring_schema', public;
> SELECT * FROM get_item_ids_to_delete(...);
>
> can give slightly different results depending on whether the first statement
> had it effect or not. And since psql is trying to be very helpful here by
> resetting the connection, it makes it all too easy to overlook the problem.
>
> What do you think about trying to read everything we can from the terminal
> using non-blocking IO and only if that gives EWOULDBLOCK, starting the
> interactive prompt?

I think that's unlikely to be completely reliable.

How about, instead of all this, adding an option to psql to suppress
the automatic reconnect behavior? When enabled, psql just exits
instead of trying to reconnect.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company