Michael Fuhr <mike@fuhr.org> writes:
> I've recently been investigating long-running statements that,
> despite statement_timeout settings and pg_cancel_backend() attempts,
> remain visible in pg_stat_activity and continue to hold locks. When
> this happens, a process trace and a debugger show that the backend
> is blocked at the send() in secure_write(), netstat shows that the
> backend's send buffer is full, and a packet sniffer shows that the
> client TCP stack is sending "win 0", suggesting that the client has
> a full receive buffer because the application has stopped reading
> data.
> If the write is interrupted by a timeout or cancel, can anything
> be done here or elsewhere to abort the statement and release its
> locks?
The best thing would really be to kill the client. The backend can't
take it upon itself to interrupt the send, because that would result in
loss of protocol message sync, and without knowing how many bytes got
sent there's really no way to recover. The only escape from the backend
side would be to abort the session --- and even that's a bit problematic
since we'd probably try to issue an error message somewhere on the way
out, which isn't going to work either if the send buffer is full.
regards, tom lane
