Re: An issue in JDBC replication API

Hi,
I have tested that after applying the patch attached, the issue gets fixed.
I have tested with the master branch.
Regards

On Sun, Jun 21, 2020 at 10:50 AM Abbas Butt <abbas(dot)butt(at)enterprisedb(dot)com>
wrote:

>
> On Sat, Jun 20, 2020 at 11:32 PM Dave Cramer <davecramer(at)postgres(dot)rocks>
> wrote:
>
>>
>> On Sat, 20 Jun 2020 at 14:23, Abbas Butt <abbas(dot)butt(at)enterprisedb(dot)com>
>> wrote:
>>
>>> Hi,
>>>
>>> On Sat, Jun 20, 2020 at 7:37 PM Dave Cramer <davecramer(at)postgres(dot)rocks>
>>> wrote:
>>>
>>>>
>>>> Dave Cramer
>>>> www.postgres.rocks
>>>>
>>>>
>>>> On Fri, 19 Jun 2020 at 19:37, Dave Cramer <davecramer(at)postgres(dot)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(dot)butt(at)enterprisedb(dot)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.co <http://www.enterprisedb.com/>m
> <http://www.enterprisedb.com/>
>
> *Follow us on Twitter*
> @EnterpriseDB
>
> Visit EnterpriseDB for tutorials, webinars, whitepapers
> <http://www.enterprisedb.com/resources-community> and more
> <http://www.enterprisedb.com/resources-community>
>

--
--
*Abbas*
Architect

Ph: 92.334.5100153
Skype ID: gabbasb
www.enterprisedb.co <http://www.enterprisedb.com/>m
<http://www.enterprisedb.com/>

*Follow us on Twitter*
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars, whitepapers
<http://www.enterprisedb.com/resources-community> and more
<http://www.enterprisedb.com/resources-community>