From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eser HEUFT [Germany] <peter.eser@heuft.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: LISTEN NOTIFY sometimes huge delay
Date: Thu, 28 Apr 2022 12:38:35 -0400
Message-ID: <21909.1651163915@sss.pgh.pa.us> (raw)
In-Reply-To: <feeaf68f-b048-5d62-8b60-50c2245b7113@heuft.com>
References: <feeaf68f-b048-5d62-8b60-50c2245b7113@heuft.com>
"Peter Eser HEUFT [Germany]" <peter.eser@heuft.com> writes:
> I have a table with time series data and on this table a trigger for
> notifies:
> containers_notify AFTER INSERT ON containers FOR EACH ROW EXECUTE
> PROCEDURE containers_notify('containers_notify_collector')
> and the function does:
> PERFORM pg_notify(CAST(TG_ARGV[0] AS text), row_to_json(NEW)::text);
> This works as a charm but occasionally (I think with more load on the
> system) the notifications are received much time (up to hours!) after
> the INSERTs.
> Nevertheless no notifications become lost, they are only very late! The
> delay grows, seems as a queue grows, but the java process tries to fetch
> the notifications fairly fast,
Hm. We've not previously had reports of late notifications. One idea
that comes to mind is that the server won't deliver notifications as
long as the client has an open transaction, so is it possible your
listening process sometimes forgets to close its transaction?
> Versions:
> PostgreSQL 10.12 on x86_64-pc-linux-gnu, compiled by
> x86_64-pc-linux-gnu-gcc (Gentoo 6.4.0-r1 p1.3) 6.4.0, 64-bit
> JDBC 42.2.23
That's pretty old. We've made a number of changes to the LISTEN/NOTIFY
code since then; although in reading the commit log entries about them,
nothing is said about long-delayed notifications.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, peter.eser@heuft.com, pgsql-performance@lists.postgresql.org
Subject: Re: LISTEN NOTIFY sometimes huge delay
In-Reply-To: <21909.1651163915@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox