From: Tom Lane <tgl@sss.pgh.pa.us>
To: Arseniy Mukhin <arseniy.mukhin.dev@gmail.com>
cc: Joel Jacobson <joel@compiler.org>,
        pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: Optimize LISTEN/NOTIFY
In-reply-to: 
 <CAE7r3M+E94vsA_D2iV3R=hq_Gq5k+4wYPzS11u1kDrUaLr1-iQ@mail.gmail.com>
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>
 <CAE7r3M+E94vsA_D2iV3R=hq_Gq5k+4wYPzS11u1kDrUaLr1-iQ@mail.gmail.com>
Comments: In-reply-to Arseniy Mukhin <arseniy.mukhin.dev@gmail.com>
	message dated "Wed, 15 Oct 2025 14:19:40 +0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1126736.1760537761.1@sss.pgh.pa.us>
Date: Wed, 15 Oct 2025 10:16:01 -0400
Message-ID: <1126737.1760537761@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1126737.1760537761%40sss.pgh.pa.us>
Precedence: bulk

Arseniy Mukhin <arseniy.mukhin.dev@gmail.com> writes:
> 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().

Good catch!

I think we can perhaps salvage the idea if we invent a separate
"advisory" queue position field, which tells its backend "hey,
you could skip as far as here if you want", but is not used for
purposes of SLRU truncation.  Alternatively, split the queue pos
into "this is where to read next" and "this is as much as I'm
definitively done with", where the second field gets advanced at
the end of asyncQueueReadAllNotifications.  Not sure which
view would be less confusing (in the end I guess they're nearly
the same thing, differently explained).

A different line of thought could be to get rid of
asyncQueueReadAllNotifications's optimization of moving the
queue pos only once, per

	 * (We could alternatively retake NotifyQueueLock and move the position
	 * before handling each individual message, but that seems like too much
	 * lock traffic.)

Since we only need shared lock to advance our own queue pos,
maybe that wouldn't be too awful.  Not sure.

			regards, tom lane