The pg_stat_activity table basically shows the same picture as the process list:
- two sessions idle and marked "in transaction"
- one session waiting with an UPDATE as the "current_query"
- a couple of more user sessions idle (and not marked "in transaction")
Time stamps are recent and consistent with what I'd expect. (The
client hung right after it had been started and all timestamps are
around that time.)
Returning to the the pg_locks view, I've started wondering about this
sentence of the documentation
(http://www.postgresql.org/docs/8.3/interactive/view-pg-locks.html):
"If a transaction is waiting for a row-level lock, it will usually
appear in the view as waiting for the permanent transaction ID of the
current holder of that row lock."
Which column is meant here? Also, what is the difference between
permanent and virtual transaction ID, if any?
Thanks
Peter
2009/4/10 Péter Kovács <maxottovonstirlitz@gmail.com>:
> Overlooked your mentioning to timestamps in pg_stat_activity. What
> patterns do I need to look for there.
>
> Thanks
> Peter
>
> 2009/4/9 Kevin Grittner <Kevin.Grittner@wicourts.gov>:
>> Péter Kovács <maxottovonstirlitz@gmail.com> wrote:
>>> postgres 14727 9868 0 22:34 ? 00:00:00 postgres: pkovacs
>>> chemaxon 127.0.0.1(12496) idle in transaction
>>> postgres 14735 9868 0 22:34 ? 00:00:00 postgres: pkovacs
>>> chemaxon 127.0.0.1(12497) idle in transaction
>>> postgres 14737 9868 0 22:34 ? 00:00:00 postgres: pkovacs
>>> chemaxon 127.0.0.1(12498) UPDATE waiting
>>
>> I would start by looking at pg_locks to see if your update is blocked
>> by one of the connections which is idle in a transaction. Any locks
>> won't be released until the idle transaction commits or rolls back.
>> Then look at pg_stat_activity to check timestamps. Be sure your
>> software doesn't have any code path which leaves things dangling.
>> (Since you seem to be using Java, I recommend proper use of
>> try/finally blocks to ensure resources are released.)
>>
>> -Kevi
>>
>