idle users

On Thu, Mar 05, 2009 at 04:50:09AM -0800, paulo matadr wrote:

I need to procedure to kill users in idle,anybody have this made?
thanks

See pg_cancel_backend.
http://www.postgresql.org/docs/8.3/static/functions-admin.html

- Josh / eggyknap

Can we automate this process , maintained by postmaster itself

Regards
Sathish

On Thu, Mar 5, 2009 at 10:01 PM, Joshua Tolley <eggyknap@gmail.com> wrote:

On Thu, Mar 05, 2009 at 04:50:09AM -0800, paulo matadr wrote:

I need to procedure to kill users in idle,anybody have this made?
thanks

See pg_cancel_backend.
http://www.postgresql.org/docs/8.3/static/functions-admin.html

- Josh / eggyknap

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkmv/nQACgkQRiRfCGf1UMP0uQCcCZXH4XivHZqfAWUngpPaIDct
fVUAoJkna4zTGgDzyuv4ay5UdK114Czk
=Kk08
-----END PGP SIGNATURE-----

--
BSG LeatherLink Pvt Limited,
Mail To : sathish@leatherlink.net
Website : http://www.leatherlink.net
Contact : +91 44 65191757

On Fri, 2009-03-06 at 01:09 +0530, Sathish Duraiswamy wrote:

Can we automate this process , maintained by postmaster itself

No and that would be a bad idea. There has been discussion in the past
of having an IDLE in TRANSACTION timeout but that is a different thing.

Joshua D. Drake

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

Thanks for your reply.

Can we have script in cron jobs to do this step periodically

If possible , can anyone help for cron script to do this job .

Regards
sathish

On Fri, Mar 6, 2009 at 1:21 AM, Joshua D. Drake <jd@commandprompt.com>wrote:

On Fri, 2009-03-06 at 01:09 +0530, Sathish Duraiswamy wrote:

Can we automate this process , maintained by postmaster itself

No and that would be a bad idea. There has been discussion in the past
of having an IDLE in TRANSACTION timeout but that is a different thing.

Joshua D. Drake

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

--
BSG LeatherLink Pvt Limited,
Mail To : sathish@leatherlink.net
Website : http://www.leatherlink.net
Contact : +91 44 65191757

On Thu, Mar 5, 2009 at 9:31 AM, Joshua Tolley <eggyknap@gmail.com> wrote:

On Thu, Mar 05, 2009 at 04:50:09AM -0800, paulo matadr wrote:

   I need to procedure to kill users in idle,anybody have this made?
   thanks

See pg_cancel_backend.
http://www.postgresql.org/docs/8.3/static/functions-admin.html

I just tried it and it didn't work. An idle in transaction
connection ignored it. And I ran pg_cancel_backend as a superuser,
getting back a 't' as a response.

I think you gotta kill them from outside. Unless there's a pg_kill
command I'm not familiar with. But then I'd have to make a view so I
could pg_kill(all_humans); (bender joke for those that don't get
it)

On Thu, Mar 5, 2009 at 5:50 AM, paulo matadr <saddoness@yahoo.com.br> wrote:

I need to procedure to kill users in idle,anybody have this made?
thanks
Paulo Moraes

Here's a really primitive bash script to kill off all idle
connections. Run it as postgres:

#!/bin/bash
for i in `psql -U postgres -t -c "select procpid from pg_stat_activity
where current_query like '%<IDLE> in transaction%' and current_query
not ilike '%from pg_stat_activity%';"` ;do
echo $i;
kill $i;
done;

Note that the psql -U line shouldn't be wrapped, unwrap it if you
can't see it in its original glory.

I make no promises that this thing won't eat your machine or anything
else. works on my laptop.