Re: Timeouts in Postgresql JDBC Driver

Increase the TCP keepalive window. It should fix your issue. To debug more establish and snif the packets from your client end.

Wireshark should say what's happening.

Get Outlook for Android<https://aka.ms/AAb9ysg>
________________________________
From: Debraj Manna <subharaj(dot)manna(at)gmail(dot)com>
Sent: Saturday, June 1, 2024 5:18:02 PM
To: Muhammad Ikram <mmikram(at)gmail(dot)com>
Cc: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Timeouts in Postgresql JDBC Driver

Thanks for replying. I am using hikari so following defaults are already set.

connectionTimeout 30 secs
idleTimeout 10 mins

In this case shouldn't the client connection be timed out rather than hanging?

On Sat, 1 Jun, 2024, 16:23 Muhammad Ikram, <mmikram(at)gmail(dot)com<mailto:mmikram(at)gmail(dot)com>> wrote:
Hi Debraj,

See which of these option works best for you

Client side Timeouts ... socketTimeout, loginTimeout, connectTimeout
Server side Timeouts ... statement_timeout
If you are using connection pooling e.g. HikariCP then you may set setConnectionTimeout, setIdleTimeout, setMaxLifetime

Regards,
Muhammad Ikram
bitnine

On Sat, Jun 1, 2024 at 2:41 PM Debraj Manna <subharaj(dot)manna(at)gmail(dot)com<mailto:subharaj(dot)manna(at)gmail(dot)com>> wrote:
Hi

I am using Postgresql JDBC Driver 42.7.3 with Postgres 14.9 with default settings.

I am observing sometimes my Postgres Client thread getting stuck like below. I have observed it for about ~12 hours and the thread was stuck like below. Can someone let me know if there are some known issues with the JDBC Driver that I am using or are there any timeout settings that I can set in either the Postgres server or JDBC client level that will allow me to not land in this situation

pool-9-thread-1" #352 prio=5 os_prio=0 cpu=28470.75ms elapsed=175425.73s tid=0x00007fd1a0053e50 nid=0x1cb runnable [0x00007fd17ecfd000]
java.lang.Thread.State: RUNNABLE
at sun.nio.ch.Net.poll(java(dot)base(at)17(dot)0(dot)10/Native Method)
at sun.nio.ch.NioSocketImpl.park(java(dot)base(at)17(dot)0(dot)10/NioSocketImpl.java:186)
at sun.nio.ch.NioSocketImpl.park(java(dot)base(at)17(dot)0(dot)10/NioSocketImpl.java:195)
at sun.nio.ch.NioSocketImpl.implRead(java(dot)base(at)17(dot)0(dot)10/NioSocketImpl.java:319)
at sun.nio.ch.NioSocketImpl.read(java(dot)base(at)17(dot)0(dot)10/NioSocketImpl.java:355)
at sun.nio.ch.NioSocketImpl$1.read(java(dot)base(at)17(dot)0(dot)10/NioSocketImpl.java:808)
at java.net.Socket$SocketInputStream.read(java(dot)base(at)17(dot)0(dot)10/Socket.java:966)
at sun.security.ssl.SSLSocketInputRecord.read(java(dot)base(at)17(dot)0(dot)10/SSLSocketInputRecord.java:484)
at sun.security.ssl.SSLSocketInputRecord.readHeader(java(dot)base(at)17(dot)0(dot)10/SSLSocketInputRecord.java:478)
at sun.security.ssl.SSLSocketInputRecord.bytesInCompletePacket(java(dot)base(at)17(dot)0(dot)10/SSLSocketInputRecord.java:70)
at sun.security.ssl.SSLSocketImpl.readApplicationRecord(java(dot)base(at)17(dot)0(dot)10/SSLSocketImpl.java:1465)
at sun.security.ssl.SSLSocketImpl$AppInputStream.read(java(dot)base(at)17(dot)0(dot)10/SSLSocketImpl.java:1069)
at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:162)
at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:129)
at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:114)
at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:74)
at org.postgresql.core.PGStream.receiveChar(PGStream.java:467)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2166)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:371)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:502)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:419)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:194)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:180)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
...
at com.sp.gossiper.producers.GossiperProducerScheduler.readCheckpoint(GossiperProducerScheduler.java:369)

--
Muhammad Ikram