From: Noah Misch <noah@leadboat.com>
To: Stepan Yankevych <Stepan_Yankevych@epam.com>
Cc: Stepan Yankevych <stepya@ukr.net>
Cc: sergii.zhuravlev@smartnet.ua <sergii.zhuravlev@smartnet.ua>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #16961: Could not access status of transaction
Date: Sat, 5 Jun 2021 13:55:45 -0700
Message-ID: <20210605205545.GE228552@rfd.leadboat.com> (raw)
In-Reply-To: <VE1PR03MB531295B1BDCFE422441B15FD92499@VE1PR03MB5312.eurprd03.prod.outlook.com>
References: <16961-25f29f95b3604a8a@postgresql.org>
<1618416199.969761000.ago134mu@frv53.fwdcdn.com>
<VE1PR03MB531295B1BDCFE422441B15FD92499@VE1PR03MB5312.eurprd03.prod.outlook.com>
On Mon, Apr 19, 2021 at 06:49:15PM +0000, Stepan Yankevych wrote:
> The database was rebooted and the issue disappeared
Each postmaster restart clears the LISTEN/NOTIFY queue, so that fits.
> The issue happening each morning when application starts on the production DataBase during about a month.
> Always the same transaction id is mentioned in the error (1954017648)
> We tried to do UNLISTEN - no changes. the same issue.
> LISTEN works good for any other channels.
>
>
> Can it be related to some hanged transaction? 1954017648? (for example while some network interruption)
Something like that; see below.
> Is it possible to kill/clean it somehow without DB restart?
Not to my knowledge.
> Can it be related to some non-vacuumed system table or so?
Probably not.
> Command - LISTEN missed_trades_empty_instrument
>
> ERROR: could not access status of transaction 1954017648
> DETAIL: Could not open file "pg_xact/0747": No such file or directory.
> STATEMENT: LISTEN missed_trades_empty_instrument
The LISTEN/NOTIFY queue stores a transaction id for each notification
(internally, each AsyncQueueEntry). I can imagine the "could not access
status" happening if a sequence of events like this happened since the last
postmaster restart:
backend 1: LISTEN missed_trades_empty_instrument
backend 2: BEGIN; NOTIFY missed_trades_empty_instrument [TransactionId N]
backend 1: BEGIN
backend 2: COMMIT
backend 1: CREATE TEMP TABLE x (); [sets TransactionId N+K]
autovacuum: freezes tuples, deletes pg_xact data covering TransactionId N
backend 1: COMMIT
On the other hand, if that's what happened, your report that "LISTEN works
good for any other channels" surprises me. Perhaps something completely
different happened on your system.
We could prevent the trouble if vac_truncate_clog() had access to the oldest
xid in the notification queue; it would set frozenXID to that value if
frozenXID would otherwise be older.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noah@leadboat.com, Stepan_Yankevych@epam.com, stepya@ukr.net, sergii.zhuravlev@smartnet.ua, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16961: Could not access status of transaction
In-Reply-To: <20210605205545.GE228552@rfd.leadboat.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox