Re: Logical replication fails due to SocketException

On Tue, 21 May 2019 at 15:49, Alex Maltinsky <[email protected]> wrote:

> The server runs a dockerized Postgres on Ubuntu.
>
> The client is on Windows 10.
>
> On Tue, 21 May 2019 at 21:22 Dave Cramer <[email protected]> wrote:
>
>> On Tue, 21 May 2019 at 09:58, Alex Maltinsky <[email protected]> wrote:
>>
>>> Hi All
>>>
>>> I ran into the a problem with Postgres 11 and JDBC driver 42.2.5 which
>>> resembles a problem that was posted here before (
>>> https://www.postgresql-archive.org/postgresql-Logical-Replication-Stream-fails-with-Database-connection-failed-when-reading-from-copy-td6036639.html),
>>> but unfortunately the solution was never posted here.
>>>
>>> I have a simple endless loop that follows the official replication
>>> example (https://jdbc.postgresql.org/documentation/head/replication.html),
>>> and I keep getting socket exceptions like these after fetching
>>> approximately 66K rows with remarkable consistency:
>>>
>>>     Exception in thread "main" org.postgresql.util.PSQLException:
>>> Database connection failed when reading from copy
>>>     at
>>> org.postgresql.core.v3.QueryExecutorImpl.readFromCopy(QueryExecutorImpl.java:1037)
>>>     at
>>> org.postgresql.core.v3.CopyDualImpl.readFromCopy(CopyDualImpl.java:41)
>>>     at
>>> org.postgresql.core.v3.replication.V3PGReplicationStream.receiveNextData(V3PGReplicationStream.java:155)
>>>     at
>>> org.postgresql.core.v3.replication.V3PGReplicationStream.readInternal(V3PGReplicationStream.java:124)
>>>     at
>>> org.postgresql.core.v3.replication.V3PGReplicationStream.readPending(V3PGReplicationStream.java:78)
>>>     at com.example.main(ReplicationTest.java:48)
>>>     Caused by: java.net.SocketException: socket closed
>>>     at java.net.SocketInputStream.socketRead0(Native Method)
>>>     at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
>>>     at java.net.SocketInputStream.read(SocketInputStream.java:171)
>>>     at java.net.SocketInputStream.read(SocketInputStream.java:141)
>>>     at
>>> org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:140)
>>>     at
>>> org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:109)
>>>     at
>>> org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:191)
>>>     at org.postgresql.core.PGStream.receive(PGStream.java:462)
>>>     at org.postgresql.core.PGStream.receive(PGStream.java:446)
>>>     at
>>> org.postgresql.core.v3.QueryExecutorImpl.processCopyResults(QueryExecutorImpl.java:1170)
>>>     at
>>> org.postgresql.core.v3.QueryExecutorImpl.readFromCopy(QueryExecutorImpl.java:1035)
>>>     ... 5 more
>>>
>>> The database log shows "LOG:  could not send data to client: Connection
>>> reset by peer"
>>>
>>> Wireshark shows that it was the client who suddenly sent a TCP RST to
>>> the server and closed the connection.
>>>
>>> Parameters: `wal_sender_timeout` is set to 60 seconds, and I'm using a
>>> status interval of 10 seconds and  TCP_KEEP_ALIVE is enabled.
>>>
>>> The body of the Java loop looks like this:
>>>
>>>         while (true) {
>>>             ByteBuffer msg = stream.readPending();
>>>             if (msg == null) {
>>>                 TimeUnit.MILLISECONDS.sleep(10L);
>>>                 continue;
>>>             }
>>>
>>>             LogSequenceNumber lastReceiveLSN =
>>> stream.getLastReceiveLSN();
>>>             System.out.println((i++) + " " + lastReceiveLSN);
>>>
>>>             stream.setAppliedLSN(lastReceiveLSN);
>>>             stream.setFlushedLSN(lastReceiveLSN);
>>>         }
>>>
>>>
>>> Curiously enough, if I change the loop to the code below, the problem
>>> disappears:
>>>
>>>         while (true) {
>>>             ByteBuffer msg = stream.readPending();
>>>             if (msg == null) {
>>>                 TimeUnit.MILLISECONDS.sleep(10L);
>>>                 continue;
>>>             }
>>>
>>>             int offset = msg.arrayOffset();
>>>             byte[] source = msg.array();
>>>             int length = source.length - offset;
>>>
>>>             LogSequenceNumber lastReceiveLSN =
>>> stream.getLastReceiveLSN();
>>>             System.out.println((i++) + " " + lastReceiveLSN + " " + new
>>> String(source, offset, length));
>>>
>>>             stream.setAppliedLSN(lastReceiveLSN);
>>>             stream.setFlushedLSN(lastReceiveLSN);
>>>         }
>>>
>>>
>>> Any help would be appreciated
>>>
>>> - Alex
>>>
>>>
>> What OS are you using ?
>>
>>
>> Dave Cramer
>>
>> [email protected]
>> www.postgresintl.com
>>
>>
>>

Seems to be the common theme. Is there something about windows that drops
connections ?


Dave Cramer

[email protected]
www.postgresintl.com