Re: Idle transaction causing problems.

Garo Hussenjian <garo(at)xapnet(dot)com> writes:
> I have a server (pgsql 7.1.2) that periodically racks up a bunch of UPDATE
> waiting processes causing my php application to hang... I used gdb to get
> the debug_query_string on one of the UPDATE waiting processes and found it
> to be a very simple query on our session table... Not a server-breaker!

> The culprit seemed to be another process with status 'transaction idle' but
> the gdb debug_query_string was null (pointed to 0x0)... When I killed the
> transaction idle process the UPDATE waiting processes cleared out
> immediately and we were up and running again...

Sounds like it had an exclusive lock on the table the UPDATEs wanted to
update.

> Is there a way (w/ gdb or other) to determine the source of the idle
> transaction blocking traffic?

In 7.1 it's not at all easy to figure out. In 7.3 you can look in the
pg_locks system view to see whose lock is blocking whom.

regards, tom lane