oops! with with command
psql -c "begin;select pg_notify('channel', concat_ws(' = ', 'a',clock_timestamp()::text));commit;select pg_sleep(10);begin;select pg_notify('channel', concat_ws(' = ', 'b',clock_timestamp()::text));select 1/0;commit;select now(), clock_timestamp();"
nothing notifies are received!
Thanks for the tip! I learned outer code of function exec_simple_query and I seen "idle" condition of calling ProcessCompletedNotifies! In my postgres job scheduler
https://github.com/RekGRpth/pg_task I copied exec_simple_query and modified it to call ProcessCompletedNotifies after CommitTransactionCommand and it works!
And how about several transaction in new procedures?
That does seem like a legitimate gripe.
create or replace procedure foo() LANGUAGE plpgsql as $$
begin
FOR i IN 1..100 LOOP
perform pg_notify('channel','HI');
perform pg_sleep(1);
commit;
end loop;
end; $$;
CALL foo();
An actively polling listener gets all notification all at once at the end. (I just use psql with a "LISTEN channel;" and the repeatedly execute ";" to get it to poll)
Cheers,
Jeff