Date: Sat, 5 Jun 2021 13:55:45 -0700
From: Noah Misch <noah@leadboat.com>
To: Stepan Yankevych <Stepan_Yankevych@epam.com>
Cc: Stepan Yankevych <stepya@ukr.net>,
	"sergii.zhuravlev@smartnet.ua" <sergii.zhuravlev@smartnet.ua>,
	"pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #16961: Could not access status of transaction
Message-ID: <20210605205545.GE228552@rfd.leadboat.com>
References: <16961-25f29f95b3604a8a@postgresql.org>
 <1618416199.969761000.ago134mu@frv53.fwdcdn.com>
 <VE1PR03MB531295B1BDCFE422441B15FD92499@VE1PR03MB5312.eurprd03.prod.outlook.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <VE1PR03MB531295B1BDCFE422441B15FD92499@VE1PR03MB5312.eurprd03.prod.outlook.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Archived-At: 
 <https://www.postgresql.org/message-id/20210605205545.GE228552%40rfd.leadboat.com>
Precedence: bulk

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.