From: Peter Eser HEUFT [Germany] <peter.eser@heuft.com>
To: pgsql-performance@lists.postgresql.org
Subject: LISTEN NOTIFY sometimes huge delay
Date: Thu, 28 Apr 2022 16:28:15 +0200
Message-ID: <feeaf68f-b048-5d62-8b60-50c2245b7113@heuft.com> (raw)
Hi all,
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);
so that another application (java) fetches every inserted row as a JSON
for further processing every half second:
...listenStatement.execute("LISTEN 'containers_notify_collector'");
...PGNotification notifications[] =
((org.postgresql.PGConnection)notifyPGCon.getUnderlyingConnection()).getNotifications();
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,
so there should be no queue growing..
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
The commit of the application inserting the data is ok/fast. So the
insert of the data is not slowed down.
Are the notifications delivered asynchronously to the commit/trigger?
Thanks for any help,
Peter
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: peter.eser@heuft.com, pgsql-performance@lists.postgresql.org
Subject: Re: LISTEN NOTIFY sometimes huge delay
In-Reply-To: <feeaf68f-b048-5d62-8b60-50c2245b7113@heuft.com>
* 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