Re: IDLE in transaction introspection

On Mon, Oct 31, 2011 at 23:37, Scott Mead <scottm(at)openscg(dot)com> wrote:
>    So I wrote the attached patch, it just turns <IDLE> in transaction into:
>  "<IDLE> in transaction\n: Previous: <last query executed>".  After seeing
> how quickly our dev's fixed the issue once they saw prepared statement XYZ,

Solving this problem is a good idea, but I don't particularly like the
proposed solution. I think the proposed state/query split is going to
make pg_stat_activity more confusing, especially if even idle
connections get a query string. And if we display the last query
there, why not the one before that? etc. (Adding a "state" column
might not be a bad idea though)

I'd very much like to see a more generic solution: a runtime query log
facility that can be queried in any way you want. pg_stat_statements
comes close, but is limited too due to its (arbitrary, I find)
deduplication -- you can't query for "10 last statements from process
N" since it has no notion of processes, just users and databases.

So far my developers are simply grepping pg_log, but you can't use the
full power of SQL there. I know there's csvlog, but the pains aren't
big enough to make attempt to use that.

Regards,
Marti