Обсуждение: CopyManager.copyOut stuck in socket read

Поиск
Список
Период
Сортировка

CopyManager.copyOut stuck in socket read

От
Karthik Shivashankar
Дата:

Hi Team,

 

We are using a java application that does something like “COPY (select xyz from abc ) TO filename.csv.gz” using JDBC to postgres 9.3.11 server.

 

Java version: openjdk version "1.8.0_151"

Postgres server: PostgreSQL 9.3.11 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit

JDBC driver: postgresql-8.4-701.jdbc3.jar

 

·       The problem is that the COPY is just running there forever even though the table it is referring to has <10 columns with ~50 rows.

·       Thread dump indicates copyOut is stuck in socket read. There is no enforcement of a timeout in application nor we have added a statement_timeout.

·       We want to understand why it is stuck as it has been running for more than 3 days now.  Inspecting the target csv.gz file, it has no content in it yet.

·       The CPU utilization of the pid representing the query ( captured from pg_stat_activity ) shows close to 100%.  Is this some known issue?

·       I know we are in old version but would like to know if this is a bug that’s already seen? Looking at release notes, I’m not able to find out.

 

"OurThread" #21 prio=5 os_prio=0 tid=0x00007f37fc3ed570 nid=0x23ca7 runnable [0x00007f37e5d79000]

   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:135)

        at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:104)

        at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73)

        at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:259)

        at org.postgresql.core.v3.QueryExecutorImpl.processCopyResults(QueryExecutorImpl.java:893)

        at org.postgresql.core.v3.QueryExecutorImpl.startCopy(QueryExecutorImpl.java:713)

        - locked <0x00000000fb497b68> (a org.postgresql.core.v3.QueryExecutorImpl)

        at org.postgresql.copy.CopyManager.copyOut(CopyManager.java:63)

        at org.postgresql.copy.CopyManager.copyOut(CopyManager.java:103)

        at  etl.task.extract.SelectFromDatabaseTableTask.execute(SelectFromDatabaseTableTask.java:118)

        at  etl.jobprocessor.JobApplication.mainline(JobApplication.java:250)

        at  etl.application.Application.run(Application.java:258)

        at java.lang.Thread.run(Thread.java:748)

 

Thanks,

Karthik S

Disclaimer:
This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is considered confidential, proprietary, sensitive and/or otherwise legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.

Re: CopyManager.copyOut stuck in socket read

От
Sehrope Sarkuni
Дата:
On Wed, May 20, 2020 at 3:34 AM Karthik Shivashankar <kshivashank@sandvine.com> wrote:

We are using a java application that does something like “COPY (select xyz from abc ) TO filename.csv.gz” using JDBC to postgres 9.3.11 server.


A COPY command which outputs to a server file must be executed as a regular query, not as a COPY statement. Use a regular JDBC java.sql.Statement, not the PGJDBC driver specific CopyManager classes.

The PGJDBC specific CopyManager classes are for COPY operations that read "... FROM STDIN" or write "... TO STDOUT".

I'm not sure exactly what's causing it to hang but I bet it's waiting for COPY related messages from the server that are never arriving because the executed command is not returning a COPY stream.

Regards,
-- Sehrope Sarkuni
Founder & CEO | JackDB, Inc. | https://www.jackdb.com/
 

RE: CopyManager.copyOut stuck in socket read

От
Karthik Shivashankar
Дата:

Thanks Sehrope. I will check further and get back to you.

 

From: Sehrope Sarkuni <sehrope@jackdb.com>
Sent: Wednesday, May 20, 2020 6:26 PM
To: Karthik Shivashankar <kshivashank@sandvine.com>
Cc: pgsql-jdbc@postgresql.org; Doug Fickling <dfickling@sandvine.com>; Padam Sonar <psonar@sandvine.com>; Raghavendra Chikmagalur Eswarappa <reswarappa@sandvine.com>
Subject: Re: CopyManager.copyOut stuck in socket read

 

[EXTERNAL]

On Wed, May 20, 2020 at 3:34 AM Karthik Shivashankar <kshivashank@sandvine.com> wrote:

We are using a java application that does something like “COPY (select xyz from abc ) TO filename.csv.gz” using JDBC to postgres 9.3.11 server.

 

A COPY command which outputs to a server file must be executed as a regular query, not as a COPY statement. Use a regular JDBC java.sql.Statement, not the PGJDBC driver specific CopyManager classes.

The PGJDBC specific CopyManager classes are for COPY operations that read "... FROM STDIN" or write "... TO STDOUT".

 

I'm not sure exactly what's causing it to hang but I bet it's waiting for COPY related messages from the server that are never arriving because the executed command is not returning a COPY stream.


Regards,

-- Sehrope Sarkuni

Founder & CEO | JackDB, Inc. | https://www.jackdb.com/

 

Disclaimer:
This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is considered confidential, proprietary, sensitive and/or otherwise legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.