I have built a web application that uses postgres (7.3.2) as its backend, with the front being an Apache2/mod_perl solution. I've printed what I think the relevant versions are below.
Anway, DBD::Pg throws random errors into my logs, along the lines of what is below (the lines beginning with SQL are debug statements from the script, and are the lines references by the DBD error.
SQL: SELECT session_key FROM session WHERE user_id = 101 at /home/dragon/public_html/iactweb/lib/perl/Apache/IACT/AuthUtil.pm line 38.
DBD::Pg::db selectrow_array failed: ERROR: current transaction is aborted, queries ignored until end of transaction block at /home/dragon/public_html/iactweb/lib/perl/Apache/IACT/AuthUtil.pm line 40.
[Thu Feb 12 18:48:13 2004] [error] [client 127.0.0.1] DBD::Pg::db selectrow_array failed: ERROR: current transaction is aborted, queries ignored until end of transaction block at /home/dragon/public_html/iactweb/lib/perl/Apache/IACT/AuthUtil.pm line 40.
with the corresponding postgresql.log entry being
LOG: statement: SELECT session_key FROM session WHERE user_id = 101
ERROR: current transaction is aborted, queries ignored until end of transaction block
This seems to randomly happen in my application, resulting in an error page being returned to the user. However, if the user refreshes their browser (sometimes more than once), it seems to come back fine. I have seen it happen with DBD::Pg::db st, selectrow_array, selectrow_arrayref, and selectrow_hashref.
The components of the system currently are (upgraded this morning to the latest):
DBI 1.40 DBD::Pg 1.31 Apache::DBI 0.93 Apache 2.0.48 and 2.0.47 mod_perl 1.99.11 and 1.99.12 PostgreSQL 7.4.1 and 7.3.2
I'm leaning towards blaming the interaction between DBI and Apache::DBI (persists DB connections within apache), but I'd like to rule out postgres as the problem, if anyone can do that conclusively. I never disconnect from my DB handles, since Apache::DBI caches them, so I was wondering about the whole 'ping' method. I wrote a script to exercise one of my scripts from the command line, and ran it 10000 times without error, so I am pretty sure that there is some interaction error going on in the system.
If anyone has seen anything like this before (i.e. random errors in a similar apache/mod_perl) situation, or might have any pointers as to where to look next, let me know. What would cause postgres to return an error for a selectrow_array, or one of those others mentioned?
Thanks -Mike
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
