Add support for LISTEN/NOTIFY

[levkk]

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.

[aek]

Hello, I'm trying to debug pgcat to find out how to make this work but I'm very new to the project and I really wanna switch from pgbouncer.

Let me know if I could help to get this done. I read this thread https://github.com/sfackler/rust-postgres/issues/1098#issuecomment-1913027515 that have some working stuffs around LISTEN/NOTIFY in Rust

The readme it's not accurate when it says: Session pooling Stable Identical to PgBouncer.

It's not Identical to PgBouncer since it doesn't support LISTEN/NOTIFY in Session Mode