node.js 偵聽器客戶端未立即收到來自儲存過程/觸發器內部的 postgres 通知

我們正在使用 postgres 11 的儲存過程來批量循環更新行。更新批次後，將使用進度百分比和狀態更新“進度”表，然後發送通知（使用 pg_notify）。nodejs 客戶端監聽通知並處理它。

對於測試，我們PERFORM pg_sleep(5)在pg_notify通話後立即添加，因此我們可以看到進度百分比緩慢增加。

問題是在呼叫時通知不會立即發送給偵聽器，PERFORM pg_notify而是在儲存過程完成執行後發送所有通知。

有沒有人遇到過這個問題並找到了任何解決方案（除了 dblink）？

我們在儲存過程中嘗試了以下內容： -pg_sleep在循環頂部移動 - 添加送出；after pg_notify

• 使用notify channel, 'payload而不是 pg_notify

然後我們嘗試使用觸發器 + pg_notify。當那不起作用時，我們還嘗試使用execute format('notify channel, ''%s''', payload). 同樣的結果。

一些範常式式碼：

節點.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);
});

後端：

-- 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();

我們希望在儲存過程仍在執行時，在每個批次更新後，偵聽器將一一收到通知，但在儲存過程完成後，我們會快速連續收到所有通知。

據我所知，通過commit在程序內部發出一個程序，您正在嘗試做的是使用自主事務。Postgres 在儲存過程/函式中不支持它們，並且您看到的行為是正確的 - （隱式）事務在過程完成之前不會完成。

此頁面詳細討論了該問題以及潛在的解決方法。

引用自：https://dba.stackexchange.com/questions/236578