SV: Problem with pg_notify / listen

From: Gustavsson Mikael <mikael(dot)gustavsson(at)smhi(dot)se>
To: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
Cc: pgsql-generallists(dot)postgresql(dot)org <pgsql-general(at)lists(dot)postgresql(dot)org>, Svensson Peter <peter(dot)svensson(at)smhi(dot)se>, Almen Anders <anders(dot)almen(at)smhi(dot)se>
Subject: SV: Problem with pg_notify / listen
Date: 2020-11-27 14:48:28
Message-ID: 19d46e8276a54d24b09b6bcbd3bc8e17@smhi.se
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-general

Hi David,

Thanks for fast reply!

We had to restart the server to avoid problems with a full queue så process is no longer with us.

The queue is at 25% now and we have minimised the payload to avoid problems during the weekend.
# select * from pg_notification_queue_usage()
;
pg_notification_queue_usage
-----------------------------
0.245367050170898
(1 row)

If my calculations is correct we will reach 50% on monday.

kr
/Mikael Gustavsson

________________________________
Från: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
Skickat: den 27 november 2020 15:28
Till: Gustavsson Mikael
Kopia: pgsql-generallists.postgresql.org; Svensson Peter; Almen Anders
Ämne: Re: Problem with pg_notify / listen

On Friday, November 27, 2020, Gustavsson Mikael <mikael(dot)gustavsson(at)smhi(dot)se<mailto:mikael(dot)gustavsson(at)smhi(dot)se>> wrote:

Hi.

After applying the latest patch we have encountered a problem with the pg_notify queue.

The queue is filling up and starts issuing warnings like
WARNING: NOTIFY queue is 87% full
DETAIL: The server process with PID 2969993 is among those with the oldest transactions.
NOTIFY queue cannot be emptied until that process ends its current transaction.

There is no long-running 'active' or 'idle in transaction' transactions on the server.

As its easy enough to run can you please confirm this by showing us the pg_stat_activity record for that pid during the time when this warning appears?

Thanks!

David J.

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2020-11-27 15:21:33 Re: Problem with pg_notify / listen
Previous Message David G. Johnston 2020-11-27 14:28:10 Re: Problem with pg_notify / listen

Browse pgsql-general by date

  From Date Subject
Next Message Hemil Ruparel 2020-11-27 15:01:50 Re: How to debug authentication issues in Postgres
Previous Message Alexander Farber 2020-11-27 14:37:44 Deleting takes days, should I add some index?