Idle transaction causing problems.

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