Mime-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset=UTF-8
Date: Wed, 03 Sep 2025 18:14:40 -0300
Message-Id: <DCJH5HX8X3B4.O9P9P8VKKO1F@gmail.com>
Cc: "Masahiko Sawada" <sawada.mshk@gmail.com>, "Daniil Davydov"
 <3danissimo@gmail.com>, =?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
From: "Matheus Alcantara" <matheusssilv97@gmail.com>
To: "Arseniy Mukhin" <arseniy.mukhin.dev@gmail.com>, "Yura Sokolov"
 <y.sokolov@postgrespro.ru>
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>
 <DC7KGTXW3QSG.OZA24HONT78J@gmail.com>
 <CAD21AoCD+HXoc2QZCAS9d8ahDeikNqbnU0i6cQzpMFOEurkPPg@mail.gmail.com>
 <DC8HKDZVIDYG.12H0X267CHMO5@gmail.com>
 <CAFY6G8cJm73_MM9SuynZUqtqcaTuepUDgDuvS661oLW7U0dgsg@mail.gmail.com>
 <d186fba0-dc65-4274-aa96-3906bbb2e530@postgrespro.ru>
 <CAE7r3M+Xwf0A_aNhu7pYQd7nDQaqaEnyCjtvqg8XsgManmPOUA@mail.gmail.com>
In-Reply-To: 
 <CAE7r3M+Xwf0A_aNhu7pYQd7nDQaqaEnyCjtvqg8XsgManmPOUA@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/DCJH5HX8X3B4.O9P9P8VKKO1F%40gmail.com>
Precedence: bulk

On Wed Sep 3, 2025 at 4:28 PM -03, Arseniy Mukhin wrote:
>> Since we still need to scan those pages, couldn't we mark finished
>> transactions as committed/aborted?
>> This way we may to not hold datfrozenxid for a long time and will allow
>> both safe clog truncation and safe async queue notification.
>> More over, most notifications could be marked as completed in
>> AtCommit_Notify already. So there is a need to recheck a few notificatio=
ns
>> that were written but not marked in AtCommit_Notify.
>>
>> Since AsyncQueueEntry field is used only to check visibility, looks like=
 it
>> is safe to set it to FrozenTransactionId.
>
> I like the idea to make queue records more self-sufficient, so we
> don't need to check a transaction's status to determine if a
> notification should be sent. What if we somehow mark every record of
> an aborted transaction, so listeners can skip it without checking
> transaction status? Also if the record is not marked as "aborted" we
> also can send it without checking transaction status. IIUC
> AtAbort_Notify is called before any listener can read records of the
> aborted transaction (is it correct?), so we can do such "marking" in
> AtAbort_Notify. And the listen/notify subsystem already has a similar
> "marking" mechanism. At the end of every queue page we have a "dummy"
> record which is skipped by every listener. Listeners skip it because
> record's 'dboid' equals InvalidOid (listeners ignore any record where
> the 'dboid' is different from their own). In the same way aborted
> transactions can set 'dboid' to InvalidOid for their records in
> AtAbort_Notify.
>
> Some benefits of this approach:
> 1) Only aborted transactions would need to perform extra work.
> 2) Listeners don't need to check notify transaction status, but ofc
> they still need to wait if the notify transaction is 'in progress'.
> 3) Vacuum don't need to be listen/notify aware.
> 4) We don't need to do an extra scan of the whole queue.
>
> What do you think?
>
IIUC we don't store notifications of aborted transactions on the queue.
On PreCommit_Notify we add the notifications on the queue and on
Commit_Notify() we signal the backends.

Or I'm missing something here?

--
Matheus Alcantara