cannot execute unlisten during recovery

Question

I'm getting the error "cannot execute unlisten during recovery" when I use Pooling=True in my connection string.

This error is on a replicated / read server which is running on hot standby.

Answer 1

This is still happening with version 3.2.7. It should be possible to run SELECT queries on a hot standby database, but when doing this with Npgsql, we get

PostgresException 25006: cannot execute UNLISTEN during recovery    
at Npgsql.NpgsqlConnector.<DoReadMessage>d__157.MoveNext()

A look at the source confirms that, when pooling connections, Npgsql cleans up after each connection is closed. One of the cleanup operations is UNLISTEN *, which fails on hot standby databases, since it affects state.

Fortunately, there are connection string parameters we can set to avoid this. As the original question mentions, you can disable connection pooling. However, in situations where performance is important, it is better to add No Reset On Close=true; to the connection string instead.

Using No Reset On Close does carry some risk of leaking state from one command to the next, but since you're in read-only mode, it can't affect your stored data. Be careful to dispose of cursors, sequences and temporary tables if you're using them. On the bright side, it may give your queries a slight speed boost.