Обсуждение: BUG #16961: Could not access status of transaction

The following bug has been logged on the website:

Bug reference:      16961
Logged by:          Sergey Zhuravlev
Email address:      sergii.zhuravlev@smartnet.ua
PostgreSQL version: 13.2
Operating system:   CentOS Linux release 7.9.2009 (Core)
Description:

Hello

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
"
Current transaction
# select txid_current();
 txid_current
--------------
   6985716158

Parameter -  autovacuum_freeze_max_age = 200000000

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.

Noah Misch <noah@leadboat.com> writes:
> On Mon, Apr 19, 2021 at 06:49:15PM +0000, Stepan Yankevych wrote:
>> 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.

> ...
> 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.

I suspect the true state of affairs was more like "LISTEN works good in
any other databases".  The described symptoms are consistent with there
being a message in the NOTIFY queue that has a pruned-away xid.  An
incoming listener would try to scan over already-committed notify
messages, but testing to see whether this stale message is committed
would fail.  However, since asyncQueueProcessPageEntries just ignores
messages not targeted for the current database, incoming listeners
in other databases wouldn't notice the problem.

ISTM the interesting question here is what was holding back truncation
of the NOTIFY queue.  Could there have been an open transaction somewhere
that was failing to collect NOTIFY data?  But a transaction sitting open
for a month is likely to cause far more severe problems than that one.

> 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.

Perhaps.  I'm not sure how hard it is to extract the oldest xid in the
queue (where "oldest" is defined as "numerically smallest").  The entries
are in xid commit order which is a different thing.

            regards, tom lane

On Sat, Jun 05, 2021 at 05:25:39PM -0400, Tom Lane wrote:
> Noah Misch <noah@leadboat.com> writes:
> > On Mon, Apr 19, 2021 at 06:49:15PM +0000, Stepan Yankevych wrote:
> >> 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.
> 
> > ...
> > 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.
> 
> I suspect the true state of affairs was more like "LISTEN works good in
> any other databases".  The described symptoms are consistent with there
> being a message in the NOTIFY queue that has a pruned-away xid.  An
> incoming listener would try to scan over already-committed notify
> messages, but testing to see whether this stale message is committed
> would fail.  However, since asyncQueueProcessPageEntries just ignores
> messages not targeted for the current database, incoming listeners
> in other databases wouldn't notice the problem.
> 
> ISTM the interesting question here is what was holding back truncation
> of the NOTIFY queue.  Could there have been an open transaction somewhere
> that was failing to collect NOTIFY data?

Open transactions always decline to collect notify data, don't they?  See
ProcessNotifyInterrupt().

> But a transaction sitting open
> for a month is likely to cause far more severe problems than that one.

True.

> > 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.
> 
> Perhaps.  I'm not sure how hard it is to extract the oldest xid in the
> queue (where "oldest" is defined as "numerically smallest").  The entries
> are in xid commit order which is a different thing.

Yeah, it wouldn't be cheap in the general case.  The value could be a field in
pg_control, updated by a separate VACUUM NOTIFY, which autovacuum would also
run roughly as often as autovacuum visits template0.

Hi,

On Wed, Jun 25, 2025 at 10:29 AM Noah Misch <noah@leadboat.com> wrote:
>
> On Sat, Jun 05, 2021 at 05:25:39PM -0400, Tom Lane wrote:
> >
> > ISTM the interesting question here is what was holding back truncation
> > of the NOTIFY queue.  Could there have been an open transaction somewhere
> > that was failing to collect NOTIFY data?
>
> Open transactions always decline to collect notify data, don't they?  See
> ProcessNotifyInterrupt().
>
> > But a transaction sitting open
> > for a month is likely to cause far more severe problems than that one.
>
> True.
>

One of our clients faced this problem, and I found out that long
transactions may have nothing to do with it.

We have the following logic in the notify queue :
If there are no listeners within all databases, and we are calling
LISTEN, then we must iterate from 'tail' to 'head' of the queue and
check statuses of transactions (see Exec_ListenPreCommit).
If there is a pruned-away xid in the queue, we will try to access its
status and get an error.

Because the tail of the queue is not necessarily always advanced
forward by the listeners, we can get such error without any long lived
transactions.

> > > 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.
> >
> > Perhaps.  I'm not sure how hard it is to extract the oldest xid in the
> > queue (where "oldest" is defined as "numerically smallest").  The entries
> > are in xid commit order which is a different thing.
>
> Yeah, it wouldn't be cheap in the general case.  The value could be a field in
> pg_control, updated by a separate VACUUM NOTIFY, which autovacuum would also
> run roughly as often as autovacuum visits template0.

As a temporary solution, which almost completely eliminates the
possibility of such a situation, I suggest adding a
AsyncQueueAdvanceTail call to vacuum (inside vac_update_datfrozenxid).
I mean 'static asyncQueueAdvanceTail()' that should be made
'external'. Thus, we can get rid of the problem that I described
above.

But there is one more : if the listener client is lagging, the
AsyncQueueAdvanceTail call will not save us (because it cannot advance
tail any further than the listener's position in the queue).
Again, this may be due to a very high load, but not because the client
keeps the transaction open for a very long time.

The best solution is to teach vacuum to recognize the minimum xid in
constant time, but I didn't come up with any sane implementations.
What do you think?

--
Best regards,
Daniil Davydov
Postgres Professional

Hi,

On Fri, Aug 1, 2025 at 10:41 AM Alexandra Wang
<alexandra.wang.oss@gmail.com> wrote:
>
> I'm not sure what's the best solution here. When ComputeXidHorizons()
> determines the cutoff xid, it doesn't check asyncQueueControl for the
> sender's xids stored in each AsyncQueueEntry.  It make sense that the
> async queue itself isn't expected to survive a database restart, but
> should the sender's xids it stores be considered?
>
> What are your thoughts?
>

Finding the minimum xid in async queue may consume a lot of time, so I
don't think
that we should teach ComputeXidHorizons to look into async queue :
1) This is pretty "hot" function
2) We don't need minimal xid from async queue each time
ComputeXidHorizons is called

IMO, the best solution is to create something like the
"AsyncQueueMinXid" function and
call it in the beginning of vac_update_datfrozenxid. Thus,
newFrozenXid will be capped
by minimal sender's xid and autovacuum could not advance it too far.

If we want to find out the minimum xid in a reasonable amount of time, we need
something like a tree structure for xids from async queue (which will
be maintained
together with SLRU structures).

I still don't have a solution for it. Maybe we should write to the pgsql-hackers
mailing list with this problem?

BTW, I'll attach an interim solution of this problem to this letter
(explicitly advance
queue tail before advancing datfrozenxid).

--
Best regards,
Daniil Davydov

Hi,

On Wed, Aug 6, 2025 at 6:30 AM Alexandra Wang
<alexandra.wang.oss@gmail.com> wrote:
>
> Thank you for sharing your interim solution! Inspired by your solution, I found
> that there is an existing built-in function "pg_notification_queue_usage()"[1]
>
>> pg_notification_queue_usage () → double precision
>> Returns the fraction (0–1) of the asynchronous notification queue's maximum
>> size that is currently occupied by notifications that are waiting to be
>> processed. See LISTEN and NOTIFY for more information.
>
>
> This function calls asyncQueueAdvanceTail(). I think it's a similar workaround
> to your patch, but without code change.
>
> What do you think?
>

Yep, it can be used as a workaround. But obviously the user doesn't know when
this function should be called - it will become clear only when an error occurs,
that is, post factum. Thus, I think that it is better to add such a
functionality
to the autovacuum. This simple code will ensure that there are no
errors in most cases.

--
Best regards,
Daniil Davydov