Обсуждение: Disconnected but query still running

Hi, list.
I've been running a query from pgAdmin for a couple of hours, just to
realize that the pgAdmin timer that counts ellapsed time had stopped,
but the status bar still said "executing". So I first thought it
could've been some kind of bug with the counter. However, after some
more time, I found that a popup telling me that the connection had
been reset (the timer had stopped almost exactly at 2 hours, 7.210.123
ms to be exact, which makes me think of a 2 hours timeout somewhere).
So I closed the dialog, and then it told me that the connection had
been reset. Ok, status was updated to connection reset, but the query
was (actually is) still running, despite the disconnection (it's still
hammering the disk, and it shows up in one of the pg_views showing
running quesries).

I've read that that a query still running after a disconnection is
normal, since the server won't realize about this disconnection until
it needs to send some data back to the client, which is ok and
understandable. But my question is, what happens when the query
finishes? FYI, the query was an update, in what would be autocommit
mode (no explicit transaction was open). Will the changes be commited?
or rollbacked because it will detect the disconnection later on? In
other words, is it worth waiting for this query to finish (it has been
running for almost 7 hours now), or should I cancel it because it will
irremediably be rolled back when the server finds theres no one on the
other end of the tcp connection?

Thanks.
Eduardo

Eduardo Piombino <drakorg@gmail.com> wrote:

> (the timer had stopped almost exactly at 2 hours, 7.210.123 ms to
> be exact, which makes me think of a 2 hours timeout somewhere).

The most likely cause of this would be that you were accessing the
server through a firewall with neither the client nor the server
configured for TCP keepalive messages, or configured to an initial
delay longer than the firewall is willing to tolerate.  The default
for initial delay in most environments is two hours, with a few
retries at much shorter intervals after that.  Or it could be that
the client was using TCP keepalive, and the network was down when it
hit the two hour mark and tried to confirm that the connection was
still good.

http://www.postgresql.org/docs/current/static/runtime-config-connection.html

http://www.postgresql.org/docs/current/static/libpq-connect.html

http://tldp.org/HOWTO/TCP-Keepalive-HOWTO/overview.html

http://superuser.com/questions/729034/any-way-to-keep-connection-alive-in-pgadmin-without-setting-it-on-the-server

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company