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
