Thread: improvement proposition
hi, would it be possible for someone to add "last query" for pg_stat_activity view? there is a lot of cases that we have "idle in transaction" sitting for long time, and since we dont log all queries it is next to impossible to tell where in app code the problem lies. it would be very useful to get something like "previous query". is adding something like this possible? depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV)
hubert depesz lubaczewski <depesz@depesz.com> writes:
> would it be possible for someone to add "last query" for
> pg_stat_activity view?
> there is a lot of cases that we have "idle in transaction" sitting for
> long time, and since we dont log all queries it is next to impossible to
> tell where in app code the problem lies.
Turn on log_statement. I don't believe the feature you are asking for
will be nearly as useful as you think. More than likely, what it
will show you is something like "commit; begin" and you'll be little
wiser than before. What you'll need to identify the problem will be a
trace of everything the app did; which is what the log is for, not
pg_stat_activity.
regards, tom lane
On Tue, Oct 16, 2007 at 11:16:46AM -0400, Tom Lane wrote: > Turn on log_statement. I don't believe the feature you are asking for > will be nearly as useful as you think. More than likely, what it > will show you is something like "commit; begin" and you'll be little > wiser than before. What you'll need to identify the problem will be a > trace of everything the app did; which is what the log is for, not > pg_stat_activity. logging all statements is possible (this is what i do now), but it is far from optimal - it generates 500 megabytes of logs every 10 minutes. on the other hand - doing log analysis i did found the problem (and it was not commit; begin thing) - i was simply hoping for simpler, and less intrusive way of getting the data. depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV)
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 10/16/07 08:21, hubert depesz lubaczewski wrote: > hi, > would it be possible for someone to add "last query" for > pg_stat_activity view? > > there is a lot of cases that we have "idle in transaction" sitting for > long time, and since we dont log all queries it is next to impossible to > tell where in app code the problem lies. > it would be very useful to get something like "previous query". Transaction analysis is the way to go here. It requires a serious code review, though. > is adding something like this possible? - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHFRUuS9HxQb37XmcRAkvuAKCrVQZK1wEAxLquo8ZHHCTLyOmEUgCfYc5o 55pO2d/P4nEp8a8l3GRl0bk= =Kkkd -----END PGP SIGNATURE-----
ron.l.johnson@cox.net (Ron Johnson) writes: > On 10/16/07 08:21, hubert depesz lubaczewski wrote: >> hi, >> would it be possible for someone to add "last query" for >> pg_stat_activity view? >> >> there is a lot of cases that we have "idle in transaction" sitting for >> long time, and since we dont log all queries it is next to impossible to >> tell where in app code the problem lies. >> it would be very useful to get something like "previous query". > > Transaction analysis is the way to go here. It requires a serious > code review, though. > >> is adding something like this possible? I don't think it's likely to happen any time soon, certainly not for "legacy" versions of PostgreSQL. (e.g. - this might *conceivably* be material for version 8.4, but it won't be happening for any databases presently in production) On The Other Hand, the last time this happened and I got "called into action" (which wasn't terribly long ago), I did a search on pg_locks to see what locks the "<IDLE> in transaction" connection was holding onto, and that provided enough information for us to infer which part of the user code the system was in. -- (format nil "~S@~S" "cbbrowne" "linuxdatabases.info") http://linuxfinances.info/info/linuxdistributions.html Signs of a Klingon Programmer - 7. "Klingon function calls do not have 'parameters' - they have 'arguments' -- and they ALWAYS WIN THEM."