Content-Type: text/plain; charset=UTF-8
Date: Wed, 20 Aug 2025 18:18:32 -0300
Message-Id: <DC7KGTXW3QSG.OZA24HONT78J@gmail.com>
From: "Matheus Alcantara" <matheusssilv97@gmail.com>
To: "Daniil Davydov" <3danissimo@gmail.com>
Cc: =?utf-8?q?=C3=81lvaro_Herrera?= <alvherre@kurilemu.de>, "Alexandra Wang"
 <alexandra.wang.oss@gmail.com>, "PostgreSQL Hackers"
 <pgsql-hackers@postgresql.org>
Subject: Re: LISTEN/NOTIFY bug: VACUUM sets frozenxid past a xid in async
 queue
MIME-Version: 1.0
References: 
 <CAK98qZ3wZLE-RZJN_Y+TFjiTRPPFPBwNBpBi5K5CU8hUHkzDpw@mail.gmail.com>
 <202508061044.ptcyt7aqsaaa@alvherre.pgsql>
 <DBZN3PVLZ1UW.25XLKCUXYBVXV@gmail.com>
 <CAJDiXgg1ArRB1-6wLtXfVVnQ38P9Y+CDfEc9M2TXiOf_4kfBMg@mail.gmail.com>
 <CAFY6G8cnDOMZCfSvc62vER_9+HOs4EM52XromhOZki+=Ytv2sg@mail.gmail.com>
 <CAJDiXgheRHTnK8RJuBBcF0VjQA83wi81oX2s-GvBV3x+eSwBbA@mail.gmail.com>
 <CAFY6G8dyimRikP3nEK5gqMPnMZe5RTZjFvKvCFLJAvsajxK1fg@mail.gmail.com>
 <CAJDiXgjQZgHLFziG6sS6D5CGYqohMio673ydiRkkffW3WfXmEg@mail.gmail.com>
In-Reply-To: 
 <CAJDiXgjQZgHLFziG6sS6D5CGYqohMio673ydiRkkffW3WfXmEg@mail.gmail.com>
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/DC7KGTXW3QSG.OZA24HONT78J%40gmail.com>
Precedence: bulk

On Tue Aug 19, 2025 at 2:37 PM -03, Daniil Davydov wrote:
> Hi,
>
> On Tue, Aug 19, 2025 at 6:31=E2=80=AFPM Matheus Alcantara
> <matheusssilv97@gmail.com> wrote:
>>
>> On Tue Aug 19, 2025 at 12:57 AM -03, Daniil Davydov wrote:
>> > You have started a very long transaction, which holds its xid and prev=
ents
>> > vacuum from freezing it. But what if the backend is stuck not inside a
>> > transaction? Maybe we can just hardcode a huge delay (not inside the
>> > transaction) or stop process execution via breakpoint in gdb. If we wi=
ll use it
>> > instead of a long query, I think that this error may be reproducible.
>> >
>> But how could this happen in real scenarios? I mean, how the backend
>> could be stuck outside a transaction?
>>
>
> For now, I cannot come up with a situation where it may be possible.
> Perhaps, such a lagging may occur during network communication,
> but I couldn't reproduce it. Maybe other people know how we can achieve
> this?
>
Reading more the code I understand that once the a NOTIFY command is
received by a backend (and the transaction is committed) it will
emedialy signal all other listener backends and if the listener backend
is in idle it will consume the notification and then send it back to the
client as a PqMsg_NotificationResponse, so if there is a network delay
to send the notification from the listener backend back to the client I
don't think that it would be possible to get this error, because the
message was already dispatched by the backend and it will eventually get
to the client and once the notification is dispatched the backend
doesn't need to track it anymore (the queue pointers of the backend are
advanced after the dispatch).

Assuming that every SQL command is wrapped into a transaction (if it's
not already inside in) I think a busy listener backend will always
prevent the vacuum from freezing clog files past from its current xid,
so any notification that is sent while the backend is busy will not have
their transaction status removed from clog files anyway.

Is all these understandings and assumptions correct or am I missing
something here?

> I think that if such a situation may be possible, the suggestion to  dele=
te
> messages  will no longer be relevant. Therefore, first of all, I would li=
ke to
> clarify this issue.
>
From what I've understood until now it seems to me that this can happen
only if we have a notification on the queue without any listener, so the
notification may stay on the queue from a long time and a vacuum freeze
can be executed during this time and then when we have a new listener
(even for a different channel) it will fail to advance the pointers at
listener setup(Exec_ListenPreCommit()) because it would not be able to
get the transition status of this very old notification.

(please note that I'm not trying to invalidate your concern, I'm also
have this concern but unfortunately I'm unable to reproduce it and I'm
just sharing my thoughts to see if this issue is really possible or not)

--
Matheus Alcantara