SQL to query running transactions with subtransactions that exceeds 64

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

<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.albe@cybertec.at&gt;<br>To: Li EF Zhang &lt;bjzhangl@cn.ibm.com&gt;, pgsql-general@lists.postgresql.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/&quot; target="_blank">https://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/&lt;/a&gt;&amp;nbsp;&lt;br&gt;&lt;br&gt;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&quot; target="_blank">https://www.cybertec-postgresql.com&lt;/a&gt;&amp;nbsp;&lt;/font&gt;&lt;br&gt;&amp;nbsp;&lt;/div&gt;&lt;/blockquote&gt;
<div dir="ltr" >&nbsp;</div></div><BR>

On Wed, 2021-01-06 at 11:14 +0000, Li EF Zhang wrote:

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)
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';

Use xact_start rather than query_start.

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