Support for Postgresql LISTEN command?

[MTRNord]

Hi Postgresql has the non standard LISTEN command which allows to essentially have like a callback or event listener style interface to the database. https://www.postgresql.org/docs/current/sql-listen.html

Is there any chance that this might be supported in the future? Or is it already possible in some way I am not aware of? :)

[vadz]

It should be possible to use it directly, shouldn't it? I.e. wouldn't just issuing the corresponding SQL work?

But SOCI doesn't provide any special support for it as I don't know if any other RDBMS provide anything similar. If they do, we could try to find some useful API that could be implemented for all of them, but as long as it's just available in Postgres, it doesn't seem really useful to do anything special for it in SOCI, what would be the added value of doing this?

[MTRNord]

It should be possible to use it directly, shouldn't it? I.e. wouldn't just issuing the corresponding SQL work?

I am not sure tbh as this is async. LISTEN registers a callback channel from how I understand it :)

From the docs:

With the libpq library, the application issues LISTEN as an ordinary SQL command, and then must periodically call the function PQnotifies to find out whether any notification events have been received. Other interfaces such as libpgtcl provide higher-level methods for handling notify events; indeed, with libpgtcl the application programmer should not even issue LISTEN or UNLISTEN directly.

So at least they have some sort of interface for this. So I am not sure if SOCI can do that in any way. The registration of it using the SQL command is only one half of this command from what I understand.

So unless I am mistaken, at least a polling or so is needed in the pg driver to use it.

I am not sure if any of the other supported Database backends provide anything like this.

Sqlite most certainly does not. Mysql doesn't seem to have this either. At least, a quick google didn't yield results. For oracle I am not sure as I only am able to find java results and my java isnt good enough to differentiate it from a java or a db listener thing. Firebird sql seems to have something similar https://firebirdsql.org/file/documentation/papers_presentations/Power_Firebird_events.pdf DB2 only has update events it seems https://www.ibm.com/docs/en/db2/11.5?topic=events-rowupdated no generic/user defined ones ODBC seems to not support this or I wasnt able to find it.

[samh]

I'm not sure whether or not it makes sense to add some wrapper for this into SOCI, but in case anyone needs it, is it possible to do it "alongside" SOCI by leveraging the libpq backend. This should also give some idea of what would need to be done if anyone ever wanted to implement this for SOCI; the libpqxx library appears to have a reasonable implementation, so that might be a good model.

1. Call LISTEN on each channel, and keep track of what you want to receive each channel
2. Get a pointer to the soci::postgresql_session_backend, for example:

// dynamic_cast fails with "undefined reference to `typeinfo for soci::postgresql_session_backend'"
// I'm not clear on why, but the documentation also uses static_cast.
// Seems to be something about visibility and shared libraries.
// Not sure if it's the same thing, but in https://github.com/SOCI/soci/pull/975
// they changed away from dynamic_cast to fix some other issue.
auto *backend = static_cast<  // NOLINT(cppcoreguidelines-pro-type-static-cast-downcast)
        soci::postgresql_session_backend *>(
        sql_->get_backend());

3. Optionally get the file descriptor by calling PQsocket(backend->conn_); you can use this to listen asynchronously for changes however you like (e.g. select, poll, QSocketNotifier)
4. When the socket is notified for read, or periodically, check for updates; something like this, but the exact details depend on your implementation:

/*
 * "A better way to check for NOTIFY messages when you have no useful
 *  commands to execute is to call PQconsumeInput , then check PQnotifies.
 *  You can use select() to wait for data to arrive from the server,
 *  thereby using no CPU power unless there is something to do.
 *  (See PQsocket to obtain the file descriptor number to use with
 *  select().)"
 */
PQconsumeInput(impl_->postgresql_session_backend_->conn_);
/*
 * "The function PQnotifies returns the next notification from a list of
 *  unhandled notification messages received from the server. It returns a
 *  null pointer if there are no pending notifications. Once a notification
 *  is returned from PQnotifies, it is considered handled and will be
 *  removed from the list of notifications."
 */
// We have to call PQfreemem after PQnotifies returns non-null;
// use unique_ptr with deleter to try to be exception-safe.
std::unique_ptr<PGnotify, decltype(&PQfreemem)> notifyPointer(nullptr, PQfreemem);
while (auto *notify = PQnotifies(impl_->postgresql_session_backend_->conn_)) {
    notifyPointer.reset(notify);
    if (auto it = impl_->receivers.find(notify->relname); it != impl_->receivers.end()) {
        (*(it->second))(notify->extra, notify->be_pid);
    }
}

5. Make sure you re-send the LISTEN SQL if SOCI reconnects

References:

1. https://www.postgresql.org/docs/current/libpq-notify.html
2. https://www.postgresql.org/docs/current/libpq-example.html#LIBPQ-EXAMPLE-2
3. libpqxx Notifications and Receivers