MIME-Version: 1.0
References: <6899c044-4a82-49be-8117-e6f669765f7e@app.fastmail.com>
 <165530.1752362320@sss.pgh.pa.us>
 <02a7cd37-e2fc-4212-8b19-f8c239c95fb8@app.fastmail.com>
 <e396ecf3-4227-4918-b9ff-e9568dcebcf0@app.fastmail.com>
 <b6a427fe-2ed6-4568-85d9-207a68172617@app.fastmail.com>
 <CAK80=jhmE40KVqQ3ho37MArS7cAED1p9m7uikDxcnDmqdW7t8A@mail.gmail.com>
 <96f00bf1-cc9d-4520-9d02-9e14e7767c88@app.fastmail.com>
 <30c2aa7d-dd6c-4b68-a2e4-f217a1a34acf@app.fastmail.com>
 <CA+hUKGLrMGkWDB0cwTa0RqD+AF7O-Ywgck8aVYKwOQnZgYRRug@mail.gmail.com>
 <0b4d402a-9ac2-4aa8-acf8-8231dbe579ea@app.fastmail.com>
 <c8e84bb2-d2e5-4a77-b547-3a93c5778e7e@app.fastmail.com>
 <3095599.1758644879@sss.pgh.pa.us>
 <0dc6a2cc-5216-4dc1-9dd2-430cafc6095b@app.fastmail.com>
 <E7D8A5CD-C445-4713-B6B7-6CC1D3A71161@gmail.com>
 <baf71275-9083-41be-ae13-f80821541a1a@app.fastmail.com>
 <52CC167F-763B-4ECA-B0B4-DAB381816828@gmail.com>
 <d35a114d-204a-4b40-9284-06749532b724@app.fastmail.com>
 <9186C6D0-F7A9-482A-9183-89E530B57E36@gmail.com>
 <da0996!!!!!d8-ecf9-490d-bbd0-2a5bdb5b115a@app.fastmail.com>
 <D1CF7CA5-213A-4FC1-86DB-052D58904A0A@gmail.com>
 <cad78ff4-aae9-4c1c-8e16-ff0ccd4957f1@app.fastmail.com>
 <1073593.1759423179@sss.pgh.pa.us>
 <4bd5e6c4-6fa7-44bb-869d-59a32a331fa8@app.fastmail.com>
 <CAFY6G8dap-bCnAnMG-2Gzew8yv2Vbi9gsx9+yszKMmd57ygfvA@mail.gmail.com>
 <85828f29-e72e-4400-94f3-9a69bc8dc239@app.fastmail.com>
 <2495353.1759860890@sss.pgh.pa.us>
 <8aeae418-92a6-4bbd-9c06-9574c79e59f7@app.fastmail.com>
 <2531672.1759868124@sss.pgh.pa.us>
 <474efa78-337c-41cd-a73a-f845a0115109@app.fastmail.com>
 <2749343.1759949176@sss.pgh.pa.us>
 <8bfca2be-1ec0-4e15-aafb-0b7b661fe936@app.fastmail.com>
 <9eba307f-f2fb-48f0-9507-2e197f39ef9e@app.fastmail.com>
 <cde2a91d-d47a-43b5-ab27-f98aa739d38e@app.fastmail.com>
 <8c71183a-0d28-4bcf-a806-78446ff95404@app.fastmail.com>
 <1009807.1760476747@sss.pgh.pa.us>
 <2a30bcf1-aee2-4b07-a302-11e4b350adaf@app.fastmail.com>
In-Reply-To: <2a30bcf1-aee2-4b07-a302-11e4b350adaf@app.fastmail.com>
From: Arseniy Mukhin <arseniy.mukhin.dev@gmail.com>
Date: Wed, 15 Oct 2025 14:19:40 +0300
Message-ID: 
 <CAE7r3M+E94vsA_D2iV3R=hq_Gq5k+4wYPzS11u1kDrUaLr1-iQ@mail.gmail.com>
Subject: Re: Optimize LISTEN/NOTIFY
To: Joel Jacobson <joel@compiler.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000e8e3e0064130addf"
Archived-At: 
 <https://www.postgresql.org/message-id/CAE7r3M%2BE94vsA_D2iV3R%3Dhq_Gq5k%2B4wYPzS11u1kDrUaLr1-iQ%40mail.gmail.com>
Precedence: bulk

--000000000000e8e3e0064130addf
Content-Type: text/plain; charset="UTF-8"

Hi,

Thank you for working on it! Benchmarking looks great. There are several points:

I tried the patch and it seems listeners sometimes don't receive
notifications. To reproduce it you can try to listen to the channel in
one psql session and send notifications from another psql session. But
all tests are fine, so I tried to write a TAP test to reproduce it. It
passes on master and fails with the patch, so looks like it's real.
Please find the repro in attachments. I added the TAP test to amcheck
module just for simplicity.

I think "Direct advancement" is a good idea. But the way it's
implemented now has a concurrency bug. Listeners store its current
position in the local variable 'pos' during the reading in
asyncQueueReadAllNotifications() and don't hold NotifyQueueLock. It
means that some notifier can directly advance the listener's position
while the listener has an old value in the local variable. The same
time we use listener positions to find out the limit we can truncate
the queue in asyncQueueAdvanceTail(). asyncQueueAdvanceTail() doesn't
know that listeners have a local copy of their positions and can
truncate the queue beyond that which means listeners can try to read
notifications from the truncated segment. I managed to reproduce it
locally. Please let me know if more details are needed.

BTW error message a bit confusing:

2025-10-15 13:32:15.570 MSK [261845] ERROR:  could not access status
of transaction 0
2025-10-15 13:32:15.570 MSK [261845] DETAIL:  Could not open file
"pg_notify/000000000000001": No such file or directory.

Looks like all slru IO errors have an error message about transaction
status. It's not a problem really as we have a directory path in the
log.


Best regards,
Arseniy Mukhin

--000000000000e8e3e0064130addf
Content-Type: application/octet-stream;
	name="listen-notify-test.patch.nocfbot"
Content-Disposition: attachment; filename="listen-notify-test.patch.nocfbot"
Content-Transfer-Encoding: base64
Content-ID: <f_mgrw8rbx0>
X-Attachment-Id: f_mgrw8rbx0

RnJvbSBjMmZjNTBiNjNjYjY1MTdlNTFhMTIwM2JiY2U4N2YyOWVhMDljYTg0IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBBcnNlbml5IE11a2hpbiA8YXJzZW5peS5tdWtoaW4uZGV2QGdt
YWlsLmNvbT4KRGF0ZTogV2VkLCAxNSBPY3QgMjAyNSAxMTo0MzoyNiArMDMwMApTdWJqZWN0OiBb
UEFUQ0hdIGxpc3RlbiBub3RpZnkgdGVzdAoKLS0tCiBjb250cmliL2FtY2hlY2svbWVzb24uYnVp
bGQgICAgICAgICAgICB8ICAxICsKIGNvbnRyaWIvYW1jaGVjay90LzAwN19saXN0ZW4tbm90aWZ5
LnBsIHwgMzMgKysrKysrKysrKysrKysrKysrKysrKysrKysKIDIgZmlsZXMgY2hhbmdlZCwgMzQg
aW5zZXJ0aW9ucygrKQogY3JlYXRlIG1vZGUgMTAwNjQ0IGNvbnRyaWIvYW1jaGVjay90LzAwN19s
aXN0ZW4tbm90aWZ5LnBsCgpkaWZmIC0tZ2l0IGEvY29udHJpYi9hbWNoZWNrL21lc29uLmJ1aWxk
IGIvY29udHJpYi9hbWNoZWNrL21lc29uLmJ1aWxkCmluZGV4IDFmMGMzNDdlZDU0Li5hZDViNGIw
MWY3OSAxMDA2NDQKLS0tIGEvY29udHJpYi9hbWNoZWNrL21lc29uLmJ1aWxkCisrKyBiL2NvbnRy
aWIvYW1jaGVjay9tZXNvbi5idWlsZApAQCAtNTAsNiArNTAsNyBAQCB0ZXN0cyArPSB7CiAgICAg
ICAndC8wMDRfdmVyaWZ5X25idHJlZV91bmlxdWUucGwnLAogICAgICAgJ3QvMDA1X3BpdHIucGwn
LAogICAgICAgJ3QvMDA2X3ZlcmlmeV9naW4ucGwnLAorICAgICAgJ3QvMDA3X2xpc3Rlbi1ub3Rp
ZnkucGwnLAogICAgIF0sCiAgIH0sCiB9CmRpZmYgLS1naXQgYS9jb250cmliL2FtY2hlY2svdC8w
MDdfbGlzdGVuLW5vdGlmeS5wbCBiL2NvbnRyaWIvYW1jaGVjay90LzAwN19saXN0ZW4tbm90aWZ5
LnBsCm5ldyBmaWxlIG1vZGUgMTAwNjQ0CmluZGV4IDAwMDAwMDAwMDAwLi5kYzlkMGNhMDcxZAot
LS0gL2Rldi9udWxsCisrKyBiL2NvbnRyaWIvYW1jaGVjay90LzAwN19saXN0ZW4tbm90aWZ5LnBs
CkBAIC0wLDAgKzEsMzMgQEAKKworIyBDb3B5cmlnaHQgKGMpIDIwMjEtMjAyNSwgUG9zdGdyZVNR
TCBHbG9iYWwgRGV2ZWxvcG1lbnQgR3JvdXAKKwordXNlIHN0cmljdDsKK3VzZSB3YXJuaW5ncyBG
QVRBTCA9PiAnYWxsJzsKKwordXNlIFBvc3RncmVTUUw6OlRlc3Q6OkNsdXN0ZXI7Cit1c2UgUG9z
dGdyZVNRTDo6VGVzdDo6VXRpbHM7CisKK3VzZSBUZXN0OjpNb3JlOworCitteSAkbm9kZTsKKwor
IworIyBUZXN0IHNldC11cAorIworJG5vZGUgPSBQb3N0Z3JlU1FMOjpUZXN0OjpDbHVzdGVyLT5u
ZXcoJ3Rlc3QnKTsKKyRub2RlLT5pbml0KCk7Ciskbm9kZS0+c3RhcnQoKTsKKworbXkgJGxpc3Rl
bmVyID0gJG5vZGUtPmJhY2tncm91bmRfcHNxbCgncG9zdGdyZXMnKTsKKworJGxpc3RlbmVyLT5x
dWVyeV9zYWZlKCJMSVNURU4gY2giKTsKKworJG5vZGUtPnNhZmVfcHNxbCgicG9zdGdyZXMiLCJO
T1RJRlkgY2gsJ2FhJyIpOworbXkgJHJlcyA9ICRsaXN0ZW5lci0+cXVlcnlfc2FmZSgic2VsZWN0
ICdBQUFBQUFBQUEnOyIpOworCitsaWtlKCRyZXMsIHFyL0FzeW5jaHJvbm91cyBub3RpZmljYXRp
b24gImNoIiB3aXRoIHBheWxvYWQgImFhIiByZWNlaXZlZCBmcm9tIHNlcnZlciBwcm9jZXNzLyk7
CisKKyRsaXN0ZW5lci0+cXVpdCgpOworCisKK2RvbmVfdGVzdGluZygpOwotLSAKMi40My4wCgo=
--000000000000e8e3e0064130addf--