Support for LISTEN/NOTIFY

[deyandyankov]

Is there support for LISTEN/NOTIFY as per this? I didn't see any examples in the docs and not sure how to go about it.

[iamed2]

You can access this using the C API in LibPQ.libpq_c, the function is listed here: https://www.postgresql.org/docs/14/libpq-notify.html

However, receiving notifications is unfortunately tied to consuming input or executing queries. If you're fine waiting for queries, you can call PQnotifies to get the next notification. But if you want to receive notifications asynchronously without executing queries, you might have some challenges working around what is currently in LibPQ.jl. Right now, PQconsumeInput is only called when processing asynchronous queries, and so the code in LibPQ.jl for calling it is highly coupled to the code for fetching asynchronous results. It's also not supported behaviour to operate on a connection from multiple threads (see https://www.postgresql.org/docs/14/libpq-threading.html) so really there needs to be an internal rearchitecture to decouple this functionality.

[deyandyankov]

thanks for the pointers - I'll have a look and will post a mwe here before closing the issue. At the moment, I'm not interested in async or multi-threaded operation.

[deyandyankov]

hi @iamed2 - I managed to identify whenever a notification is sent but I am struggling to get the payload.

My code is the following:

using LibPQ
using ConfigEnv
dotenv()

lpqc = LibPQ.libpq_c

function onenotify()
    conn = lpqc.PQconnectdb(ENV["EXPCONNSTR"])
    res = lpqc.PQexec(conn, "LISTEN virtual")
    lpqc.PQclear(res)

    while true
        notify = lpqc.PQnotifies(conn)
        if notify != C_NULL
            @show notify
            lpqc.PQfreeNotify(notify)
            break
        end
        lpqc.PQfreeNotify(notify)
        lpqc.PQconsumeInput(conn)
        sleep(1)
    end
    lpqc.PQfinish(conn)
end

Calling this function results in:

julia> onenotify()
notify = Ptr{Nothing} @0x0000000004cbed50

julia>

any hints on how to convert this to LibPQ.libpq_c.pgNotify ?

Thank you for your time and apologies for my poor C...

[iamed2]

Ah, I noticed that there is a pgNotify and a PGnotify, and the PQnotifies function is set to return the latter, which is an alias to a void pointer. I recommend changing the return type of PQnotifies to Ptr{pgNotify}, and then you should be able to get that data using unsafe_load. Note that the string fields of the struct are still pointers to libpq-allocated memory, so if you want to pass the data back to the user after freeing the memory you need to copy those strings (use unsafe_string for that), otherwise you might see segfaults.

Even without changing the return type of the ccall in PQnotifies, you could get the data out by converting:

tmp = unsafe_load(Ptr{lpqc.pqNotify}(onenotify()))

[deyandyankov]

Thank you - I raised #245 with the fix you suggested.
Closing the issue for now. Once I've played around with this some more, I might raise another PR that allows for the handling of these messages a little more cleanly. I am thinking something similar to conninfo(ci_ptr::Ptr{libpq_c.PQconninfoOption}) but for notifications.