I tried some testing with PG and it appears that when PG is stopped, existing pooled connections are then invalid, then PG is restarted and after the connection is returned and fetched from the pool again, the .cursor() call will fail which is one place we do catch errors and invalidate connections; so you get one error throw after the DB is restarted per previously existing connection. So while we still dont have invalidate catches for PG on execute()-level throws, we have them for cursor() level throws (since we consider all throws upon cursor() to be an invalidate situation). So an application will survive a DB restart at this time on PG, but with some bumps. setting recycle to a low number (like 5 minutes) can also decrease the chance of errors.

the two errors we probably want to catch for PG on execute() are:

psycopg2.OperationalError: connection not open psycopg2.InterfaceError: connection already closed

meaning, if those errors get thrown on cursor.execute(), invalidate the connection immediately.

Still, all of these measures require that we actually get an error thrown to detect that a restart took place, which inconveniently usually happens not at the point of cursor() but at the point of execute(), and we dont have any frameworks in place (nor am i terribly comfortable with) to handle "error was thrown, reconnect and try execute() again". lots of things can go wrong with that, namely transactional state getting lost, mis-interpreted errors resulting in double-executions, race conditions, etc. so i dont know if theres a way to recover from DB restarts 100% seamlessly.

On Mar 25, 2007, at 2:15 PM, Andreas Jung wrote: