而不是使用标志report_subscriber就其本身而言，我认为您最好使用一个单独的待处理更改队列。这样做有几个好处：
- 无触发递归
- 在引擎盖下，
UPDATE只是DELETE + re-INSERT，因此插入队列实际上比翻转标志更便宜
- 可能便宜很多，因为你只需要排队不同的
report_ids，而不是克隆整个report_subscriber记录，并且您可以在临时表中执行此操作，因此存储是连续的并且不需要将任何内容同步到磁盘
- 翻转标志时无需担心竞争条件，因为队列是当前事务的本地队列（在您的实现中，受
UPDATE report_subscriber不一定与您在SELECT...)
因此，初始化队列表：
CREATE FUNCTION create_queue_table() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
CREATE TEMP TABLE pending_subscriber_changes(report_id INT UNIQUE) ON COMMIT DROP;
RETURN NULL;
END
$$;
CREATE TRIGGER create_queue_table_if_not_exists
BEFORE INSERT OR UPDATE OF report_id, subscriber_name OR DELETE
ON report_subscriber
FOR EACH STATEMENT
WHEN (to_regclass('pending_subscriber_changes') IS NULL)
EXECUTE PROCEDURE create_queue_table();
...在更改到达时对其进行排队，忽略已排队的任何内容：
CREATE FUNCTION queue_subscriber_change() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
IF TG_OP IN ('DELETE', 'UPDATE') THEN
INSERT INTO pending_subscriber_changes (report_id) VALUES (old.report_id)
ON CONFLICT DO NOTHING;
END IF;
IF TG_OP IN ('INSERT', 'UPDATE') THEN
INSERT INTO pending_subscriber_changes (report_id) VALUES (new.report_id)
ON CONFLICT DO NOTHING;
END IF;
RETURN NULL;
END
$$;
CREATE TRIGGER queue_subscriber_change
AFTER INSERT OR UPDATE OF report_id, subscriber_name OR DELETE
ON report_subscriber
FOR EACH ROW
EXECUTE PROCEDURE queue_subscriber_change();
...并在语句末尾处理队列：
CREATE FUNCTION process_pending_changes() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
UPDATE report
SET report_subscribers = ARRAY(
SELECT DISTINCT subscriber_name
FROM report_subscriber s
WHERE s.report_id = report.report_id
ORDER BY subscriber_name
)
FROM pending_subscriber_changes c
WHERE report.report_id = c.report_id;
DROP TABLE pending_subscriber_changes;
RETURN NULL;
END
$$;
CREATE TRIGGER process_pending_changes
AFTER INSERT OR UPDATE OF report_id, subscriber_name OR DELETE
ON report_subscriber
FOR EACH STATEMENT
EXECUTE PROCEDURE process_pending_changes();
这有一个小问题：UPDATE不提供有关更新顺序的任何保证。这意味着，如果这两个语句同时运行：
INSERT INTO report_subscriber (report_id, subscriber_name) VALUES (1, 'a'), (2, 'b');
INSERT INTO report_subscriber (report_id, subscriber_name) VALUES (2, 'x'), (1, 'y');
...如果他们尝试更新，就有可能陷入僵局report以相反的顺序记录。您可以通过对所有更新强制执行一致的顺序来避免这种情况，但不幸的是没有办法附加ORDER BY to an UPDATE陈述;我认为你需要求助于游标：
CREATE FUNCTION process_pending_changes() RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
target_report CURSOR FOR
SELECT report_id
FROM report
WHERE report_id IN (TABLE pending_subscriber_changes)
ORDER BY report_id
FOR NO KEY UPDATE;
BEGIN
FOR target_record IN target_report LOOP
UPDATE report
SET report_subscribers = ARRAY(
SELECT DISTINCT subscriber_name
FROM report_subscriber
WHERE report_id = target_record.report_id
ORDER BY subscriber_name
)
WHERE CURRENT OF target_report;
END LOOP;
DROP TABLE pending_subscriber_changes;
RETURN NULL;
END
$$;
如果客户端尝试在同一事务中运行多个语句，这仍然有可能出现死锁（因为更新顺序仅应用于每个语句中，但更新锁将一直保持到提交为止）。你可以通过关闭来解决这个问题（某种程度上）process_pending_changes()仅在事务结束时执行一次（缺点是，在该事务中，您不会看到自己的更改反映在report_subscribers array).
如果您认为值得麻烦填写的话，这里是“提交时”触发器的通用大纲：
CREATE FUNCTION run_on_commit() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
<your code goes here>
RETURN NULL;
END
$$;
CREATE FUNCTION trigger_already_fired() RETURNS BOOLEAN LANGUAGE plpgsql VOLATILE AS $$
DECLARE
already_fired BOOLEAN;
BEGIN
already_fired := NULLIF(current_setting('my_vars.trigger_already_fired', TRUE), '');
IF already_fired IS TRUE THEN
RETURN TRUE;
ELSE
SET LOCAL my_vars.trigger_already_fired = TRUE;
RETURN FALSE;
END IF;
END
$$;
CREATE CONSTRAINT TRIGGER my_trigger
AFTER INSERT OR UPDATE OR DELETE ON my_table
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
WHEN (NOT trigger_already_fired())
EXECUTE PROCEDURE run_on_commit();
