Re: What could keep a connection / query alive?

Derrick Rice <derrick.rice@gmail.com> writes:

> I'm investigating (using 8.2) an instance of a database client
> connection remaining open in a single query well past statement
> timeout settings.? I understand that severed TCP connections can cause
> the backend to hang until the connection is closed, but our tcp
> keepalive settings should have recognized that condition after about
> 30 minutes.? The connection and backend didn't terminate for nearly 90
> minutes.
>
> What can cause this?? Why would these tcp and statement timeout settings not terminate the backend?

Try trussing the backend process.  You may find it in a network IO wait
trying to send data to a client that is hung or over a socket that was
timed out by a firewall or network equipment.

Such a condition will cause the backend to be unable to hear the
cancel.  The statement will still show as running in pg_stat_activity.

SIGTERM on such a backend will probably also fall on deaf ears.

This has been my experience several times in an environment of
EnterpriseDB 8.2 systems on Solaris 10.

YMMV
>

--
Jerry Sievers
e: gsievers19@comcast.net
p: 305.321.1144