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. Unfortunately we have limited ability to continue the
investigation at the client.
Here's an excerpt from internal_flush():
while (bufptr < bufend) { int r;
r = secure_write(MyProcPort, bufptr, bufend - bufptr);
if (r <= 0) { if (errno == EINTR) continue; /* Ok if we were interrupted */
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? I realize that the full send buffer complicates the matter
because of the inability to send any more data to the client, but
I'm wondering if the backend can do anything to get rid of statements
from such misbehaving applications. We've reluctantly tried SIGTERM
but that doesn't work either. SIGQUIT and SIGABRT would kill the
entire backend.
Since these statements won't go away, they hold locks that can block
other transactions and they cause vacuum to leave behind dead rows
that it could otherwise clean up. I noticed this problem in 8.1.14
but I can reproduce it in later versions as well. I can provide a
test case if anybody's interested (it's easy: use PQsendQuery() to
execute a query that returns enough data to fill both the client's
and the server's socket buffers, then go to sleep without reading
the response).
--
Michael Fuhr