Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kif39-0002mC-Vg for pgsql-general@arkaria.postgresql.org; Fri, 27 Nov 2020 14:48:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kif38-0004nb-MM for pgsql-general@arkaria.postgresql.org; Fri, 27 Nov 2020 14:48:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kif38-0004nT-9E for pgsql-general@lists.postgresql.org; Fri, 27 Nov 2020 14:48:38 +0000 Received: from smtpout1.smhi.se ([2001:67c:274:5501::30]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kif34-00019q-0T for pgsql-general@lists.postgresql.org; Fri, 27 Nov 2020 14:48:37 +0000 Received: from winserv743.ad.smhi.se (winserv743.ad.smhi.se [172.16.6.38]) by smtpout1.smhi.se (Postfix) with ESMTPS id A56A2109508B; Fri, 27 Nov 2020 14:48:29 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=smhi.se; s=20180112; t=1606488509; bh=gyfqomwNMdOJ8Vj8/2INyxtMo3diYqV/4c1TICx6WAE=; h=From:To:CC:Subject:Date:References:In-Reply-To:From; b=Ch3QYWQ0ADruuPrj97k2VJFjKYJJpi1qnWImC52k0NxijFgirOMEFoMPImKGxllrc sgzK4jePfkupHtPE3OLZKE6EKHpF5E5cSDk/JvU2i/eYa4LROVdPAY5TSIe6E/ayhZ CvvNv2nl6fkAZU/uQtm8U6ZK9RSDJ1buHjw9yMAs= Received: from winserv744.ad.smhi.se (172.16.6.35) by winserv743.ad.smhi.se (172.16.6.38) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.2106.2; Fri, 27 Nov 2020 15:48:28 +0100 Received: from winserv744.ad.smhi.se ([fe80::c19c:d0f0:5830:a72b]) by winserv744.ad.smhi.se ([fe80::c19c:d0f0:5830:a72b%9]) with mapi id 15.01.2106.004; Fri, 27 Nov 2020 15:48:28 +0100 From: Gustavsson Mikael To: "David G. Johnston" CC: pgsql-generallists.postgresql.org , Svensson Peter , Almen Anders Subject: SV: Problem with pg_notify / listen Thread-Topic: Problem with pg_notify / listen Thread-Index: AQHWxLzC/H89OzBnPkSJXMLfqEVBDqnb+NIAgAAToYw= Date: Fri, 27 Nov 2020 14:48:28 +0000 Message-ID: <19d46e8276a54d24b09b6bcbd3bc8e17@smhi.se> References: <1b8561412e8a4f038d7a491c8b922788@smhi.se>, In-Reply-To: Accept-Language: sv-SE, en-US Content-Language: sv-SE X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.122.1.210] Content-Type: multipart/alternative; boundary="_000_19d46e8276a54d24b09b6bcbd3bc8e17smhise_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_19d46e8276a54d24b09b6bcbd3bc8e17smhise_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi David, Thanks for fast reply! We had to restart the server to avoid problems with a full queue s=E5 proce= ss 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=E5n: David G. Johnston Skickat: den 27 november 2020 15:28 Till: Gustavsson Mikael Kopia: pgsql-generallists.postgresql.org; Svensson Peter; Almen Anders =C4mne: Re: Problem with pg_notify / listen On Friday, November 27, 2020, Gustavsson Mikael > wrote: Hi. After applying the latest patch we have encountered a problem with the pg_n= otify 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 transact= ion. 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. --_000_19d46e8276a54d24b09b6bcbd3bc8e17smhise_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi David,


Thanks for fast reply!


We had to restart the server to avoid problems with a full queue s=E5 pr= ocess 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=E5n: David G. Johnston &= lt;david.g.johnston@gmail.com>
Skickat: den 27 november 2020 15:28
Till: Gustavsson Mikael
Kopia: pgsql-generallists.postgresql.org; Svensson Peter; Almen Ande= rs
=C4mne: Re: Problem with pg_notify / listen
 
On Friday, November 27, 2020, Gustavsson Mikael <mikael.gustavsson@smhi.se> wrote:

Hi.

After applying the la= test 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 ser= ver process with PID 2969993 is among those with the oldest transactions.
NOTIFY queue cannot b= e emptied until that process ends its current transaction.

There is no long-runn= ing 'active' or 'idle in transaction' transactions on the server.
 <= /div>

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

Thanks!

David J.
--_000_19d46e8276a54d24b09b6bcbd3bc8e17smhise_--