Support PostgreSQL notifications.

[lvella]

I need to receive PostgreSQL asynchronous notifications in my application.

At ConfigConnectImpl<T>::connect() , a task is created awaiting for the tokio-postgres connection to run to completion. This discards every notification received by the client.

Instead of simple await on the connection, deadpool-postgres could use tokio_postgres::Connection<S, T>::poll_message() to received notifications. It could buffer them a VecDeque, like the synchronous postgres wrapper around tokio-postgres does it. Or at least allow us to register a callback, for the duration of the borrow, to handle them.

[bikeshedder]

How about pushing messages to a tokio::sync::mpsc (or broadcast?) channel? I just think there should be a security measure in place. e.g. returning the connection to the pool will close this channel and upon recycling the connection a new empty channel is created.

When recycling the connection it's also important to execute a UNLISTEN * statement so those unused resources are cleared.

Apart from those technical details I'm curious if this is really the right way to do it. A better aproach would be to create one connection to the database and issue all LISTEN statements there. Am I missing a usecase that is not possible with that approach?

[lvella]

Yes, I ended up writing my own connection pool, and used a tokio::sync::mpsc channel for the messages.

In my application, the notification is task specific: get a connection from the pool, do some writes, write to a channel, wait on for the response in the channel specific to the thing that was written, do some read, return the connection to the pool. The receiving channel will never be used again, or by other client.

[lvella]

By now it seems kinda obvious that the design of my application is dump. Instead of block one connection waiting for a channel notification, I think the following is the right approach:

We must have one connection, outside of the pool, just to handle the channel subscription/notification. Lets call it the NotificationDispatcher. Tasks will subscribe to a channel via the NotificationDispatcher, which will return a handle that can be awaited on, and upon dropping it will automatically UNLISTEN if that is the last handle to that channel.

The NotificationDispatcher will receive the notifications and dispatch to registered handles.

This can be implemented in a completely independent library from deadpool, but I could not find an existing one.

[bikeshedder]

Yes. That's the approach I've been promoting ever since. The connection that handles all the subscriptions is typically long lived and will subscribe to all the topics the application is interested in. You will never ever want to release this connection back to the pool.

It might still be useful to have this feature as part of deadpool-postgres though when using PostgreSQL LISTEN+NOTIFY for a kind of RPC interface. The application design might become much simpler if you could just create short lived subscriptions that way.