We are using postgres 11's stored procedures to do looped update of rows in batches. After a batch is updated, a 'progress' table is updated with progress percent and status, THEN a notification (using pg_notify) is sent. A nodejs client listens for the notification and handles it.
For testing, we added PERFORM pg_sleep(5) right after pg_notify call so we can see the progress percent slowly increment.
The problem is the notifications aren't sent immediately to listeners when PERFORM pg_notify is called but instead ALL notifications are sent AFTER the stored procedure has completed running.
Has anyone encountered this problem and found any solution (aside from dblink)?
We've tried the following inside the Stored Procedure:
- moved pg_sleep at top of loop
- adding commit; after pg_notify
- use notify channel, 'payload instead of pg_notify
We then tried using triggers + pg_notify. When that didn't work we also tried using execute format('notify channel, ''%s''', payload). The same result.
Some sample codes:
Node.js
const pg = require('pg');
let client = new pg.Client({user: '', database: '', host: '', password: '', port: 5432});
client.connect();
client.query('LISTEN progress_updates');
client.on('notification', function(data) {
console.log('notification: ', data);
});
Backend:
-- table
drop table if exists operations;
create table progress ( id serial primary key, percent int default 0 );
-- stored procedure
CREATE OR REPLACE PROCEDURE my_stored_proc() language plpgsql
AS $$
DECLARE
progress_id int;
BEGIN
insert into progress (percent) values (0) returning id into progress_id;
commit;
update progress set percent= 10 where id = progress_id ;
commit;
perform pg_notify('progress_updates', '{"percent": 10}');
-- notify progress_updates, '{"percent": 10}'
perform pg_sleep(5);
update progress set percent= 30 where id = progress_id ;
commit;
perform pg_notify('progress_updates', '{"percent": 30}');
perform pg_sleep(5);
update progress set percent= 70 where id = progress_id ;
commit;
perform pg_notify('progress_updates', '{"percent": 70}');
perform pg_sleep(5);
update progress set percent= 100 where id = progress_id ;
commit;
perform pg_notify('progress_updates', '{"percent": 100}');
END;
$$;
-- trigger
create or replace function notify_progress_trigger() returns trigger
language plpgsql
as
$$
DECLARE
payload TEXT;
BEGIN
raise notice 'TRIGGERED ON %', NOW();
payload := '{"percent": ' || NEW.percent || '}';
perform pg_notify('progress_updates', payload);
-- execute format('notify progress_updates, ''%s''', payload);
return NEW;
END;
$$;
alter function notify_progress_trigger() owner to postgres;
create trigger watched_progress_trigger
after insert or update
on operations
execute procedure notify_progress_trigger();
-- Call stored proc manually
call my_stored_proc();
We expected the notifications will be received one by one by the listener after each batch gets updated while the stored procedure is still running, but instead we receive all of the notifications in quick succession after the stored procedure completes.
