Jooq: Add support for the Postgres NOTIFY statement

[lukaseder]

See the documentation here:
http://www.postgresql.org/docs/9.2/static/sql-notify.html

[mkurz]

+1

[lukaseder]

@mkurz: Thank you for your feedback. I don't remember where I put some additional comments regarding this functionality, but the problem here is that while the NOTIFY statement itself can be supported rather easily, notifications can't really be collected easily through JDBC/jOOQ as they are shipped with the JDBC driver as a "by-product" of any JDBC interaction.

But perhaps, I'm missing something obvious. How would you like this to work inside of jOOQ? Perhaps you have a real-world use-case that helps understand how this could be implemented...?

[kag0]

+1 for me as well
A real world use case I could present would be as follows:
System has a web service, a postgres db, a queue, and a queue worker. Actions from a web client cause work to be enqueued and consumed by the worker. The web client wants to be notified when the work is complete, so it maintains a websocket connection to the service. The service with the web socket connection listens on a channel. Upon completing the work the worker notifies that channel. The listening service relays the notification over the websocket to the client. The service unlistens when the concerned websocket closes.

A rough shot at how this might look in a service using jetty:

@WebSocket
public class MyWebSocket {

    private JooqChannelType channel;

    @OnWebSocketConnect
    public void connected(Session session) {
        channel = create
            .select()
            .from(channel("someChannel"))
            .listen();
        channel.onMessage(payload -> {
            if(payload != null){
                session.getRemote().sendString(payload);
            } else session.getRemote().sendString("ping");
        });
    }

    @OnWebSocketClose
    public void closed(Session session, int statusCode, String reason) {
        channel.unlisten();
    }

    @OnWebSocketMessage
    public void message(Session session, String message) throws IOException { ... }
}

[lukaseder]

Thanks for that example, @kag0. I think it is too early to discuss API at this stage, when it is not yet properly understood how the PostgreSQL LISTEN command can be implemented correctly in JDBC. In particular, I'm not sure why you suggest to "hijack" the SELECT * FROM someChannel statement, when the real PostgreSQL command is just LISTEN channel

The problem is that any messages from the server will be piggy-backed on any given JDBC interaction. Here's an example:

• Thread 1: LISTEN channel
• Thread 2: NOTIFY channel
• Thread 3: NOTIFY channel
• Pause (no messages are collected on thread 1)
• Thread 1: SELECT 1 (now, the previous 2 messages are piggy-backed with the select statement result. This might be far too late)

The only "reasonable" way to eagerly collect notifications is by polling the database with frequent statements right after the LISTEN command until the UNLISTEN command, but I don't like the idea of putting something like that in jOOQ.

In my opinion, Oracle AQ's API is much better implemented. There, you simply call DBMS_AQ.dequeue() which blocks until there's a message.

Anyway, I may be missing some feature that would greatly simplify this, so I'm very happy to discuss options.

[kag0]

@lukaseder very good points.

With the limitations on the postgres jdbc driver implementation, what do you think about just implementing LISTEN as non-blocking, returning whatever messages had been sent since the last notify? The user would be left to figure out polling interval, back-off, and such for themselves.
I'm not sure that's actually a good idea, it might be wiser to just wait for the jdbc driver to improve. Thought I'd just throw the idea out there anyway.

[lukaseder]

I'm really not sure. Compared to Oracle AQ, where I have extensive productive experience, I have no clue how people expect to use this feature in PostgreSQL. So, I'd prefer not to publish a new feature that doesn't respond to actual needs...

So, I'm really curious about actual needs, first.

[JanecekPetr]

Our desired usage is like this. We have three applications:

• one writing various kinds of data to the DB
• two different human-facing UI applications that show new "posts" when they arrive (new data in the DB), ergo they get notified that "their" data has been updated, and get the new/updated DB keys

Obviously the "you have new data, show it to the user" notifications can be implemented via any messaging API out there. It's just that the DB could, in an ideal world, be the only shared component between the applications, and the PG notify/listen mechanism seems to be designed to handle exactly this situation.

It's a shame JDBC sucks in this regard, but both the "classic" pgjdbc and pgjdbc-ng drivers have methods that do not require any dummy queries nor server polling - they're now fully client-waits-only.

E.g. The pgjdbc has had PGConnection.html#getNotifications() since forever (but was misimplemented/misdocumented) and PGConnection.html#getNotifications(int) (with a timeout) since 42.1.0, so that you can call getNotification(0) to exactly emulate the behaviour of DBMS_AQ.dequeue().

[lukaseder]

@JanecekPetr Thanks for the pointer, great to know this has been fixed. It was misimplemented, then, because it really didn't work at the time of my last comment. This now works:

try (Connection cn = new org.postgresql.Driver().connect(url, properties)) {
    cn.createStatement().execute("LISTEN channel");

    for (;;) {
        PGNotification[] ns = ((PGConnection) cn).getNotifications(0);

        if (ns != null)
            for (PGNotification n : ns)
                System.out.println(n.getName() + ":" + n.getParameter() + ":" + n.getPID());

        break;
    }
}

The following notifications sent from a SQL client are printed in the Java process:

NOTIFY channel, 'x';
NOTIFY channel, '1';

I'll consider how this could be added to the PostgresDSL for simplification of the API usage. In Oracle, the AQ integration is quite cool given that the payloads are typed objects. In PostgreSQL, there is less opportunity for making this better, but I can still investigate.

[davinkevin]

Could be a good match with Reactive producer API and with a potential r2dbc implementation 😍.

I will have to do the same for an app really soon, so I will post feedback if possible 😉