postgres notifications from inside stored procedures/triggers not immediately received by node.js listener client

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 prog

As far as I'm aware what you're trying to do, by issuing a commit inside a procedure, is use an autonomous transaction. Postgres does not support them inside stored procedures/functions, and the behaviour you are seeing is correct - the (implicit) transaction does not complete until the procedure completes.

This page discusses the issue in detail along with potential workarounds.

StackExchange Database Administrators Q#236578, answer score: 2