Accessing Hikari connection from ratpack.server.Service with pgjdbc-ng

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Accessing Hikari connection from ratpack.server.Service with pgjdbc-ng

Massimo Lusetti
Hi all,
  I'm having trouble getting a Hikari connection from a Service implementation.

Basically the situation is the following.
I got Hikari correctly configured to use pgjdbc-ng (https://github.com/impossibl/pgjdbc-ng). To use this driver I needed to exlude netty dep from pgjdbc-ng but that shouldn't be the problem.
Then I implemented Service, in the onStart method I obtain a connection and fire a "LISTEN my_channel" on PostgreSQL to get notified by a custom trigger, the whole fit in the PGNotificationListener from pgjdbc-ng.

Now every time a particular query is issued in the DB, my trigger fire and I got notified on my_channel. Cool a simple event bus in the DB.

The problem start to happen when in the NotificationListener implementation I try to access the DB (from the same Hikari DataSource as above).
The connection fall in timeout and I cannot get to the DB.

I suspect something (a reference) is lost in the path between ratpack thread handling and pgjdbc-ng connection notification.

I can post some example code if you want.

Any hint is really appreciated.

Thanks
Massimo
Reply | Threaded
Open this post in threaded view
|

Re: Accessing Hikari connection from ratpack.server.Service with pgjdbc-ng

Massimo Lusetti
For reference here my own question on pgjdbc-ng issue: https://github.com/impossibl/pgjdbc-ng/issues/232 
rus
Reply | Threaded
Open this post in threaded view
|

Re: Accessing Hikari connection from ratpack.server.Service with pgjdbc-ng

rus
Some example code would be great.  Will see if anything jumps out.
Reply | Threaded
Open this post in threaded view
|

Re: Accessing Hikari connection from ratpack.server.Service with pgjdbc-ng

Massimo Lusetti
Thank rus for your interest.

In my main method I got this code:

.registry(Guice.registry(b ->
            b.module(HikariModule.class, hikariConfig -> {
                Database db = b.getServerConfig().get("/db", Database.class);

//                hikariConfig.setDataSourceClassName("com.impossibl.postgres.jdbc.PGDataSource");

                hikariConfig.addDataSourceProperty("host", db.getHostname());
                hikariConfig.addDataSourceProperty("database", db.getDatabase());
                hikariConfig.addDataSourceProperty("port", db.getPort());
                hikariConfig.addDataSourceProperty("user", db.getUsername());
                hikariConfig.addDataSourceProperty("password", db.getPassword());
            })
                    .module(AppModule.class)
                    .add(new PushNotificationService(
                            b.getServerConfig().get("/system", System.class)
                    )) // Listener to PostgreSQL notifications
            ))

Before I got normal configurations and after, all my handlers in the chain. Pretty usual till now.

Now the PushNotificationService onStart method:

public void onStart(StartEvent event) throws Exception
    {
        DataSource dataSource = event.getRegistry().get(DataSource.class);
        DbService dbService = event.getRegistry().get(DbService.class);

        NotificationListener listener = new NotificationListener(systemConfig, dataSource, dbService);

        try (Connection con = dataSource.getConnection())
        {
             PGConnection pgcon = con.unwrap(PGConnection.class);

            pgcon.addNotificationListener(listener);

            try (Statement stmt = con.createStatement()) {
                stmt.execute("LISTEN my_channel");
                stmt.close();
            }
        }
        catch (SQLException sqle)
        {
            throw new RuntimeException(sqle);
       }
        LOGGER.info("################################");
        LOGGER.info("Got start event: ",  event);
    }

Now within my NotificationListener I implement: https://github.com/impossibl/pgjdbc-ng/blob/develop/src/main/java/com/impossibl/postgres/api/jdbc/PGNotificationListener.java.

In the void notification(int processId, String channelName, String payload) method I will receive notification from postgresql as soon as anyone will fire "NOTIFY my_channel;" SQL command.

Here is my running notification method:

 void notification(int processId, String channelName, String payload) {

   LOGGER.info("Received message on channel: {}", channelName);

}

But as soon as I try to get a DataSource connection from within the notification method, the thread handling the notification hangs.

As I said in the issue on github the thread responsible for the notification is identified as "[PG-JDBC EventLoop (1)] " in the log messages.

I'm prone to think about some thread issues here cause both ratpack and pgjdbc-ng are handling a netty eventloop.

Hopefully I was clear enough to let you understand.
Thanks.
Reply | Threaded
Open this post in threaded view
|

Re: Accessing Hikari connection from ratpack.server.Service with pgjdbc-ng

Massimo Lusetti
Something has gone a little further in the driver side, see https://github.com/impossibl/pgjdbc-ng/issues/232#issuecomment-181953144

But I don't think this is the reason I cannot get connection from the ratpack/hikari pool from there. I'll try to test as soon as I find a spare cycle.
rus
Reply | Threaded
Open this post in threaded view
|

Re: Accessing Hikari connection from ratpack.server.Service with pgjdbc-ng

rus
In your listener, have you tried starting a new ratpack execution and doing the work in that?  You won't need to pass the datasource to your listener then and you'll be on a managed thread.
Reply | Threaded
Open this post in threaded view
|

Re: Accessing Hikari connection from ratpack.server.Service with pgjdbc-ng

Massimo Lusetti
Nope, I didn't.
Can you give me a practical idea on what do you mean with "starting a new ratpack execution", I understand it as a completely new ratpack server within a thread in another netty eventloop. Is that what you mean ?
rus
Reply | Threaded
Open this post in threaded view
|

Re: Accessing Hikari connection from ratpack.server.Service with pgjdbc-ng

rus
It's not a new server within a thread, here is a good description https://ratpack.io/manual/current/api/ratpack/exec/Execution.html

I also recommend reading http://ldaley.com/post/97376696242/ratpack-execution-model-part-1 and http://ldaley.com/post/102495950257/ratpacks-execution-model-in-practice

If your code is available I'll send you a PR.  If not I can post back here.
Reply | Threaded
Open this post in threaded view
|

Re: Accessing Hikari connection from ratpack.server.Service with pgjdbc-ng

Massimo Lusetti
Sorry, didn't thought about Exectution ... I'll try and come back.

Thank you very much for your time, really appreciated.