Notification Listener freezese

[M3ssman]

Hello out there,

actually, I'm running into trouble implementing the PGNotificationListenerInterface. When the Servlet starts-up. the Notification-Listener works fine, but after some time (about 15 minutes or more), the Listener is not doing anything at all. I tried also to implement re-start Logic inside PGNotificationListener#closed, but this Event gets never fired.

Are there any known Issues related to Socket-Connections, especially in context of Windows or Tomcat? Official Docs (http://impossibl.github.io/pgjdbc-ng/docs/current/user-guide) mentions only Wildfly explicitly. The common Postgres-Docs (https://jdbc.postgresql.org/documentation/head/listennotify.html) close the Statement-Resource after the Listener is attached. I tried to do alike, but this time everything explodes, literally.

Overall, it looks like some kind of Resource Problem, it seems to degenerate faster when more Notifications are sent. Unfortunately, I'm off any ideas where to locate the bottleneck.

Further, the pgjdbc-ng docs confuse me. In section 6, JDBC-Connections, they state a Method PGConnection#setNetworkTimeout(Integer), but programmatically I can only use PGConnection#setNetworkTimeout(Executor,Integer).

Platform

Windows Server 2016 OpenJDK 8 Tomcat 8.5.34 pgjdbc-ng-0.8.2 netty-all-4.1.34.Final netty-tcnative-2.0.25.Final-linux-x86_64 netty-tcnative-2.0.25.Final-windows-x86_64

[M3ssman]

The official Postgres-Docs (please see above) do close the Statement-Object after attaching to a Channel, where the example Snippet at pgjdbc-ng-Docs doesn't. Is this just missing in the later one or it is not necessary? Currently, I'm not closing the Statement after executing LISTEN <mychannel>. Can this result in more and more open DB-Connections that finally halt the system?

[kdubb]

Apologies for the delayed response... Can you run this with protocol tracing enabled, maybe directed to a file, to see if it's that the listener is not getting called or if PostgreSQL is not sending notifications?

[kdubb]

Also, I'm happy to help but a small project that reproduces the issue would go a long way to getting this worked out.

[M3ssman]

I switched Job Position, so I can't access one of those Windows-Server-Machines right away.

Since on an Ubuntu-Machine the Process runs without Issues (unfortunately, it was decided to deploy App on a Windows-Machine ... ) , I assume that there are some Basic Issues regarding the OS or the Netty Libs, but nothing to blame pgjdbc-ng for.

How do I enable protocol tracing?

Libraries used:

• pgjdbc-ng-0.8.2.jar: Postgres-Library
• netty-all-4.1.34.Final.jar: Socket Core
• netty-tcnative-2.0.25.Final-linux-x86_64.jar: Unix-Socket Implementation
• netty-tcnative-2.0.25.Final-windows-x86_64.jar: Windows 64Bit Socket Implementation

Code:

// servlet init method
Class.forName("com.impossibl.postgres.jdbc.PGDriver");

// some other Servlet method
String url = config.getProperty("dbUrl");
String user = config.getProperty("dbUser");
String pass = config.getProperty("dbPass");
conn = DriverManager.getConnection(url, user, pass).unwrap(PGConnection.class);
try (Statement stmt = conn.createStatement()) {
     stmt.execute("LISTEN sosdruck");
     MyListener m = (pid, cn, load) -> {
         LOGGER.info("get lambda notification from channel '" + cn + "' (pid: " + pid + ") => '" + load + "'!");
    };
    conn.addNotificationListener(m);
} catch (SQLException e) {
     LOGGER.error("SQL: ", e);
}

There's no magic done like some wired Polling, just plain from the Documentation.

Further, I tried to wrap the Connection/Reconnection-Process to be done with timeoutExecutorPool.scheduleAtFixedRate(() -> { connection-refresh-logic }, 0, 15, TimeUnit.MINUTES) but this didn't work either.

[poser55]

How do I enable protocol tracing? I assume they mean: https://impossibl.github.io/pgjdbc-ng/docs/current/user-guide/#tracing-protocol

---

We have potentially a related issue: the notification works fine for some time (for weeks) and then it stops until a restart of the process (we have 2 same processes). What is strange: the issue occurs typically on all (same) processes that should get the notification at the same time - so either it works everywhere or not at all.

[dannyboyer]

The official Postgres-Docs (please see above) do close the Statement-Object after attaching to a Channel, where the example Snippet at pgjdbc-ng-Docs doesn't. Is this just missing in the later one or it is not necessary? Currently, I'm not closing the Statement after executing LISTEN <mychannel>. Can this result in more and more open DB-Connections that finally halt the system?

I am very interested in having an answer on that as I am also not sure if I should close the Statement or not. Thanks!

[kdubb]

The listening statement does not need to remain open. I modified the unit test to show that it works...

@Test
public void testSimpleNotification() throws Exception {

  try (PGConnection conn = TestUtil.openDB().unwrap(PGConnection.class)) {

    final CountDownLatch latch = new CountDownLatch(1);
    final AtomicBoolean flag = new AtomicBoolean(false);
    PGNotificationListener notificationListener = new PGNotificationListener() {

      @Override
      public void notification(int processId, String channelName, String payload) {
        latch.countDown();
        flag.set(true);
      }

    };

    conn.addNotificationListener(notificationListener);

    try (Statement stmt = conn.createStatement()) {

      stmt.execute("LISTEN TestChannel");
    }

    try (Statement stmt = conn.createStatement()) {
      stmt.execute("NOTIFY TestChannel");
    }

    latch.await();

    assertTrue(flag.get());
  }

}

[stodge]

Interesting, I'm having the same problem. I haven't worked out whether the DB is not sending notifications or if there's a problem in the Java code. I'm using 0.8.2 but I'll have to try 0.8.6 to see if that makes a difference.

[stodge]

Is there anyway the listen "connection" could be closed due to inactivity? My app is listening to a table that doesn't get updated that often. Do I need to perform some kind of SQL query to keep it alive?

[kdubb]

If it was being closed due to a timeout or some network issue, you would get a PNotificationListener.closed() call.

[kdubb]

At that point you can establish a new connection or whatever you need to do.

[dengkang]

I recently encountered the same problem. Version used pgjdbc-ng:0.8.9.The service deployed in docker failed to listen to the Notification after restarting. It was working normally before restarting. I observed the log and found that it was automatically closed 1 minute after the service was started. The problem is in the local win system. Debugging did not reproduce. The listen session of Notification is connected for a long time. At present, my processing method is to add retry in the close() of listen, establish database connection again, and add monitoring. But closing once a minute makes me very distressed, I don't know how to locate this problem, looking forward to your guidance, thank you