How are Postgres server restarts handled?

[mecampbellsoup]

Hi guys,

We are using asyncpg in a web application.

Upon starting the web server, we do the following two things:

1. db = asyncpg.create_pool to create the connection pool for the application's DB connections.
2. conn = await db.pool.acquire(); await conn.add_listener('foo', _callback) to register long-lived listeners to catch and handle NOTIFY events from the DB.

Recently I restarted the Postgres server and noticed something interesting. First, connections to the DB using the db pool object saw no interruption and were still able to connect to the DB server despite it having a new PID (since the process had been restarted).

However, the conn the application relied on to receive async notifications from the server was dead/closed and so the application logic dependent upon receiving those notifications was not functioning. (This is how I reproduced this issue and discovered the cause to have been a PG server restart.)

• Why is the pool unaffected by restart? It makes sense the connection instance is dead since the socket is closed after restarting the server, but I'm a bit surprised the pool is not affected.
• I suppose the best strategy for checking for dead DB connections would be to simply check conn.is_closed periodically - or, is there some "on connection close" callback I can define to retry/reopen a connection (since obtaining new connections from the pool still works after restart)?

[elprans]

The pool is designed to handle connections being closed and to reconnect automatically as long as that is possible to do using the original connection parameters.

The closest thing to being able to handle "on close" on individual connections currently is to subclass Connection and overload the _cleanup() method. An explicit user-specified callback would arguably be nicer, of course, so I won't oppose a PR adding it (it can be one of the things _cleanup() calls).

[ioistired]

it would seem that even _cleanup is not called when the connection is closed externally. I have implemented support for custom callbacks that are run by _cleanup: https://github.com/iomintz/asyncpg/tree/on_close

A demo of it in use:

🐍 conn = await asyncpg!.connect()
🐍 def listener(conn): print('disconnected!')
•• 
🐍 conn.add_close_listener(listener)
🐍 await conn.fetchval('select 1')
1
🐍 conn._cleanup_listeners
{<function listener at 0x7f499803c160>}
🐍 # restart postgres externally
🐍 await asyncio.sleep(0)
🐍 conn.is_closed()
True
🐍 conn.terminate()
disconnected!

As you can see, _cleanup was not called until I manually ran terminate() even though the connection itself was closed.

So this does not seem to be a way to handle "on close".