RE: SQL to query running transactions with subtransactions that exceeds 64

["Li EF Zhang" <bjzhangl@xxxxxxxxxx>]

Thanks for your reply. I read it.
When I restart secondary node, it reported "DEBUG:  recovery snapshot waiting for non-overflowed snapshot or until oldest active xid on standby is at least 4739126 (now 1422751)#0122020-12-21 00:00:24.415 UTC [146-15455940] CONTEXT:  WAL redo at 4/741941F8 for Standby/RUNNING_XACTS: nextXid 5137417 latestCompletedXid 5137409 oldestRunningXid 1422751; 17 xacts: 2716862 2721890 4665244 2495592 2289138 5137416 2288820 2287653 1422751 4280517 2288510 2287620 3297674 1757103 5137219 3320989 2259670; subxid ovf"
I want to find the oldest running transaction(1422751) using the following SQL. But it showed that there was no transactions that running more than 5 minutes. 
How can I find the oldest running transaction?

SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

Thanks,

Lily

----- Original message -----
From: Laurenz Albe <laurenz.albe@xxxxxxxxxxx>
To: Li EF Zhang <bjzhangl@xxxxxxxxxx>, pgsql-general@xxxxxxxxxxxxxxxxxxxx
Cc:
Subject: [EXTERNAL] Re: SQL to query running transactions with subtransactions that exceeds 64
Date: Wed, Jan 6, 2021 12:49 PM
 

On Tue, 2021-01-05 at 10:42 +0000, Li EF Zhang wrote:
> I am new to postgresql and sql. I want to check running transactions whose
>  subtransactions exceeds 64. Is there any SQL statement or other method can
>  do this? Thanks!

You may want to read
https://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/ 

You could export a snapshot with pg_export_snapshot() and see if the resulting file
contains the line "sof:1".

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com