Tom,

> Another relevant question is why you are expecting to get this
> information through pgstats and not by looking in the postmaster log.
> I don't know about you, but I don't have any tools that are designed to
> cope nicely with looking at tables that have columns that might be many
> K wide. ÂLooking in the log seems a much nicer way of examining the full
> text of extremely long queries. ÂSo I think it's actually a good thing
> that pgstats truncates the queries at some reasonable width.

Because pg_stat_activity can be queried dynamically, and the log can't.   I'm 
currently dealing with this at a clients site who is having elusive "bad 
queries" hammer the CPU.  

In order to find a bad query by PID, I have to:
1) turn on log_statement, log_timestamp and log_pid;
2) HUP the postmaster;
3) watch top and record the time and pid of the "bad query";
4) cp the log off to a file;
5) turn back off log_statement and log_pid;
6) grep the log for the time/pid, using a regexp to deal with minor variations 
in timestamp.

It's a big PITA to retrieve the text of one bad query.   And that's assuming 
that the bad query re-occurs within a reasonable window of time from when I 
spotted it so that I don't end up watching top for the rest of the afternoon.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Reply via email to