COPY FROM STDIN hang - Mailing list pgsql-jdbc

From Robert Creager
Subject COPY FROM STDIN hang
Date
Msg-id CBE1068B-305A-436E-9143-D5D1E414F773@logicalchaos.org
Whole thread Raw
Responses Re: COPY FROM STDIN hang  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-jdbc
PostgreSQL 9.6.17 on amd64-portbld-freebsd12.1, compiled by FreeBSD clang version 9.0.1
(git@github.com:llvm/llvm-project.gitc1a0a213378a458fbea1a5c77b315c7dce08fd05) (based on LLVM 9.0.1), 64-bit 
9.3-1104-jdbc41
Java 1.8.0_242

We’re getting occasional hangs when using COPY FROM STDIN.  Note that we can reliably reproduce this problem on our
applianceafter a few hours of runtime with a specific workload.  Namely, doing 30-50 COPY operations per second. Each
queryis copying a few key/value metadata pairs, nothing significant. 

   java.lang.Thread.State: RUNNABLE
        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:143)
        at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:112)
        at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:71)
        at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:282)
        at org.postgresql.core.v3.QueryExecutorImpl.processCopyResults(QueryExecutorImpl.java:930)
        at org.postgresql.core.v3.QueryExecutorImpl.endCopy(QueryExecutorImpl.java:828)
        - locked <43521773> (a org.postgresql.core.v3.QueryExecutorImpl)
        at org.postgresql.core.v3.CopyInImpl.endCopy(CopyInImpl.java:59)
        at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:203)
        at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:183)
…

This is the only query running on the server at the time when we get the hang.  I’m unable to cancel or terminate the
query,I have to kill -9 the pid from the command line.  When the COPY operations come in at a more limited pace, we
don’tsee this issue. 

(SELECT pid,
    client_port,
    now() - query_start AS "runtime",
    query_start,
    datname,
    state,
    wait_event_type,
    query,
    usename
FROM pg_stat_activity
WHERE query !~ 'pg_stat_activity' AND
    state != 'idle'
ORDER BY state, runtime DESC;)

12976    14322    0 years 0 mons 0 days 13 hours 35 mins 5.073483 secs    2021-03-23 07:02:22.892034    tapesystem
active       COPY ds3.s3_object_property (id, key, object_id, value) FROM STDIN WITH DELIMITER AS '|'    Administrator 

Thoughts?

Thanks,
Robert


pgsql-jdbc by date:

Previous
From: Andy Fan
Date:
Subject: Re: Where col like 'abc%' with PreparedStatement
Next
From: Tom Lane
Date:
Subject: Re: COPY FROM STDIN hang