Re: Logical replication fails due to SocketException - Mailing list pgsql-jdbc

On Tue, 21 May 2019 at 15:49, Alex Maltinsky <alex@bgprotect.com> 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 <pg@fastcrypt.com> wrote:

On Tue, 21 May 2019 at 09:58, Alex Maltinsky <alex@bgprotect.com> 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

davec@postgresintl.com

www.postgresintl.com

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

Dave Cramer

davec@postgresintl.com

www.postgresintl.com