Re: Handling psql lost connections

On 03/29/2017 08:49 AM, Steve Crawford wrote:
> When firewalls/VPNs stand between my psql client and a remote PostgreSQL
> server the connection will on occasion time out and drop. This results
> in the following scenario:
>
> -Leave for lunch mid project - leave psql open.
>
> -Return from lunch, complete and submit large query.
>
> -Notice query is taking too long. cancel it.
>
> -Cancel doesn't return - realize that connection has dropped.
>
> -Kill psql - history is not written out. Start query from scratch.
>
> Is there:
>
> 1) A way to set psql to send keepalives?

From server side:
https://www.postgresql.org/docs/9.6/static/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SETTINGS

tcp_keepalives*

I guess you could abuse \watch:

https://www.postgresql.org/docs/9.6/static/app-psql.html

\watch [ seconds ]

Repeatedly execute the current query buffer (as \g does) until
interrupted or the query fails. Wait the specified number of seconds
(default 2) between executions. Each query result is displayed with a
header that includes the \pset title string (if any), the time as of
query start, and the delay interval.

aklaver(at)test=> \watch 2
Watch every 2s Wed Mar 29 08:59:55 2017

?column?
----------
1
(1 row)

Watch every 2s Wed Mar 29 08:59:57 2017

?column?
----------
1
(1 row)

With a larger value of seconds.

>
> 2) A way to gracefully kill psql ensuring that the history is saved?
>
> Yes, I know I and my coworkers could spend brain cycles trying to
> unerringly remember to close and restart connections, write all queries
> in an external editor and then submit them, etc. but I'm looking for
> more user friendly options.

Use the internal editor(\e)?

>
> Cheers,
> Steve

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com