Hi, Thank you. Fascinating query!
Aside from those 9 lines that are generated from my own query, I get a bunch of lines that indicate ongling/live query, with all the same pid. When I examine processes from unix side (via 'ps -ef) I see the same process (with ppid of postgres) running for 2+ days. But question still remains in my mind: is this a strayed process or is this simply a long running (and meaningful) process? Regards, Tena Sakai [EMAIL PROTECTED] -----Original Message----- From: brahma tiwari [mailto:[EMAIL PROTECTED] Sent: Fri 12/14/2007 4:21 AM To: Tena Sakai Subject: Re: [ADMIN] reading pg_stat_activity view HI, Plz run below qry u can detect the no of locks and after that chose them and kill or abort the process (By default 9 process will be there means ideal state/no locks) SELECT dbu.usename as locker, l.mode as locktype, pg_stat_get_backend_pid(S.backendid) as pid, db.datname||'..'||n.nspname||'.'||r.relname as relation, l.mode, pg_stat_get_backend_activity(S.backendid ) as query FROM pg_user dbu, (SELECT pg_stat_get_backend_idset() AS backendid) AS S, pg_database db, pg_locks l, pg_class r, pg_namespace n WHERE db.oid = pg_stat_get_backend_dbid(S.backendid) AND dbu.usesysid = pg_stat_get_backend_userid(S.backendid) AND l.pid = pg_stat_get_backend_pid(S.backendid) AND l.relation = r.oid AND l.database = db.oid AND r.relnamespace = n.oid AND l.granted ORDER BY locktype, db.datname, n.nspname, r.relname, l.mode; Prakash Noida,India ----- Original Message ---- From: Tena Sakai <[EMAIL PROTECTED]> To: Scott Marlowe <[EMAIL PROTECTED]> Cc: pgsql-admin@postgresql.org Sent: Friday, 14 December, 2007 4:09:18 AM Subject: Re: [ADMIN] reading pg_stat_activity view Hi Scott, Thanks for the sql statement with pg_stat_activity. > You can set the value for statement_timeout and any statement > that takes over that amount of time will generate a timeout and you > then log it in the logs. I like this idea, but according to what I read, it looks like the query will be aborted if it goes beyond the time specified, which is not what I want. There wouldn't be any way to just log it and still keep the query alive, would there? Is a scenario like below possible/probable/likely? A user starts a query, after a few minutes decides to abort it via control-C, he/she gets a notion that it is aborted, but as far as postgres is concerned the query keeps running for days and days? Is there any way to detect such has indeed taken place? Regards, Tena Sakai [EMAIL PROTECTED] -----Original Message----- From: [EMAIL PROTECTED] on behalf of Scott Marlowe Sent: Thu 12/13/2007 1:57 PM To: Tena Sakai Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] reading pg_stat_activity view On Dec 13, 2007 3:43 PM, Tena Sakai <[EMAIL PROTECTED]> wrote: > > My next question: > What would be a good way to tell if some > query is hung? That would really depend on what you mean by hung. Just running a really long time, or waiting for a lock that some other session is not will to commit / rollback like above? I take it you just mean long running queries. You can do a couple of things. You can set the value for statement_timeout and any statement that takes over that amount of time will generate a timeout and you then log it in the logs. You can use pg_stat_activity to see how long a query's been running. Something like select datname, usename, current_query, waiting, now() - query_start from pg_stat_activity order by query_start; can show you how long each query has been running. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster Why delete messages? Unlimited storage is just a click away. Go to http://help.yahoo.com/l/in/yahoo/mail/yahoomail/tools/tools-08.html
