PostgreSQL notifications!

[Baxulio]

Hi,

Nowadays PostgreSQL notifications are becoming more and more popular and are useful in most scenarios, e.g. real-time applications etc.

That would be great if Drogon also supports PostgreSQL notifications!

Thanks!

[hwc0919]

The listen/notify functionality is easy to realize. The two major problems are:

• How to maintain listening state after re-connection
• How to store and match the callback function.

Postgres listen/notify is much alike redis sub/pub, except that: postgres listen/notify can use the same connection with normal queries, but redis sub/pub can only run on a seperate connection.

Considering the similarities and differences between postgres and redis, I came up with two approches for it:

1. Use a global notification callback

static DbClient::setNotificationCallback(std::function<void(std::string channel, std::string payload)>);

pros:

• Simple to use
• Little code changes
• Reuse connections

cons:

• Require locks when making listen queries ( the first time and after re-connection)
• The global callback should be thread-safe

2. Use new connections for listen, and save one callback for each listening channel

The api would be like redis sub/pub api (#1212)

auto listener = dbClient->newListener();
listener->listen(channel, [](std::string channel, std::string payload) {
    // do something
});

pros:

• Less locks

cons:

• A lot code changes, just like #1212
• Need more connections
• Need more memory for callbacks, and overhead for matching channel and callbacks.

---

I need some opinions on which way is better.

[Baxulio]

I'm not sure which way is better, but creating separate connections for notifications sounds a bit strange. We already have these connections.

How I see the situation:

IMO each connection must have a method like subscribeToNotifications(...), e.g.

bool DbClient::subscribeToNotifications(std::vector<std::string> notifications, std::function<void(std::string notification, std::string payload)>)

usage: bool subscribed = app().getDbClient().subscribeToNotifications({"not1", "not2"}, [](std::string notification, std::string payload){ });

I think this way each connection will have its slot in its dedicated thread context, so no need to make it thread safe.

[hwc0919]

#1464 This pr is almost ready.