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.