Re: Client-based EOFs triggering hung queries?

Christopher Opena <counterveil(at)gmail(dot)com> writes:
> Hello all,
> First time poster here - probably a good sign since I've been running
> postgresql with zero issues for the last several years! At any rate, I've
> recently run into a strange issue. Client-based EOFs are nothing new to our
> application; people can sometimes close a connection for a number of
> reasons. However, on the DB side these have always been released with no
> issue.

> Today, we had 4 connections that saw either a client-based EOF or a
> 'canceling statement due to user request', and in all of these 4 cases the
> query remained active and started chewing heavily into the cpu such that
> they ground the db server to a halt until each of the procpids were manually
> issued a 'pg_cancel_backed ( procpid );' from the console. None of the 4
> queries were particularly heavy; they registered approximately a 2300ms
> completion time using a query EXPLAIN ANALYZE. A little long, but not too
> far out of the ordinary on our usual database usage.

It's always been the case that if the client drops the connection
mid-query, the backend will notice that only when it next tries to send
some data --- and even then, if the kernel isn't aware that the far end
has dropped the connection, the kernel may accept and buffer quite a bit
of data before blocking the backend from doing more work. So if the
query requires a lot of processing before it starts to emit data, the
backend can do a lot of work before noticing anything is wrong.
I suspect that these queries were expensive and you made a mistake in
what you tested after the fact (or maybe conditions changed, eg
autovacuum came along and updated statistics). It's also possible that
some network glitch affected the connections such that the server's
kernel didn't know they were lost. It's hard to say much more than that
without a lot more evidence than you've provided. If it happens again,
you might try poking a bit harder into the situation before you kill the
queries --- netstat's opinion of the connection statuses might be
interesting, and so might stack traces from the busy backends.

regards, tom lane