PgConnection.getNotifications() is empty when using Hikari

Question

I need to implement LISTEN/NOTIFY PostgreSQL in spring app. I am using spring-boot-starter-data-jpa 2.5.8. When I Autowired DataSource, by default it is HikariDataSource, to receive notifications, I need PgConnection which I get from Connection from DataSource, all this is wrapped by HikariProxyConnection, but in this case, I always receive an empty notification array.

code example

In case I am getting the connection using DriverManager.getConnection(url, "user", "pwd") all works if fine a I receive notifications.

Do you have some ideas how it can be solved?

Answer 1

Your connection is set to autoCommit = false, so the LISTEN statement is never committed.

To fix this, you need to .commit() your LISTEN statement, or change HikariCP to use autocommit.

    val pgconnection = hikariDataSource.connection
        .unwrap(PgConnection::class.java)

    val stmt = pgconnection.createStatement()
    stmt.execute("LISTEN mymessage")
    stmt.close()
    pgconnection.commit() // Here's the fix!

    while (true) {
        val notifications = pgconnection.getNotifications(Int.MAX_VALUE)

        println(notifications)
        println("Size: ${notifications.size}")
        for (notification in notifications) {
            println(notification)
        }
    }
}

There's even a issue on HikariCP's GitHub, however trying to find this issue was hard, because for some reason it doesn't show up on Google. I was only able to find this issue because Brett created a gist trying to replicate the reported issue and the class name has the issue ID. https://github.com/brettwooldridge/HikariCP/issues/828