Long statement execution. Data fetch by client

From: Pavel Suderevsky <psuderevsky(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Long statement execution. Data fetch by client
Date: 2019-02-20 16:21:24
Message-ID: CAEBTBzsxN1KHGAB8bw+CJowm+NPR6u6dZ55A6BANcH3QAumVRg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

OS: 7.5.1804
PG version: 9.6.12
Client: Java application (jdbc 42.0.0)

I have the issue with unexpectedly long statements:

LOG: duration: 9284.887 ms parse <unnamed>: SET SESSION CHARACTERISTICS
AS TRANSACTION ISOLATION LEVEL READ COMMITTED
LOG: duration: 12739.364 ms parse <unnamed>: SHOW TRANSACTION ISOLATION
LEVEL
LOG: duration: 10500.315 ms parse <unnamed>: SET SESSION CHARACTERISTICS
AS TRANSACTION ISOLATION LEVEL READ COMMITTED
LOG: duration: 10467.578 ms parse <unnamed>: SHOW TRANSACTION ISOLATION
LEVEL
LOG: duration: 25901.488 ms execute S_3: COMMIT

It's not a fsync issue. Another statements executed without issues during
execution of mentioned statements. pg_stat_statements didn't catch any such
long statement. No wait_events catched for these statements (checked by
taking pg_stat_activity snapshots every 100ms). Processes were in R state
(according atop, and related to that moments that were catched).
I have an assumption that issue is happening during data fetch by Java.
Could you please assist with the following questions?
1. Does query have 'active' state while data is being fetched by client?
2. Do I understand right that log_statement includes fetch time when
records duration time in log, while pg_stat_statements includes only
server-side computation time?
3. Is there any way to catch client's long fetch event on PostgreSQL server
side?
4. Is that all about FETCH or what else can cause such long execution of
statements like 'SHOW TRANSACTION ISOLATION LEVEL'?

Thank you very much in advance.
--
Pavel Suderevsky

Browse pgsql-general by date

  From Date Subject
Next Message Rob Nikander 2019-02-20 16:45:59 Re: procedures and transactions
Previous Message Adrian Klaver 2019-02-20 16:13:45 Re: Plpythonu extension