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

Re: Clients disconnect but query still runs

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
Cc: Greg Stark <gsstark(at)mit(dot)edu>, Jasen Betts <jasen(at)xnet(dot)co(dot)nz>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Clients disconnect but query still runs
Date: 2009-07-30 11:40:03
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
Csaba Nagy wrote:

 > A simple ping to the client would have
> cleared the fact that the client is not there anymore.

Yep. It'd also stop PostgreSQL working for clients with software 
firewalls, since most of them drop ICMP ECHO ("ping").

TCP keepalives are designed to do the same thing, but do it reliably and 
properly. Why not configure your tcp keepalive intervals instead?

Note that tcp keepalives won't kill the backend if it's in the middle of 
working on an I/O or CPU intensive query. It'll die when it tries to 
send the client data, or tries to read something more from the client.

One trick you can use if you're really worried about this is `RAISE 
NOTICE' periodically during a big complicated PL/PgSQL procedure. The 
NOTICE should be sent to the client immediately, which will cause Pg to 
notice the socket is dead. It won't help you with long running normal 
SQL queries, though.

I do think it'd be kind of nice to occasionally poke the connection to 
see if it's alive while executing a query. Doing it in a SIGALRM handler 
so as not to require the query execution path to be scattered with 
"checkConnStatus()" or whatever calls might be one approach. It'd have 
_very_ minimal cost, would only cost anything while the backend was 
working, and would help avoid wasted work when a client vanishes.

When the backend is idle (including "idle in transaction") it can 
disable any timer. It'll be poll()ing or select()ing or read()ing or 
whatever on the socket and will immediately notice if it breaks.

> I would also be
> happy to pay the cost of pinging the clients let's say once per a minute
> (or configurable interval). Considering that the connections are one to
> one with a client, it's enough to have a single timer which periodically
> signals each backend to ping it's client, but this is implementation
> details for which I have no clue how it would be best,

That applies in your situation, but for many others that's not the case. 
Not that it matters a great deal.

> the main thing
> is: I would love to have this functionality. It's extremely hard to
> secure all clients against crash, and a crash of one of the clients in
> the middle of a transaction can have very bad consequences (think
> indefinitely stucked open transaction).

Nope. Just tune your keepalives if you have hopelessly flakey clients.

Even if the client _program_ crashes, though, you shouldn't have 
anything left lying around. It's only if the client _OS_ crashes or the 
machine is hard-reset that you should be left with a backend lying 
around until tcp keepalives notice.

Craig Ringer

In response to


pgsql-general by date

Next:From: Alban HertroysDate: 2009-07-30 11:43:51
Subject: Re: org.postgresql.util.PSQLException: PANIC: could not write to log file 6
Previous:From: Dotan BarakDate: 2009-07-30 11:39:06
Subject: Re: PostgreSQL server listen on other port than 5432

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