LISTEN NOTIFY sometimes huge delay

Поиск
Список
Период
Сортировка
От Peter Eser HEUFT [Germany]
Тема LISTEN NOTIFY sometimes huge delay
Дата
Msg-id feeaf68f-b048-5d62-8b60-50c2245b7113@heuft.com
обсуждение исходный текст
Ответы Re: LISTEN NOTIFY sometimes huge delay  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
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







В списке pgsql-performance по дате отправления:

Предыдущее
От: Emil Iggland
Дата:
Сообщение: Re: Performance differential when 0 values present vs when 1 values present. Planner return 52k rows when 0 expected.
Следующее
От: Tom Lane
Дата:
Сообщение: Re: LISTEN NOTIFY sometimes huge delay