Re: Is IDLE session really idle?

Hi Igor,

On Mon, Jun 15, 2009 at 01:15:30PM -0700, Igor Polishchuk wrote:

> 2. We can kill the idle sessions periodically. This will free up a big chunk
> of memory already allocated to the sessions. The application will gradually
> reestablish the connections, and the new sessions will start with small
> memory foot-print.

Don't do that. There is a race condition - if the application just
starts using the connection a millisecond after you thought it was idle
and issued the kill command, bad things might happen, depending on the
robustness of the application. It might have already validated the
connection and started the first query.

> The question is, how safe it is to kill an idle session? If a session just
> became idle in pg_stats_activity, is it possible that it is still returning
> data to the client, or doing some other useful work?

I recently figured out (on PostgreSQL 8.2.x) that an IDLE session might
still be returning query results. I wrote an application to dump whole
tables to flat files. It does a SELECT * FROM table, then streams to the
file. Access is via JDBC, results are retrieved row by row via
ResultSet.next(). I noticed that in pg_stat_activity a session switched
between IDLE and "SELECT * FROM table" command while data was being
retrieved.

HTH,

Tino.

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.lichtkreis-chemnitz.de
www.craniosacralzentrum.de