Thread: Idle transaction causing problems.
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... Is there a way (w/ gdb or other) to determine the source of the idle transaction blocking traffic? Regards, Garo =-=-==-=-=-== Xapnet Internet Solutions 1501 Powell St., Suite N Emeryville, CA 94608 Tel - (510) 655-9771 Fax - (510) 655-9775 Web - http://www.xapnet.com
On Tue, 18 Feb 2003, Garo Hussenjian wrote: > 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... > > Is there a way (w/ gdb or other) to determine the source of the idle > transaction blocking traffic? Not helping you troubleshoot the exact situation, just useful info on PHP / Postgresql... Fyi, older versions of php had a bug that when they put out a notice condition, it could cause apache children to crash and burn. One of the most common notice messages is about supplying a missing from clause or some such. This was fixed around 4.2 or 4.3 I believe. To find the culprit, try running your various queries from a psql interface and look for the notice. Any query generating a notice can crash the backend, but only randomly, not every time. I had a script that was doing something similar, leaving ports open.
Garo Hussenjian <garo@xapnet.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
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@xapnet.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