From: Álvaro Herrera <alvherre@kurilemu.de>
To: Alexandra Wang <alexandra.wang.oss@gmail.com>
Cc: Daniil Davydov <3danissimo@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: LISTEN/NOTIFY bug: VACUUM sets frozenxid past a xid in async queue
Date: Wed, 6 Aug 2025 12:44:13 +0200
Message-ID: <202508061044.ptcyt7aqsaaa@alvherre.pgsql> (raw)
In-Reply-To: <CAK98qZ3wZLE-RZJN_Y+TFjiTRPPFPBwNBpBi5K5CU8hUHkzDpw@mail.gmail.com>
On 2025-Aug-05, Alexandra Wang wrote:
> I'm bringing up a bug that was reported multiple times [1][2][3] in
> the bugs list here, for a broader audience.
>
> The issue is that an ERROR like the one below occurs when trying to
> register any listener in the database.
>
> test=# listen c21;
> ERROR: 58P01: could not access status of transaction 14279685
> DETAIL: Could not open file "pg_xact/000D": No such file or directory.
> LOCATION: SlruReportIOError, slru.c:1087
Oh, interesting problem. Many thanks for the excellent write-up.
> My questions:
>
> 1. Is it acceptable to drop notifications from the async queue if
> there are no active listeners? There might still be notifications that
> haven’t been read by any previous listener.
I'm somewhat wary of this idea -- could these inactive listeners become
active later and expect to be able to read their notifies?
> 2. If the answer to 1 is no, how can we teach VACUUM to respect the
> minimum xid stored in all AsyncQueueEntries?
Maybe we can have AsyncQueueAdvanceTail return the oldest XID of
listeners, and back off the pg_clog truncation based on that. This
could be done by having a new boolean argument that says to look up the
XID from the PGPROC using BackendPidGetProc(QUEUE_BACKEND_PID) (which
would only be passed true by vac_update_datfrozenxid(), to avoid
overhead by other callers), then collect the oldest of those and return
it.
This does create the problem that an inactive listener could cause the
XID counter to stay far in the past. Maybe we could try to avoid this
by adding more signalling (e.g, AsyncQueueAdvanceTail() itself could
send PROCSIG_NOTIFY_INTERRUPT signal?), and terminating backends that
are way overdue on reading notifies. I'm not sure if this is really
needed or useful; consider a backend stuck on SIGSTOP (debugger or
whatever): it will just sit there forever.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
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-hackers@postgresql.org
Cc: alvherre@kurilemu.de, alexandra.wang.oss@gmail.com, 3danissimo@gmail.com
Subject: Re: LISTEN/NOTIFY bug: VACUUM sets frozenxid past a xid in async queue
In-Reply-To: <202508061044.ptcyt7aqsaaa@alvherre.pgsql>
* 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