Re: reading pg_stat_activity view

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

Yeah, it's pretty much a brick wall setting. anything over it gets
stopped, period.

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

That's not what will happen. If the user hits ctrl-C from psql, it
will send a cancel command to the backend, unlike a certain database a
lot of people use **COUGHmysqlCOUGH** which will kill the client side
app and leave the query running against the backend.

Now, should the user initiate a query through a we interface it is
quite possible for them to close the web page and for the query to
keep on running for quite some time.

There are scenarios where querys run for days and days, and it's a
good idea to keep track of the long running queries to make sure
they're not scramming your db server. Figure out what your absolutely
longest running query time should be, and set the timeout to that.
For me, on a reporting server at work, that's several hours. On an
OLTP machine it would likely be minutes.

Now, the cool thing is, you can set different max execution times per
database and per user. not just for the whole db cluster. Once
you've set something like 4 hours for the whole server, you can then
do something like:

alter database xyzdb set statement_timeout=300;
alter user stanley set statement_timeout=1200;

etc...

If you just want to see what statements have been running a long time,
you can use the pg_stat_activity view to see that. It's pretty easy
to write a simple bash script that tosses a query at the db and pulls
the numbers out and sends you an alert if it sees a query running too
long. Shout at me if you want an example.