Re: SQL to query running transactions with subtransactions that exceeds 64 - Mailing list pgsql-general

From Laurenz Albe
Subject Re: SQL to query running transactions with subtransactions that exceeds 64
Date
Msg-id 74cc5b09ce921e4f57524ed76265847599dd42fc.camel@cybertec.at
Whole thread Raw
List pgsql-general
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
oldestRunningXid1422751; 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




pgsql-general by date:

Previous
From: Laurenz Albe
Date:
Subject: Re: Keep needing to run manual analyze
Next
From: Durumdara
Date:
Subject: Onfly Query - cumulative sum the stock change values by articles