Feedback-ID: ic6394509:Fastmail
MIME-Version: 1.0
Date: Sun, 28 Sep 2025 12:24:06 +0200
From: "Joel Jacobson" <joel@compiler.org>
To: "Chao Li" <li.evan.chao@gmail.com>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>, "Thomas Munro" <thomas.munro@gmail.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>,
 "Heikki Linnakangas" <hlinnaka@iki.fi>,
 "Rishu Bagga" <rishu.postgres@gmail.com>
Message-Id: <da0996d8-ecf9-490d-bbd0-2a5bdb5b115a@app.fastmail.com>
In-Reply-To: <9186C6D0-F7A9-482A-9183-89E530B57E36@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>
Subject: Re: Optimize LISTEN/NOTIFY
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/da0996d8-ecf9-490d-bbd0-2a5bdb5b115a%40app.fastmail.com>
Precedence: bulk

On Fri, Sep 26, 2025, at 11:44, Chao Li wrote:
>> On Sep 26, 2025, at 17:32, Joel Jacobson <joel@compiler.org> wrote:
>>=20
>> On Fri, Sep 26, 2025, at 04:26, Chao Li wrote:
>>=20
>>> I think what you explained is partially correct.
>>>=20
>>> Based on my understanding, any backend process may call=20
>>> SignalBackends(), which means that it=E2=80=99s possible that multip=
le backend=20
>>> processes may call SignalBackends() concurrently.
>>>=20
>>> Looking at your code, between checking=20
>>> QUEUE_BACKEND_WAKEUP_PENDING_FLAG(i) and set the flag to true, there=
 is=20
>>> a block of code (the =E2=80=9Cif-else=E2=80=9D) to run, so that it=E2=
=80=99s possible that=20
>>> multiple backend processes have passed the=20
>>> QUEUE_BACKEND_WAKEUP_PENDING_FLAG(i) check, then multiple signals wi=
ll=20
>>> be sent to a process, which will lead to duplicate timeout enabled i=
n=20
>>> the receiver process.
>>=20
>> I don't see how that can happen; we're checking wakeup_pending_flag
>> while holding an exclusive lock, so I don't see how multiple backend
>> processes could be within the region where we check/set
>> wakeup_pending_flag, at the same time?
>>=20
>> /Joel
>
> I might miss the factor of holding an exclusive lock. I will revisit=20
> that part again.

I've re-read this entire thread, and I actually think my original
approaches are more promising, that is, the
0001-optimize_listen_notify-v4.patch patch, doing multicast targeted
signaling.

Therefore, merely consider the latest patch as PoC with some possible
interesting ideas.

Before this patch, I had never used PostgreSQL's timeout mechanism
before, so I didn't consider it when thinking about how to solve the
remaining problems with 0001-optimize_listen_notify-v4.patch, which
currently can't guarantee that all listening backends will eventually
catch up, since it just kicks one of the most lagging ones, for each
notification. This could be a problem in practise if there is a long
period of time with no notifications coming in. Then some listening
backends could end up not being signaled and would stay behind,
preventing the queue tail from advancing.

I'm thinking maybe somehow we can use the timeout mechanism here, but
I'm not sure how yet. Any ideas?

/Joel