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 1kidJC-0006th-PQ for pgsql-general@arkaria.postgresql.org; Fri, 27 Nov 2020 12:57:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kidJB-0002Jf-DF for pgsql-general@arkaria.postgresql.org; Fri, 27 Nov 2020 12:57:05 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kidJA-0002JY-Qs for pgsql-general@lists.postgresql.org; Fri, 27 Nov 2020 12:57:05 +0000 Received: from smtpout2.smhi.se ([2001:67c:274:5501::39]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kidJ8-0002MZ-0P for pgsql-general@lists.postgresql.org; Fri, 27 Nov 2020 12:57:03 +0000 Received: from winserv743.ad.smhi.se (winserv743.ad.smhi.se [172.16.6.38]) by smtpout2.smhi.se (Postfix) with ESMTPS id 291CE10A8A77 for ; Fri, 27 Nov 2020 12:56:54 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=smhi.se; s=20180112; t=1606481814; bh=kB2eFKBRhzGnpkzaYqnpor7kd7wfZButoGKUkV0sH6M=; h=From:To:CC:Subject:Date:From; b=xiA6rq20yS11okP2TsifpehlYhLT6nJ/kRybaFcr0yozHj96oeGMsykPgxSM0mpBm GsKHrgK+IXu6Ip1E1MBonbXpDsXK3YD5hjnK1FAvo9oqhpzYTABr7Cxt+XUNXB3Zm+ +ckfSkRzi+Imc8wvlO1aHsw98ZCqgXbvZ5rEFKjg= 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 13:56:53 +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 13:56:53 +0100 From: Gustavsson Mikael To: pgsql-generallists.postgresql.org CC: Svensson Peter , Almen Anders Subject: Problem with pg_notify / listen Thread-Topic: Problem with pg_notify / listen Thread-Index: AQHWxLzC/H89OzBnPkSJXMLfqEVBDg== Date: Fri, 27 Nov 2020 12:56:53 +0000 Message-ID: <1b8561412e8a4f038d7a491c8b922788@smhi.se> 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_1b8561412e8a4f038d7a491c8b922788smhise_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_1b8561412e8a4f038d7a491c8b922788smhise_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable 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. 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 hol= ding up for an hour or so before it starts growing again. We have been using pg_notify / listen for about three years and haven=92t r= un in to this problem before so it seems to me that something happened in t= he 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 --_000_1b8561412e8a4f038d7a491c8b922788smhise_ Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable

Hi.

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

The queue is filling up a= nd starts issuing warnings like 
WARNING:  NOTI= FY queue is 87% full
DETAIL:  The s= erver 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-ru= nning 'active' or 'idle in transaction' transactions on the server.
 
We restarted the ap= plication 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. Af= ter 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_not= ify / listen for about three years and haven= =92t 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


--_000_1b8561412e8a4f038d7a491c8b922788smhise_--