Re: LISTEN/NOTIFY bug: VACUUM sets frozenxid past a xid in async queue

On Wed, Sep 3, 2025 at 2:14 PM Matheus Alcantara

<matheusssilv97(at)gmail(dot)com> wrote:

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

My understanding is that something could go wrong in between

PreCommit_Notify and AtCommit_Notify, which could cause the

transaction to abort, and the notification might be in the queue at

this point, even though the transaction aborted, hence the dependency

on the commit log.

> Maybe we could have a boolean "committed" field on AsyncQueueEntry

> and check this field before sending the notification instead of

> call TransactionIdDidCommit()? Is something similar what you are

> proposing?

I like this direction, as it opens up the ability to remove the

global lock held from PreCommit_Notify to the end of the transaction.

In the same vein as this idea, I was experimenting with a patch

inspired by Tom Lane’s idea in [1] where we write the actual

notification data into the WAL, and just write the db, lsn, and xid

into the notify queue in AtCommit_Notify, so the notify queue only

contains information about committed transactions. Unfortunately,

the additional WAL write overhead in this approach seemed to outweigh

the benefits of removing the lock.

Following that idea - I think perhaps we could have two queues in the

notify system, one that stores the notification data, and another

that stores the position of the committed transaction’s notification,

which we would append to in AtCommit_Notify. Then the listener would

go through the position queue, and find / read the notifications from

the notify data queue.

[1] https://www.postgresql.org/message-id/1878165.1752858390%40sss.pgh.pa.us