Thread: LISTEN NOTIFY sometimes huge delay
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
"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