apache2 mod_perl DBD/DBI postgres connection problems

We are running Apache/2.2.10 (Linux/SUSE) mod_ssl/2.2.10 OpenSSL/0.9.8h mod_perl/2.0.4 Perl/v5.10.0 wirh DBD::Pg 2.18.1 and Apache::DBI 1.11 on SLES11 SP1.

Postgres 8.3.14 listens on TCP port 5432 and named socket /tmp/.s.PGSQL.5432

I see intermittent messages in my web server error log which occur under light or heavy load.

-e: DBD::Pg::st execute failed: could not receive data from server: Socket operation on non-socket at ...(my module)...
-e: DBD::Pg::db rollback failed: could not receive data from server: Socket operation on non-socket at /usr/lib/perl5/site_perl/5.10.0/Apache/DBI.pm line 268

It is as if the httpd2-prefork processes are trying to use connections that have "gone stale".

Restarting apache2 will stop the messages but they sometimes come back within minutes, most times it will be hours before the problem recurs. Anyone, seen this?

Comment on apache2 mod_perl DBD/DBI postgres connection problems
Re: apache2 mod_perl DBD/DBI postgres connection problems (alarm?) by tye (Cardinal) on Oct 12, 2011 at 01:20 UTC
Are you using alarm to time-out SQL queries? That will only work if you have "safe signals" turned off. This makes the signals... unsafe. I don't recall the exact errors that we ran into due to that, but yours certainly seem similar. Empirical statistics suggest that about 10% of such firings of SIGALRM will result in the Perl interpreter becoming, shall we say, "unhappy". The safest thing is to just force the process to exit quickly after an unsafe signal gets processed. I think Perl actually does things with "safe signals" that actually prevents a signal from interrupting an I/O operation. If so, that is quite unfortunate. If not, it might be fairly easy to build a patched version of the DBD module you are using such that interrupted I/O requests don't get restarted. It is certainly very unfortunate that Perl can't safely time-out a DB operation. - tye	[reply]
Re^2: apache2 mod_perl DBD/DBI postgres connection problems (alarm?) by otter117 (Initiate) on Oct 12, 2011 at 12:13 UTC
Thanks for your reply. We do not use alarm at all. We have no code for timing out SQL queries.	[reply]
Re: apache2 mod_perl DBD/DBI postgres connection problems by otter117 (Initiate) on Oct 19, 2011 at 15:02 UTC
I updated to mod_perl/2.0.5 and the messages have not appeared for the last day and a half.	[reply]


good chemistry is complicated, and a little bit messy -LW
	PerlMonks

apache2 mod_perl DBD/DBI postgres connection problems

How many continents have you visited?