Skip site navigation (1) Skip section navigation (2)

Full socket send buffer prevents cancel, timeout

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Full socket send buffer prevents cancel, timeout
Date: 2008-10-25 15:23:33
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
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


pgsql-hackers by date

Next:From: Scott MarloweDate: 2008-10-25 16:04:01
Subject: Re: again...
Previous:From: Ati RosseletDate: 2008-10-25 15:13:50
Subject: log : bad file dscriptor????

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group