Re: Long running query - connection and keepalives enabled but query not canceled

Tomasz Ostrowski wrote:
> I have database users which have to run some ad-hoc queries on the
> database. These queries sometimes are pretty long running, especially
> when a user messes up a join condition for two 1Grows tables and
> therefore tries to count the number of quarks in the universe or
> something like this.
>
> After a query fails to return result for some time the user sometimes
> just closes his laptop lid, dropping his VPN connection and goes home to
> watch some cats on the Internet.
>
> The TCP keepalives functionality on the database server's operating
> system would figure out that the connection is not working anymore and
> close it.
>
> You'd assume that the database would get the info and cancel the query.
> Except - it does not.
>
> The database would get an error on the connection socket only after it
> tries to read or write to it next time. But it does not try to do this -
> it's busy counting those quarks until statement_timeout is reached.

By default "tcp_keepalives_idle" is quite large: 2 hours.

Are you sure that the queries keep running for longer than that?

Try to experiment with lower settings. It will cause marginally more
network traffic, but dead connections will be detected more quickly.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com