Postgresql client : Connection reset by peer

[jaydev-ziroh]

Version

4.3.3
vertx-pg-client

Context

I am facing this exception when the pool is idle for some time (2 - 3 hours). This exception occours only on the initial connection. any further queries executed will not cause this issue.

java.io.IOException: Connection reset by peer at java.base/sun.nio.ch.FileDispatcherImpl.read0(Native Method) at java.base/sun.nio.ch.SocketDispatcher.read(SocketDispatcher.java:39) at java.base/sun.nio.ch.IOUtil.readIntoNativeBuffer(IOUtil.java:276) at java.base/sun.nio.ch.IOUtil.read(IOUtil.java:233) at java.base/sun.nio.ch.IOUtil.read(IOUtil.java:223) at java.base/sun.nio.ch.SocketChannelImpl.read(SocketChannelImpl.java:356) at io.netty.buffer.PooledByteBuf.setBytes(PooledByteBuf.java:258) at io.netty.buffer.AbstractByteBuf.writeBytes(AbstractByteBuf.java:1132) at io.netty.channel.socket.nio.NioSocketChannel.doReadBytes(NioSocketChannel.java:357) at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:151) at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:722) at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:658) at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:584) at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:496) at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:995) at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74) at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30) at java.base/java.lang.Thread.run(Thread.java:829)

My pool configuration is as follows

`class PostgresSqlFactory implements SqlFactory {

private final Vertx vertx;
private final PgConnectOptions connectOptions;
private final PoolOptions poolOptions;

public PostgresSqlFactory(Vertx vertx, DBCredentials credentials) {
    this.connectOptions = defaultConnectOptions(credentials).setPipeliningLimit(1);
    this.poolOptions = defaultPoolOptions();
    this.vertx = vertx;
}

private PoolOptions defaultPoolOptions() {
    var poolName = UUID.randomUUID().toString();
    return new PoolOptions()
            .setShared(true)
            .setName(poolName);
}

private PgConnectOptions defaultConnectOptions(DBCredentials credentials) {
    return new PgConnectOptions()
            .setPort(credentials.getPort())
            .setHost(credentials.getHost())
            .setDatabase(credentials.getDatabaseName())
            .setUser(credentials.getUsername())
            .setPassword(credentials.getPassword())
            .setReconnectAttempts(2)
            .setReconnectInterval(1000);
}

@Override
public SqlClient createClient() {
    return PgPool.client(vertx, connectOptions, poolOptions);
}

@Override
public PgPool createPool() {
    return PgPool.pool(vertx, connectOptions, poolOptions);
}

}`

with this above factory I create a client and execute queries as follows

sqlClient.preparedQuery(query).execute()

Do you have a reproducer?

This issue can be reproduced by the above code.

1. Create a simple http server that will have an api that communicates to postgres
2. use the above code for postgres connection.
3. start the server and hit the endpoint after a certain period of time.

Extra

• Adopt openjdk 11
• windows 11

Note : I wanted to switch to vertx-sql-client as suggested by another issue but it turns out that we cannot execute returing clause using sql client or I did not find any resource that says how.

[vietj]

can you provide a reproducer project @jaydev-ziroh ?

[jaydev-ziroh]

We have done some changes to our current structure and it seems to have resolved the issue we were facing. It was probably our lack of understanding how vertx works completly.
So we have several rest api's and each api runs in it own verticle. What we were doing is creating a single object of pool or pg client and sharing this same object throughout all the verticles. we switched to shared pool and each verticle had its own shared pool.
Along with this we set the keepAlive option to true (Which defaults to false) and set the idle timeout to 10 minutes.
This above setup seems to have resolved the issue we were facing.