Re: An issue in JDBC replication API

On Sat, Jun 20, 2020 at 11:32 PM Dave Cramer <davecramer@postgres.rocks> wrote:

On Sat, 20 Jun 2020 at 14:23, Abbas Butt <abbas.butt@enterprisedb.com> wrote:

Hi,

On Sat, Jun 20, 2020 at 7:37 PM Dave Cramer <davecramer@postgres.rocks> wrote:

Dave Cramer

www.postgres.rocks

On Fri, 19 Jun 2020 at 19:37, Dave Cramer <davecramer@postgres.rocks> wrote:

Hi Abbas,

Thanks for this I seem to recall something about this.

Dave Cramer

www.postgres.rocks

On Fri, 19 Jun 2020 at 16:40, Abbas Butt <abbas.butt@enterprisedb.com> wrote:

Hi,

I have found that the JDBC replication API does not handle the keep alive messages sent by the server correctly.

A replication client cannot ignore even a single keep alive message from the server and it must respond to it.

If it does not the server will stop WAL sender with the following error:

LOG:  terminating walsender process due to replication timeout
LOG:  disconnection: session time: 0:02:00.173 user=replicant database=edb host=127.0.0.1 port=57900

and this is exactly what is happening when I use JDBC replication API.

The JDBC driver is ignoring the server's keep alive messages and hence the server stops the WAL sender and drops the connection.

Attached please find a sample program that I have mostly copied from the documentation available here

https://jdbc.postgresql.org/documentation/head/replication.html#logical-replication

When we run this program and do not do any activity on the database server (I have tested with server version 12 and JDBC 42.2.14), after some time (wal_sender_timeout/2 seconds to be precise)  the server stops the WAL sender process and drops the connection.

To use the program follow these steps:

Issue the following commands to the database server:

./createuser --superuser --replication -h 127.0.0.1 -p 7777 -U postgres replicant
select pg_drop_replication_slot('radium');

CREATE TABLE numbers(a int PRIMARY KEY, b varchar(255));
GRANT SELECT on numbers to replicant;
INSERT INTO numbers VALUES(10, 'ten'),(20,'twenty'),(30,'thirty');
CREATE PUBLICATION barium for TABLE numbers;

To run the program use the following commands:
mvn clean
mvn package
java -cp target/Lithium-1.0-SNAPSHOT.jar:/home/abbas/tmp/postgresql-42.2.14.jar com.edb.App

If my analysis is correct, can we please have this issue fixed in the driver?

Best Regards

I'll have a look, but we do attempt to handle it here https://github.com/pgjdbc/pgjdbc/blob/f89e62cdce4895f163dc95353cd31614347624b6/pgjdbc/src/main/java/org/postgresql/core/v3/replication/V3PGReplicationStream.java#L137

I had a look at that part of the code. Assuming that the server needs reply only if replyRequired (last byte of the keep alive message) is 1 is unfortunately not correct. Server is expecting a reply irrespective of the replyRequired value. I have tested with libpq as well and I can confirm that the last byte of the keep alive message should simply be ignored by the client, and a reply must be sent to each keep alive message.

 

Dave

 

Can you try changing that line to see if it fixes it ?

Sure.

 

Dave

--

--
Abbas

Architect

Ph: 92.334.5100153

Skype ID: gabbasb

www.enterprisedb.com

Follow us on Twitter
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars, whitepapers and more