Re: Idle transaction causing problems.

Yeah,

I guess it's time to upgrade the backend (both postgres and php). If I still
see this happen again at least then I'll be able to possibly track down the
source of the idle transaction.

Thanks,
Garo.

> 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
>

=-=-==-=-=-==

Xapnet Internet Solutions
1501 Powell St., Suite N
Emeryville, CA 94608

Tel - (510) 655-9771
Fax - (510) 655-9775
Web - http://www.xapnet.com