Thread: How to identify long-running queries, not just long-running backends?

I need a way to identify Pg backends which have been running a given
query for a long time. What I have so far is to use pg_stat_activity and
the process table (in my case, via Perl's Proc::ProcessTable) to
identify processes with a lot of CPU usage and an active query. The
problem is false positives: since we use a daemon architecture which may
keep a Pg backend open for a while and run many, many queries, I can't
tell the difference between a backend which has run one query with
elapsed time of several minutes, and a backend which has run 1000
queries each with a sub-second elapsed time.

What I'm really hoping for is a way to get the "start time" for a query
in pg_stat_activity.

I'm using 7.4.1, by the way.

--
Jeff Boes                                         Vox 269-226-9550 x24
Director of Software Development                  Fax 269-349-9076

Exfacto!         Exceptional Online Content     http://www.exfacto.com
Nexcerpt         ...Extend Your Expertise...    http://www.nexcerpt.com

Jeff Boes wrote:

> What I'm really hoping for is a way to get the "start time" for a query
> in pg_stat_activity.

... which I guess is pg_stat_activity.query_start. Duh. I will now hide
under my desk for a while.

(Don't know how I missed this; maybe I was looking at old documentation
for the pg_stat_activity view.)

--
Jeff Boes                                         Vox 269-226-9550 x24
Director of Software Development                  Fax 269-349-9076

Exfacto!         Exceptional Online Content     http://www.exfacto.com
Nexcerpt         ...Extend Your Expertise...    http://www.nexcerpt.com