I open psql (PostgreSQL 9.5.4 on Arch Linux) in one terminal A and issue LISTEN "notif";
In another terminal B, I then run the following script (psql -f myscript.sql).
This creates a table with two triggers. One trigger fires if a row is added where send is TRUE, and the other triggers if a row is updated such that send goes from being FALSE to TRUE. Both triggers send a notification.
DROP TRIGGER IF EXISTS do_notif ON notif;
DROP TRIGGER IF EXISTS do_notif2 ON notif;
DROP TABLE IF EXISTS notif;
CREATE TABLE notif (id INT PRIMARY KEY, send BOOLEAN, msg TEXT);
CREATE OR REPLACE FUNCTION post() RETURNS TRIGGER AS $$
BEGIN
PERFORM pg_notify('notif', '+' || NEW.id::TEXT || ',' || coalesce(NEW.msg, '(null)'));
RETURN NEW;
END $$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION post2() RETURNS TRIGGER AS $$
BEGIN
PERFORM pg_notify('notif', '~' || NEW.id::TEXT || ',' || coalesce(NEW.msg, '(null)'));
RETURN NEW;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER do_notif AFTER INSERT ON notif
FOR EACH ROW WHEN (NEW.send)
EXECUTE PROCEDURE post();
CREATE TRIGGER do_notif2 AFTER UPDATE OF send ON notif
FOR EACH ROW WHEN (NEW.send AND NOT OLD.send)
EXECUTE PROCEDURE post2();
-- LISTEN "notif";
INSERT INTO notif VALUES (1, FALSE, 'update');
INSERT INTO notif VALUES (2, TRUE, 'insert');
UPDATE notif SET send = TRUE;
UPDATE notif SET send = FALSE;
UPDATE notif SET send = TRUE;
START TRANSACTION;
INSERT INTO notif VALUES (10, FALSE, 'a'), (11, TRUE, 'b'), (12, TRUE, 'c');
UPDATE notif SET send = TRUE WHERE id = 10;
COMMIT
I expect that the relevant INSERT and UPDATE queries should result in triggers being called, which send notifications, to be received in terminal A.
This does not happen. I have to manually run LISTEN "notif"; again in terminal A, which instantly gives me the missing notifications.
If I un-comment the LISTEN "notif"; in that script, then the psql instance running the script (B) will write notifications to the terminal at the relevant places (after the non-transactional insert/update which set send to TRUE, and after the transaction too).
But A still does not show these unless I run LISTEN "notif"; again in A (or any other query, e.g. SELECT TRUE;). It isn't some terminal buffering issue, as merely hitting ENTER in A does not result in the notifications appearing.
PostgreSQL does not appear to be immediately delivering notifications across either different connections or different processes, but does deliver immediately when the listener is the same process as the one which generated the notifications.
psql checks the notification queue after each command (more specifically, after receiving any results from the server). So you can use listen channel once, and each subsequent command will check if there are new notifications.
Programs based on libpq.c standard library operate in the same way, checking notification queue with the function PQnotifies after receiving data from the server. They have also another possibility. In the absence of communication with the server at particular time they can refresh notification queue using the function PQconsumeInput. In this way they receive notifications in asynchronous manner. Notification support by drivers may of course be implemented at a higher level.