Re^5: Preventing database handles from going stale

If an established connection to your DMBS is timing out in a matter of seconds, then my first guess is that it's misconfigured. I don't know what the default timeout is for Postgres is, but many DBs let you keep a connection open for hours, if not days. MySQL, for example, defaults to 28800 seconds (8 hours) IIRC.

Other than that, dirving's solution of wrapping queries and reconnecting on failures is the only thing I can think of.

__________
The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it.
- Terry Pratchett

Comment on Re^5: Preventing database handles from going stale

Replies are listed 'Best First'.
Re^6: Preventing database handles from going stale by dsheroh (Monsignor) on Feb 05, 2007 at 18:20 UTC
It's not timing out in a matter of seconds, it just appears that `DBD::Pg->ping` is not sufficient as a keepalive. I'd estimate that I'm seeing a 5 minute timeout, but I haven't done any testing specifically aimed at verifying that. The race condition I'm talking about (assuming a 5 minute timeout) is that the `ping` could run 4 minutes 59.999 seconds after the last query (while everything is still fine), the connection times out at 5 minutes, and then the next query (tries to) run at 5 minutes and 0.001 seconds after the previous one. `eval` is indeed the only way to catch that scenario and recover from it, but an ounce of prevention and all that.	[reply] [d/l] [select]
Re^7: Preventing database handles from going stale by kyle (Abbot) on Feb 05, 2007 at 18:28 UTC
I think `ping` is implemented as a query. I sometimes see "`SELECT 'DBD::Pg ping test'`" in `pg_stat_activity`, and I can't imagine that's anything else.	[reply] [d/l] [select]
Re^8: Preventing database handles from going stale by EvanK (Chaplain) on Feb 05, 2007 at 18:46 UTC
It is, I just took a peek at the source code for it, and it does a SELECT (dbd_db_ping(dbh) in dbdimp.c). I don't know why that wouldn't be enough to keep the connection alive though...Perhaps the query needs to interact with an existing table to keep the connection alive? __________ The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it. - Terry Pratchett	[reply]