Re: How to Kill IDLE users - Mailing list pgsql-general

On Tue, 2007-02-27 at 15:23, Goran Rakic wrote:
> I have installed POSTGRESQL 8.2 on W2K3 32bit , 100 users connecting from
> desktop applications and 200 users connecting thru web service from handheld
> computers
>
> I have problem with second groups of users.
>
> Often they do not disconnect from POSTGRE Server and with time passing thru
> I have lot of IDLE users and very much memory consumptions. From time to
> time that can crash server then only restarting server will kill all
> postgres.exe from memory and this annoying me, because that I build script
> to restart server every night. I could not get programmers to change
> program.

There are a few issues here, and I have a few questions.

Idle users don't normally use a lot of resources, and they certainly
don't generally use increasing resources just sitting idle.  However,
idle in transaction users (a different thing) do cause problems in that
the database cannot free up deleted tuples during vacuum.  This can
result in a bloated database store.

If your server is crashing from 100 idle users, something is wrong.

Are you running 8.2.0 or 8.2.3?  If you're running a version of 8.2 from
before 8.2.3 you should upgrade as soon as possible.

Your programmers are writing broken programs if they are leaving
connections idle in transaction.  You have my permission to beat them.
:) If they are just leaving connections idle, plain old idle, then
that's probably no big deal.

Can you run a shell script that just connects until all the connections
are used up?  Or does that crash the server?  If it crashes it, then
you've got something configured wrong, and need to either reduce the max
number of connections, or increase the resources available to the
postgresql db server.

You might wanna post a bit more info, like what things you've changed in
postgresql.conf, how much memory your machine has, and what the output
of

select * from pg_stat_activity ;

says

> Is there parameters which will disconnect IDLE users if they excided some
> time or some program which will purge memory from non active postgres.exe

The problem is it's hard to tell the difference between someone who's
just taking a long time to commit a real transaction and someone who's
forgotten that they logged in yesterday.  The general solution here is
to have a firewall that will time out idle connections after X time.
However, such a configuration can be overcome by judicious use of tcp
keepalives.

You can use something like:

select procpid, usename, now()-query_start from pg_stat_activity where
current_query like '%IDLE%' and now()-query_start >  interval '5
minutes';

to list all the users that have been idle over the interval in the
list.  Using some kind of scripting language, you could then issue kill
signals to those procpids.

Note that I'm a unix guy, so translating this to the (in my mind)
insanity that is windows is up to you. :)