Re: IDLE in transaction introspection

On Mon, Oct 31, 2011 at 22:37, Scott Mead <scottm(at)openscg(dot)com> wrote:
> Hey all,
>
>    So, I'm dealing with a a big ol' java app that has multiple roads on the
> way to <IDLE> in transaction.  We can reproduce the problem in a test
> environment, but the lead dev always asks "can you just tell me the last
> query that it ran?"
>    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,
> I'm thinking that I'd like to be able to have this in prod, and... maybe
> (with the frequency of IIT questions posted here) someone else would find
> this useful.
>
>  Just wondering what ya'll thought.  Any feedback (including a more
> efficient approach) is welcome.  (Patch against release 9.1.1 tarball).
> Thanks!

I think the idea in general is pretty useful, but I'd like to extend
on it. It would be even better to have a last query executed in the
general IDLE state as well, not just idle in transaction.

However, doing it the way you did it by adding it to the current query
is going to break a lot of tools. I think it's a better idea to create
a separate column called "last query" or something like that.

Actually, for the future, it might be useful to have a "state" column,
that holds the idle/in transaction/running status, instead of the
tools having to parse the query text to get that information...

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/