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

<div class="socmaildefaultfont" dir="ltr" style="font-family:Arial, Helvetica, sans-serif;font-size:10pt" ><div dir="ltr" ><pre><code>Thanks for your reply. I read it.
When I restart secondary node, it reported "</code>DEBUG:&nbsp; 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:&nbsp; 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<code>"
I want to find the oldest running transaction(</code>1422751<code>) 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) &gt; interval '5 minutes';</code></pre></div>
<div dir="ltr" >Thanks,</div>
<div dir="ltr" >Lily</div>
<blockquote data-history-content-modified="1" dir="ltr" style="border-left:solid #aaaaaa 2px; margin-left:5px; padding-left:5px; direction:ltr; margin-right:0px" >----- Original message -----<br>From: Laurenz Albe &lt;laurenz(dot)albe(at)cybertec(dot)at&gt;<br>To: Li EF Zhang &lt;bjzhangl(at)cn(dot)ibm(dot)com&gt;, pgsql-general(at)lists(dot)postgresql(dot)org<br>Cc:<br>Subject: [EXTERNAL] Re: SQL to query running transactions with subtransactions that exceeds 64<br>Date: Wed, Jan 6, 2021 12:49 PM<br>&nbsp;
<div><font size="2" face="Default Monospace,Courier New,Courier,monospace" >On Tue, 2021-01-05 at 10:42 +0000, Li EF Zhang wrote:<br>&gt; I am new to postgresql and sql. I want to check running transactions whose<br>&gt; &nbsp;subtransactions exceeds 64. Is there any SQL statement or other method can<br>&gt; &nbsp;do this? Thanks!<br><br>You may want to read<br><a href="https://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/" target="_blank">https://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/</a>&nbsp;<br><br>You could export a snapshot with pg_export_snapshot() and see if the resulting file<br>contains the line "sof:1".<br><br>Yours,<br>Laurenz Albe<br>--<br>Cybertec | <a href="https://www.cybertec-postgresql.com" target="_blank">https://www.cybertec-postgresql.com</a>&nbsp;</font><br>&nbsp;</div></blockquote>
<div dir="ltr" >&nbsp;</div></div><BR>