Re: 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
tsakai(at)gallo(dot)ucsf(dot)edu

-----Original Message-----
From: pgsql-admin-owner(at)postgresql(dot)org on behalf of Scott Marlowe
Sent: Thu 12/13/2007 1:57 PM
To: Tena Sakai
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] reading pg_stat_activity view

On Dec 13, 2007 3:43 PM, Tena Sakai <tsakai(at)gallo(dot)ucsf(dot)edu> 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