search

jackc / pgconn

MIT License
182 stars 87 forks source link

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

Open tomwassing opened 2 years ago

tomwassing commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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.