NOTIFY queue is at 66% and climbing...

From: Jeff Ross <jross(at)openvistas(dot)net>
To: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: NOTIFY queue is at 66% and climbing...
Date: 2021-10-13 23:09:58
Message-ID: c8d2f9b4-9739-a18c-565a-84749f9dcca8@openvistas.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

On 10.15 I'm getting the following on a logically replicated server.

From the CSV logs:

2021-10-13 18:49:39.792 EDT,,,213601,,6143c257.34261,64243,,2021-09-16
18:16:55 EDT,4/3914851,60709901,WARNING,01000,"NOTIFY queue is 66%
full",,,,,,,,,""
2021-10-13 18:49:46.058 EDT,,,213601,,6143c257.34261,64244,,2021-09-16
18:16:55 EDT,4/3914855,60709905,WARNING,01000,"NOTIFY queue is 66%
full",,,,,,,,,""
2021-10-13 18:49:51.934 EDT,,,213601,,6143c257.34261,64245,,2021-09-16
18:16:55 EDT,4/3914862,60709915,WARNING,01000,"NOTIFY queue is 66%
full",,,,,,,,,""
2021-10-13 18:50:00.516 EDT,,,213601,,6143c257.34261,64246,,2021-09-16
18:16:55 EDT,4/3914864,60709917,WARNING,01000,"NOTIFY queue is 66%
full",,,,,,,,,""
2021-10-13 18:50:08.003 EDT,,,213601,,6143c257.34261,64247,,2021-09-16
18:16:55 EDT,4/3914871,60709926,WARNING,01000,"NOTIFY queue is 66%
full",,,,,,,,,""

The very fine docs for 10 say

"There is a queue that holds notifications that have been sent but not
yet processed by all listening sessions. If this queue becomes full,
transactions calling |NOTIFY| will fail at commit. The queue is quite
large (8GB in a standard installation) and should be sufficiently sized
for almost every use case. However, no cleanup can take place if a
session executes |LISTEN| and then enters a transaction for a very long
time. Once the queue is half full you will see warnings in the log file
pointing you to the session that is preventing cleanup. In this case you
should make sure that this session ends its current transaction so that
cleanup can proceed."

We do have a client that has access to this logically replicated server
and I have no idea what they have done regards LISTEN/NOTIFY, and my
query to them about this has not yielded a response.

In the CSV logs above what part points to "the session that is
preventing cleanup" so that I can kill it?  pg_stat_activity yields no
clues.

Thanks,

Jeff

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Phil Endecott 2021-10-13 23:12:34 Re: Replication between different architectures
Previous Message Tomas Vondra 2021-10-13 21:19:30 Re: GIN index