Is it safe to use the same connection to perform statements and LISTEN to notifications?

[tomwassing]

Hi,

I am trying to reduce the amount of connection to a Postgres database, as many libraries open a new connection when trying to LISTEN to a channel. I have the idea that pgconn supports concurrently executing statements and handling notifications by settings the OnNotification handler, however, I'm not entirely sure, am I seeing this correctly?

Thanks!

[jackc]

It is safe -- though the handler must be written with care. It can be called during the execution of a query so it is unsafe to do anything with the connection.

If you moved up a layer to pgx then you could take advantage of it automatically buffering notifications received while executing a statement. i.e. You could alternate between executing statements and calling WaitForNotification.

[tomwassing]

Cool! What would happen in case the connection is waiting for a notification and a statement is executed, would that interfere?

Do you have a suggestion for a good approach to alternate between WaitForNotification and executing a statement? One approach I was thinking about, is to cancel the context of the WaitForNotification (does feel a bit hacky)

[jackc]

Cool! What would happen in case the connection is waiting for a notification and a statement is executed, would that interfere?

In pgx if a notification is received while a statement is executing it will be buffered and returned when WaitForNotification is next called. In pgconn the OnNotification handler is called while the statement is being executed (which is why it takes more care to use properly).

Do you have a suggestion for a good approach to alternate between WaitForNotification and executing a statement? One approach I was thinking about, is to cancel the context of the WaitForNotification (does feel a bit hacky)

Nothing wrong with that. In fact, WaitForNotification checks for buffered notifications before it checks if ctx is cancelled. So you can easily check for notifications that have already been received without waiting at all for new notifications by passing is an already cancelled context.