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
