Thread: idle users

---

Veja quais são os assuntos do momento no Yahoo! + Buscados: Top 10 - Celebridades - Música - Esportes

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




--
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




--
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.