Обсуждение: Problem with pg_notify / listen

Поиск
Список
Период
Сортировка

Problem with pg_notify / listen

От
Gustavsson Mikael
Дата:

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.
 
We restarted the application with the said pid but the result was that the new process pid appeared in the logs instead.
We tried to stop everything except postgresql it self but the queue was not purged. After restarting postgresql the queue is empty and seems to be holding up for an hour or so before it starts growing again.

We have been using pg_notify / listen for about three years and haven’t run in to this problem before so it seems to me that something happened in the latest patch.

PG version: 11.10 with slony. The notify triggers is on the master node.

KR. 
Mikael Gustavsson 
SMHI
Swedish Meteorological and Hydrological Institute


Re: Problem with pg_notify / listen

От
"David G. Johnston"
Дата:
On Friday, November 27, 2020, Gustavsson Mikael <mikael.gustavsson@smhi.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.

SV: Problem with pg_notify / listen

От
Gustavsson Mikael
Дата:

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.g.johnston@gmail.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.gustavsson@smhi.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.

Re: Problem with pg_notify / listen

От
Tom Lane
Дата:
Gustavsson Mikael <mikael.gustavsson@smhi.se> writes:
> After applying the latest patch we have encountered a problem with the pg_notify queue.

What do you mean by "the latest patch", exactly?

            regards, tom lane

SV: Problem with pg_notify / listen

От
Gustavsson Mikael
Дата:

Hi.


We installed PG 11.10 last week. So the latest release of PG 11.


KR

Mikael Gustavsson

Från: Tom Lane <tgl@sss.pgh.pa.us>
Skickat: den 27 november 2020 16:21:33
Till: Gustavsson Mikael
Kopia: pgsql-generallists.postgresql.org; Svensson Peter; Almen Anders
Ämne: Re: Problem with pg_notify / listen
 
Gustavsson Mikael <mikael.gustavsson@smhi.se> writes:
> After applying the latest patch we have encountered a problem with the pg_notify queue.

What do you mean by "the latest patch", exactly?

                        regards, tom lane

SV: Problem with pg_notify / listen

От
Gustavsson Mikael
Дата:

Clarification, we upgraded from PG 11.9 to PG 11.10.


KR Mikael Gustavsson

Från: externaly-forwarded@smhi.se <externaly-forwarded@smhi.se> för Gustavsson Mikael <mikael.gustavsson@smhi.se>
Skickat: den 27 november 2020 16:24:38
Till: Tom Lane
Kopia: pgsql-generallists.postgresql.org; Svensson Peter; Almen Anders
Ämne: SV: Problem with pg_notify / listen
 

Hi.


We installed PG 11.10 last week. So the latest release of PG 11.


KR

Mikael Gustavsson

Från: Tom Lane <tgl@sss.pgh.pa.us>
Skickat: den 27 november 2020 16:21:33
Till: Gustavsson Mikael
Kopia: pgsql-generallists.postgresql.org; Svensson Peter; Almen Anders
Ämne: Re: Problem with pg_notify / listen
 
Gustavsson Mikael <mikael.gustavsson@smhi.se> writes:
> After applying the latest patch we have encountered a problem with the pg_notify queue.

What do you mean by "the latest patch", exactly?

                        regards, tom lane