I've got some queries generated by my application that will, for some
reason, run forever until I kill the pid. Yet, when I run the
queries manually to check them out, they usually work fine. To get
more information about these queries, I'm writing a utility to take
snapshots of pg_stat_activity every 5 minutes. If it finds a query
that runs for longer than 15 minutes, it will trap the query so I can
run 'explain analyze' on it and see where the weakness is.
However, the problem I have is that pg_stat_activity only returns the
first n (255?) characters of the SQL as "current_query", so it gets
chopped off at the end. I would very much like to find out how I can
get the *entire* query that is active. Is this possible?
Also, I'm sure some people will respond with "turn on query
logging".. I've explored that option and the formatting of the log
file and the fact that EVERY query is logged is not what I'm after
for this project. The "infinite-running" queries are unpredictable
and may only happen once a week. Logging 24/7 in anticipation of one
of these occurrences is not something I'd like to do.
Thanks,
Dan Harris
