Description
Is your feature request related to a problem? Please describe.
It would be great to support LISTEN in session mode at least. NOTIFY is a regular query, so it works currently.
Additionally, it would be great to support this in transaction mode. Pgcat will have to maintain the subscriptions and messages sent by the clients and servers, and match them to each other. This is almost equivalent to implementing the whole feature inside Pgcat instead of using the one implemented in Postgres, except that there are typically more than one instance of Pgcat running in production, so we still need to let Postgres tell us who sent which message and when.
Describe the solution you'd like
For session mode, we just need to tokio::select on read_message and server.recv() instead of just acting on client messages.
For transaction mode, we have to implement a central store of subscriptions and messages, and route the messages to clients accordingly. We may also need to run a dedicated background connection to the primary to listen for events (effectively min_pool_size will be always 1).
Additional context
I think this will be impactful because currently LISTEN/NOTIFY only works in session mode in PgBouncer. Session mode doesn't really scale to big production deployments, so the LISTEN/NOTIFY feature of Postgres is rarely used in those situations, and people resort to running alternative databases for this, like Redis.