Re: SV: Problem with pg_notify / listen

Hi,

I have a question. Is this problem in postgres 12.5 ? We have very similar
problem after upgrade from 12.4 to 12.5: queue size grows to 15% in 20
hours and still is raising.

On Sun, Feb 7, 2021 at 2:15 AM Gustavsson Mikael <mikael(dot)gustavsson(at)smhi(dot)se>
wrote:

> Great!
>
>
> I will talk to the rest of the team on which path to take.
>
>
> And thank you for finding and fixing this so quickly!
>
>
> kr
>
> Mikael Gustavsson
>
> SMHI
>
>
> ------------------------------
> *Från:* Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> *Skickat:* den 28 november 2020 20:15
> *Till:* Noah Misch
> *Kopia:* Gustavsson Mikael; pgsql-bugs(at)lists(dot)postgresql(dot)org; Svensson
> Peter; Almen Anders
> *Ämne:* Re: SV: Problem with pg_notify / listen
>
> Noah Misch <noah(at)leadboat(dot)com> writes:
> > On Fri, Nov 27, 2020 at 11:03:40PM -0500, Tom Lane wrote:
> >> Hmm, it's not very clear what "stop" means here. What do you think of
> >> QUEUE_OLDEST_PAGE?
>
> > QUEUE_OLDEST_PAGE is fine. I like it a little less than QUEUE_STOP_PAGE,
> > because oldestClogXid is a logical tail, and QUEUE_OLDEST_PAGE would be a
> > physical tail.
>
> I went with QUEUE_STOP_PAGE.
>
> In further testing, I noted that the patch as I had it re-introduced the
> symptom that 8b7ae5a82 fixed, that running "make installcheck" twice
> in a row causes the async-notify isolation test to fail. That's because
> I'd changed asyncQueueUsage() to measure the distance back to the physical
> tail, which isn't stable because we only truncate after crossing an
> SLRU segment boundary. So I reverted it to measuring the distance to
> QUEUE_TAIL. You could argue either way about which definition is more
> useful to end users, perhaps; but in practice the difference should
> usually be too small to matter, for everyone except regression tests
> that are looking for distance exactly zero.
>
> Anyhow, pushed with that fix.
>
> Mikael, it appears that you have three options: revert to 11.9 until 11.11
> is out, restart your server every time it approaches notify-queue-full, or
> apply this patch:
>
>
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=40f2fbe71ad615a2bcaaf5b840ccb9329e4378aa
>
> regards, tom lane
>

--

Pozdrawiam
Piotr Włodarczyk