Content-Type: multipart/mixed;
 boundary=db7b4e3babaab0140da76d4b59be4c591a66cfafc3bdd677751c2aa7017b
Mime-Version: 1.0
Date: Mon, 11 Aug 2025 10:41:08 -0300
Message-Id: <DBZN3PVLZ1UW.25XLKCUXYBVXV@gmail.com>
Cc: "Daniil Davydov" <3danissimo@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: =?utf-8?q?=C3=81lvaro_Herrera?= <alvherre@kurilemu.de>, "Alexandra Wang"
 <alexandra.wang.oss@gmail.com>
References: 
 <CAK98qZ3wZLE-RZJN_Y+TFjiTRPPFPBwNBpBi5K5CU8hUHkzDpw@mail.gmail.com>
 <202508061044.ptcyt7aqsaaa@alvherre.pgsql>
In-Reply-To: <202508061044.ptcyt7aqsaaa@alvherre.pgsql>
Archived-At: 
 <https://www.postgresql.org/message-id/DBZN3PVLZ1UW.25XLKCUXYBVXV%40gmail.com>
Precedence: bulk

--db7b4e3babaab0140da76d4b59be4c591a66cfafc3bdd677751c2aa7017b
Content-Type: multipart/alternative;
 boundary=056f8d6cbcd266cfcff5df2e16d4a85573ca1e9609d077ad6e8d38e700d0

--056f8d6cbcd266cfcff5df2e16d4a85573ca1e9609d077ad6e8d38e700d0
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Type: text/plain; charset=UTF-8

On Wed Aug 6, 2025 at 7:44 AM -03, =C3=81lvaro Herrera wrote:
>> My questions:
>>=20
>> 1. Is it acceptable to drop notifications from the async queue if
>> there are no active listeners? There might still be notifications that
>> haven=E2=80=99t 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?
>
I'm bit worry about this too.

>> 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. =C2=A0This
> 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.
>
The problem with only considering the oldest XID of listeners is that
IIUC we may have notifications without listeners, and in this case we
may still get this error because when the LISTEN is executed we loop
through the AsyncQueueEntry's on asyncQueueProcessPageEntries() and we
call TransactionIdDidCommit() that raise the error before
IsListeningOn(channel) is called.

Another option would be to add a minXid field on AsyncQueueControl and
then update this value on asyncQueueProcessPageEntries() and
asyncQueueAddEntries() routines, and then we could check this value on
vac_update_datfrozenxid().

> This does create the problem that an inactive listener could cause the
> XID counter to stay far in the past. =C2=A0Maybe we could try to avoid th=
is
> by adding more signalling (e.g, AsyncQueueAdvanceTail() itself could
> send PROCSIG_NOTIFY_INTERRUPT signal?), and terminating backends that
> are way overdue on reading notifies. =C2=A0I'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.
>
With this idea that I've proposed we still could have this problem, if a
listener take too long to consume a message we would block vacuum freeze
to advance the xid. For this I think that we could have two GUC's; One
to enable and disable the oldest xmin check on async queue and the
second to control how far we want to prevent the vacuum from freezing
the oldest async queue xid, and if the min xid raises this limit we
ignore and truncate the xid.

I've write a draft patch that plays with the idea, see attached.

--
Matheus Alcantara

--056f8d6cbcd266cfcff5df2e16d4a85573ca1e9609d077ad6e8d38e700d0--

--db7b4e3babaab0140da76d4b59be4c591a66cfafc3bdd677751c2aa7017b
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename=v0-0001-Consider-async-queue-min-xid-on-VACUUM-FREEZE.patch
Content-Type: text/plain; charset=utf-8;
 name=v0-0001-Consider-async-queue-min-xid-on-VACUUM-FREEZE.patch

RnJvbSA3ZWMzM2RmYzE0MTc0ZTlhNDQyNGExNzM4OTc3NjAxYTRmZDEzYmY1IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBNYXRoZXVzIEFsY2FudGFyYSA8bXRocy5kZXZAcG0ubWU+CkRh
dGU6IFNhdCwgOSBBdWcgMjAyNSAxMzo1MToyMSAtMDMwMApTdWJqZWN0OiBbUEFUQ0ggdjBdIENv
bnNpZGVyIGFzeW5jIHF1ZXVlIG1pbiB4aWQgb24gVkFDVVVNIEZSRUVaRQoKLS0tCiBzcmMvYmFj
a2VuZC9jb21tYW5kcy9hc3luYy5jICB8IDU4ICsrKysrKysrKysrKysrKysrKysrKysrKysrKysr
KysrKysrCiBzcmMvYmFja2VuZC9jb21tYW5kcy92YWN1dW0uYyB8IDExICsrKysrKysKIHNyYy9p
bmNsdWRlL2NvbW1hbmRzL2FzeW5jLmggIHwgIDMgKysKIDMgZmlsZXMgY2hhbmdlZCwgNzIgaW5z
ZXJ0aW9ucygrKQoKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2NvbW1hbmRzL2FzeW5jLmMgYi9z
cmMvYmFja2VuZC9jb21tYW5kcy9hc3luYy5jCmluZGV4IDRiZDM3ZDViZWI1Li5mZmYwNTU2MDFh
MSAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvY29tbWFuZHMvYXN5bmMuYworKysgYi9zcmMvYmFj
a2VuZC9jb21tYW5kcy9hc3luYy5jCkBAIC0yODcsNiArMjg3LDcgQEAgdHlwZWRlZiBzdHJ1Y3Qg
QXN5bmNRdWV1ZUNvbnRyb2wKIAkJCQkJCQkJICogdGFpbC5wYWdlICovCiAJUHJvY051bWJlcglm
aXJzdExpc3RlbmVyOwkvKiBpZCBvZiBmaXJzdCBsaXN0ZW5lciwgb3IKIAkJCQkJCQkJICogSU5W
QUxJRF9QUk9DX05VTUJFUiAqLworCVRyYW5zYWN0aW9uSWQgbWluWGlkOwogCVRpbWVzdGFtcFR6
IGxhc3RRdWV1ZUZpbGxXYXJuOwkvKiB0aW1lIG9mIGxhc3QgcXVldWUtZnVsbCBtc2cgKi8KIAlR
dWV1ZUJhY2tlbmRTdGF0dXMgYmFja2VuZFtGTEVYSUJMRV9BUlJBWV9NRU1CRVJdOwogfSBBc3lu
Y1F1ZXVlQ29udHJvbDsKQEAgLTQ1OCw2ICs0NTksOSBAQCBzdGF0aWMgdWludDMyIG5vdGlmaWNh
dGlvbl9oYXNoKGNvbnN0IHZvaWQgKmtleSwgU2l6ZSBrZXlzaXplKTsKIHN0YXRpYyBpbnQJbm90
aWZpY2F0aW9uX21hdGNoKGNvbnN0IHZvaWQgKmtleTEsIGNvbnN0IHZvaWQgKmtleTIsIFNpemUg
a2V5c2l6ZSk7CiBzdGF0aWMgdm9pZCBDbGVhclBlbmRpbmdBY3Rpb25zQW5kTm90aWZpZXModm9p
ZCk7CiAKK3N0YXRpYyB2b2lkIFN0b3JlRW50cnlYaWQoVHJhbnNhY3Rpb25JZCB4aWQpOworc3Rh
dGljIHZvaWQgUmVsZWFzZUVudHJ5WGlkKFRyYW5zYWN0aW9uSWQgeGlkKTsKKwogLyoKICAqIENv
bXB1dGUgdGhlIGRpZmZlcmVuY2UgYmV0d2VlbiB0d28gcXVldWUgcGFnZSBudW1iZXJzLgogICog
UHJldmlvdXNseSB0aGlzIGZ1bmN0aW9uIGFjY291bnRlZCBmb3IgYSB3cmFwYXJvdW5kLgpAQCAt
NTIxLDYgKzUyNSw3IEBAIEFzeW5jU2htZW1Jbml0KHZvaWQpCiAJCVFVRVVFX1NUT1BfUEFHRSA9
IDA7CiAJCVFVRVVFX0ZJUlNUX0xJU1RFTkVSID0gSU5WQUxJRF9QUk9DX05VTUJFUjsKIAkJYXN5
bmNRdWV1ZUNvbnRyb2wtPmxhc3RRdWV1ZUZpbGxXYXJuID0gMDsKKwkJYXN5bmNRdWV1ZUNvbnRy
b2wtPm1pblhpZCA9IE1heFRyYW5zYWN0aW9uSWQ7CiAJCWZvciAoaW50IGkgPSAwOyBpIDwgTWF4
QmFja2VuZHM7IGkrKykKIAkJewogCQkJUVVFVUVfQkFDS0VORF9QSUQoaSkgPSBJbnZhbGlkUGlk
OwpAQCAtMTQyOCw2ICsxNDMzLDEyIEBAIGFzeW5jUXVldWVBZGRFbnRyaWVzKExpc3RDZWxsICpu
ZXh0Tm90aWZ5KQogCQkJICAgJnFlLAogCQkJICAgcWUubGVuZ3RoKTsKIAorCQkvKgorCQkgKiBS
ZW1lbWJlciB0aGUgbm90aWZpY2F0aW9uIHhpZCBzbyB0aGF0IHZhY3V1bSBkb24ndCBmcm96ZW4g
YWZ0ZXIKKwkJICogdGhpcyB4aWQuCisJCSAqLworCQlTdG9yZUVudHJ5WGlkKHFlLnhpZCk7CisK
IAkJLyogQWR2YW5jZSBxdWV1ZV9oZWFkIGFwcHJvcHJpYXRlbHksIGFuZCBkZXRlY3QgaWYgcGFn
ZSBpcyBmdWxsICovCiAJCWlmIChhc3luY1F1ZXVlQWR2YW5jZSgmKHF1ZXVlX2hlYWQpLCBxZS5s
ZW5ndGgpKQogCQl7CkBAIC0yMDc3LDYgKzIwODgsMTIgQEAgYXN5bmNRdWV1ZVByb2Nlc3NQYWdl
RW50cmllcyh2b2xhdGlsZSBRdWV1ZVBvc2l0aW9uICpjdXJyZW50LAogCQkJCQljaGFyCSAgICpw
YXlsb2FkID0gcWUtPmRhdGEgKyBzdHJsZW4oY2hhbm5lbCkgKyAxOwogCiAJCQkJCU5vdGlmeU15
RnJvbnRFbmQoY2hhbm5lbCwgcGF5bG9hZCwgcWUtPnNyY1BpZCk7CisKKwkJCQkJLyoKKwkJCQkJ
ICogTm90aWZpY2F0aW9uIHdhcyBzZW50LCBzbyByZWxlYXNlIHRoZSBub3RpZmljYXRpb24geGlk
CisJCQkJCSAqIHNvIHRoYXQgdmFjdXVtIGNhbiBmcmVlemUgcGFzdCB0aGlzIG5vdGlmaWNhdGlv
bi4KKwkJCQkJICovCisJCQkJCVJlbGVhc2VFbnRyeVhpZChxZS0+eGlkKTsKIAkJCQl9CiAJCQl9
CiAJCQllbHNlCkBAIC0yMzk1LDMgKzI0MTIsNDQgQEAgY2hlY2tfbm90aWZ5X2J1ZmZlcnMoaW50
ICpuZXd2YWwsIHZvaWQgKipleHRyYSwgR3VjU291cmNlIHNvdXJjZSkKIHsKIAlyZXR1cm4gY2hl
Y2tfc2xydV9idWZmZXJzKCJub3RpZnlfYnVmZmVycyIsIG5ld3ZhbCk7CiB9CisKKy8qCisgKiAg
UmV0dXJuIHRoZSBtaW5pbXVtIG5vdGlmaWNhdGlvbiB4aWQgb24gdGhlIHF1ZXVlLgorICoKKyAq
ICBUT0RPKG1hdGhldXMpOiBoYW5kbGUgd2hlbiB0aGUgcXVldWUgaXMgZW1wdHksIHNvIHdlIGRv
bid0IGdldCBsb2NrZWQgaW4KKyAqICB0aGUgcGFzdCB3aXRoIGEgbm90aWZpY2F0aW9uIHRoYXQg
d2FzIGFscmVhZHkgc2VudC4KKyAqLworVHJhbnNhY3Rpb25JZAorQXN5bmNRdWV1ZU1pblhpZCgp
Cit7CisJcmV0dXJuIGFzeW5jUXVldWVDb250cm9sLT5taW5YaWQ7Cit9CisKKy8qCisgKiAgU3Vi
cm91dGluZSBvZiBhc3luY1F1ZXVlQWRkRW50cmllcworICoKKyAqICBXZSBhcmUgaG9sZGluZyBO
b3RpZnlRdWV1ZUxvY2sgYWxyZWFkeSBmcm9tIHRoZSBjYWxsZXIuCisgKi8KK3N0YXRpYyB2b2lk
CitTdG9yZUVudHJ5WGlkKFRyYW5zYWN0aW9uSWQgeGlkKQoreworCWlmICh4aWQgPCBhc3luY1F1
ZXVlQ29udHJvbC0+bWluWGlkKQorCQlhc3luY1F1ZXVlQ29udHJvbC0+bWluWGlkID0geGlkOwor
fQorCisvKgorICogU3Vicm91dGluZSBvZiBhc3luY1F1ZXVlQWRkRW50cmllcworICoKKyAqIFRP
RE8obWF0aGV1cyk6IG1heSB1cGRhdGUgdGhlIGNvZGUgY29tbWVudCBvbgorICogYXN5bmNRZXVl
dVJlYWRBbGxOb3RpZmljYXRpb25zKCkgd2hlbiBjYWxsaW5nCisgKiBhc3luY1F1ZXVlUHJvY2Vz
c1BhZ2VFbnRyaWVzKCkuCisgKgorICovCitzdGF0aWMgdm9pZAorUmVsZWFzZUVudHJ5WGlkKFRy
YW5zYWN0aW9uSWQgeGlkKQoreworCUxXTG9ja0FjcXVpcmUoTm90aWZ5UXVldWVMb2NrLCBMV19F
WENMVVNJVkUpOworCWlmICh4aWQgPiBhc3luY1F1ZXVlQ29udHJvbC0+bWluWGlkKQorCQlhc3lu
Y1F1ZXVlQ29udHJvbC0+bWluWGlkID0geGlkOworCUxXTG9ja1JlbGVhc2UoTm90aWZ5UXVldWVM
b2NrKTsKK30KZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2NvbW1hbmRzL3ZhY3V1bS5jIGIvc3Jj
L2JhY2tlbmQvY29tbWFuZHMvdmFjdXVtLmMKaW5kZXggNzMzZWY0MGFlN2MuLmYwZDZkODY4ZTYw
IDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9jb21tYW5kcy92YWN1dW0uYworKysgYi9zcmMvYmFj
a2VuZC9jb21tYW5kcy92YWN1dW0uYwpAQCAtMjEsNiArMjEsNyBAQAogICoKICAqLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLQogICovCisjaW5jbHVkZSAiY29tbWFuZHMvYXN5bmMuaCIKICNpbmNsdWRlICJwb3N0
Z3Jlcy5oIgogCiAjaW5jbHVkZSA8bWF0aC5oPgpAQCAtMTczOSw2ICsxNzQwLDE2IEBAIHZhY191
cGRhdGVfZGF0ZnJvemVueGlkKHZvaWQpCiAJaWYgKGJvZ3VzKQogCQlyZXR1cm47CiAKKwkvKgor
CSAqIFdlIG5lZWQgdG8gY2hlY2sgdHJhbnNhY3Rpb24gc3RhdHVzIG9mIG5vdGlmaWNhdGlvbnMg
YmVmb3JlIG9mIG5vdGlmeQorCSAqIHRoZSBjbGllbnQsIGlmIHRoZXJlIGlzIGxhZyB0byBjb25z
dW1lIHRoZSBub3RpZmljYXRpb25zIHdlIG5lZWQgdG8KKwkgKiBjb25zaWRlciB0aGUgb2xkZXIg
eGlkIG9mIG5vdGlmaWNhdGlvbiBvbiB0aGUgcXVldWUgc28gdGhhdCB0aGUKKwkgKiB0cmFuc2Fj
dGlvbiBzdGF0dXMgY2FuIGJlIGFjY2Vzc2VkLgorCSAqCisJICogWFhYKG1hdGhldXMpOiBXcmFw
IHRoaXMgYmVoYXZpb3IgaW50byBhIEdVQz8KKwkgKi8KKwluZXdGcm96ZW5YaWQgPSBNaW4obmV3
RnJvemVuWGlkLCBBc3luY1F1ZXVlTWluWGlkKCkpOworCiAJQXNzZXJ0KFRyYW5zYWN0aW9uSWRJ
c05vcm1hbChuZXdGcm96ZW5YaWQpKTsKIAlBc3NlcnQoTXVsdGlYYWN0SWRJc1ZhbGlkKG5ld01p
bk11bHRpKSk7CiAKZGlmZiAtLWdpdCBhL3NyYy9pbmNsdWRlL2NvbW1hbmRzL2FzeW5jLmggYi9z
cmMvaW5jbHVkZS9jb21tYW5kcy9hc3luYy5oCmluZGV4IGY3NWMzZGY5NTU2Li41MTJhODk3NjEy
NiAxMDA2NDQKLS0tIGEvc3JjL2luY2x1ZGUvY29tbWFuZHMvYXN5bmMuaAorKysgYi9zcmMvaW5j
bHVkZS9jb21tYW5kcy9hc3luYy5oCkBAIC0xMyw2ICsxMyw3IEBACiAjaWZuZGVmIEFTWU5DX0gK
ICNkZWZpbmUgQVNZTkNfSAogCisjaW5jbHVkZSA8Yy5oPgogI2luY2x1ZGUgPHNpZ25hbC5oPgog
CiBleHRlcm4gUEdETExJTVBPUlQgYm9vbCBUcmFjZV9ub3RpZnk7CkBAIC00Niw0ICs0Nyw2IEBA
IGV4dGVybiB2b2lkIEhhbmRsZU5vdGlmeUludGVycnVwdCh2b2lkKTsKIC8qIHByb2Nlc3MgaW50
ZXJydXB0cyAqLwogZXh0ZXJuIHZvaWQgUHJvY2Vzc05vdGlmeUludGVycnVwdChib29sIGZsdXNo
KTsKIAorZXh0ZXJuIFRyYW5zYWN0aW9uSWQgQXN5bmNRdWV1ZU1pblhpZCh2b2lkKTsKKwogI2Vu
ZGlmCQkJCQkJCS8qIEFTWU5DX0ggKi8KLS0gCjIuMzkuNSAoQXBwbGUgR2l0LTE1NCkKCg==
--db7b4e3babaab0140da76d4b59be4c591a66cfafc3bdd677751c2aa7017b--