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>
 <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>
 <1126737.1760537761@sss.pgh.pa.us>
In-Reply-To: <1126737.1760537761@sss.pgh.pa.us>
From: Arseniy Mukhin <arseniy.mukhin.dev@gmail.com>
Date: Wed, 15 Oct 2025 22:53:38 +0300
Message-ID: 
 <CAE7r3ML3ni8La5pX7tdN9ehFns_t=2_s2HkUP5WUxyk7B7t72g@mail.gmail.com>
Subject: Re: Optimize LISTEN/NOTIFY
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Joel Jacobson <joel@compiler.org>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAE7r3ML3ni8La5pX7tdN9ehFns_t%3D2_s2HkUP5WUxyk7B7t72g%40mail.gmail.com>
Precedence: bulk

On Wed, Oct 15, 2025 at 5:16=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> 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 po=
sition
>          * before handling each individual message, but that seems like t=
oo 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

Advisory queue position field sounds good IMHO. Listeners are still
solely responsible for advancing their positions so they still need to
wake up to do it, but they will only do so if there are relevant
notifications, or if they are too far behind. In any case they will be
able to jump over all irrelevant stuff.


Best regards,
Arseniy Mukhin