PostgreSQL中一行只执行一次延迟触发器

Question

我有一个设置为在某一列更新时触发的延迟AFTER UPDATE触发器，该列是我用作计数器的整数类型。

我不确定，但似乎如果在事务期间递增该特定列100次，则触发器会排队并在事务结束时执行100次。

无论我增加该列的次数有多少，我都希望触发器只被调度一次。

有什么办法可以做到这一点吗？或者，如果触发触发器无论是否重复都必须排队，那么我可以在触发器第一次运行时清除此队列吗？

Postgres的版本是9.1。以下是我得到的内容：

CREATE CONSTRAINT TRIGGER counter_change
    AFTER UPDATE OF "Counter" ON "table"
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
    EXECUTE PROCEDURE counter_change();

CREATE OR REPLACE FUNCTION counter_change()
    RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
BEGIN

PERFORM some_expensive_procedure(NEW."id");

RETURN NEW;

END;$$;

- jjames

---

你使用的Postgres版本会有所帮助。同时请提供触发器和触发器函数的（基本）代码。 - Erwin Brandstetter

Answer 1

这是一个棘手的问题。但是在PostgreSQL 9.0中引入了每列触发器和条件触发器执行，可以解决这个问题。

对于此解决方案，您需要每行一个"已更新"标志。为简单起见，在同一表中使用boolean列。但它也可以在另一个表中，甚至是每个事务的临时表中。

昂贵的有效载荷每行执行一次，其中计数器被更新（一次或多次）。

这也应该执行良好，因为...

• ...它避免了在根处调用多个触发器（可扩展）
• ...不会更改其他行（最小化表膨胀）
• ...不需要昂贵的异常处理。

考虑以下内容：

演示

在测试环境中使用x作为单独模式在PostgreSQL 9.1中进行测试。

表和虚拟行

-- DROP SCHEMA x;
CREATE SCHEMA x;

CREATE TABLE x.tbl (
 id int
,counter int
,trig_exec_count integer  -- for monitoring payload execution.
,updated bool);

添加两行以演示它可以处理多行：

INSERT INTO x.tbl VALUES
 (1, 0, 0, NULL)
,(2, 0, 0, NULL);

触发器函数和触发器

1.) 执行昂贵的有效载荷

CREATE OR REPLACE FUNCTION x.trg_upaft_counter_change_1()
    RETURNS trigger AS
$BODY$
BEGIN

 -- PERFORM some_expensive_procedure(NEW.id);
 -- Update trig_exec_count to count execution of expensive payload.
 -- Could be in another table, for simplicity, I use the same:

UPDATE x.tbl t
SET    trig_exec_count = trig_exec_count + 1
WHERE  t.id = NEW.id;

RETURN NULL;  -- RETURN value of AFTER trigger is ignored anyway

END;
$BODY$ LANGUAGE plpgsql;

2.) 标记行已更新。

CREATE OR REPLACE FUNCTION x.trg_upaft_counter_change_2()
    RETURNS trigger AS
$BODY$
BEGIN

UPDATE x.tbl
SET    updated = TRUE
WHERE  id = NEW.id;
RETURN NULL;

END;
$BODY$ LANGUAGE plpgsql;

3.) 重置“updated”标志。

CREATE OR REPLACE FUNCTION x.trg_upaft_counter_change_3()
    RETURNS trigger AS
$BODY$
BEGIN

UPDATE x.tbl
SET    updated = NULL
WHERE  id = NEW.id;
RETURN NULL;

END;
$BODY$ LANGUAGE plpgsql;

触发器名称很重要！当它们被调用时，它们按字母顺序执行相同的事件。

1.) 负载，仅在尚未“更新”时：

CREATE CONSTRAINT TRIGGER upaft_counter_change_1
    AFTER UPDATE OF counter ON x.tbl
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
    WHEN (NEW.updated IS NULL)
    EXECUTE PROCEDURE x.trg_upaft_counter_change_1();

2.) 如果该行尚未标记为“已更新”，请将其标记为已更新：

CREATE TRIGGER upaft_counter_change_2   -- not deferred!
    AFTER UPDATE OF counter ON x.tbl
    FOR EACH ROW
    WHEN (NEW.updated IS NULL)
    EXECUTE PROCEDURE x.trg_upaft_counter_change_2();

3.) 重置标志位。由于触发条件，不会出现无限循环。

CREATE CONSTRAINT TRIGGER upaft_counter_change_3
    AFTER UPDATE OF updated ON x.tbl
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
    WHEN (NEW.updated)                 --
    EXECUTE PROCEDURE x.trg_upaft_counter_change_3();

测试

分别运行UPDATE和SELECT以查看延迟效果。如果在一个事务中同时执行（一起执行），则SELECT将显示新的tbl.counter，但旧的tbl2.trig_exec_count。

UPDATE x.tbl SET counter = counter + 1;

SELECT * FROM x.tbl;

现在，多次更新计数器（在一个事务中）。负载只会执行一次。哇！

UPDATE x.tbl SET counter = counter + 1;
UPDATE x.tbl SET counter = counter + 1;
UPDATE x.tbl SET counter = counter + 1;
UPDATE x.tbl SET counter = counter + 1;
UPDATE x.tbl SET counter = counter + 1;

SELECT * FROM x.tbl;

- Erwin Brandstetter

---

我知道我来晚了，但是是否有可能仅在最后一个UPDATE语句中执行有效载荷函数？ - andreak

Answer 2

我不知道有没有一种方法可以将触发器执行折叠为每个事务中的每个（更新的）行，但是您可以使用TEMPORARY ON COMMIT DROP表来模拟这种情况，该表跟踪已修改的行并仅对每行每个tx执行一次昂贵的操作：

CREATE OR REPLACE FUNCTION counter_change() RETURNS TRIGGER
AS $$
BEGIN
  -- If we're the first invocation of this trigger in this tx,
  -- make our scratch table.  Create unique index separately to
  -- suppress avoid NOTICEs without fiddling with log_min_messages
  BEGIN
    CREATE LOCAL TEMPORARY TABLE tbl_counter_tx_once
      ("id" AS_APPROPRIATE NOT NULL)
      ON COMMIT DROP;
    CREATE UNIQUE INDEX ON tbl_counter_tx_once AS ("id");
  EXCEPTION WHEN duplicate_table THEN
    NULL;
  END;

  -- If we're the first invocation in this tx *for this row*,
  -- then do our expensive operation.
  BEGIN
    INSERT INTO tbl_counter_tx_once ("id") VALUES (NEW."id");
    PERFORM SOME_EXPENSIVE_OPERATION_HERE(NEW."id");
  EXCEPTION WHEN unique_violation THEN
    NULL;
  END;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

当然，使用临时表存在名称冲突的风险，因此请谨慎选择。

- pilcrow

---

异常处理很昂贵，也不是必需的。考虑使用 CREATE TABLE IF NOT EXISTS（9.1中新增）和 IF NOT EXISTS (SELECT ..) THEN ...; INSERT INTO tbl ..; END IF;。此外，在PostgreSQL中，“LOCAL”只是一个噪声词。 - Erwin Brandstetter

关于非异常处理，是的，有不止一种方法可以做到。 （事实上，我第一个测试的解决方案是CREATE IF NOT EXISTS。）关于LOCAL，是的，我知道，在这里使用它强化了使用此表的目的。 - pilcrow

除此之外，您的解决方案也值得点赞。高级的东西。您可以使用 txid_current() 将当前事务 ID 包含在临时表的名称中。不过这将强制您使用带有 EXECUTE 的动态 SQL。或者更好的方法是，在临时表中添加一个名为 xid 的列，并使用稳定的名称，这样您就可以避免静态 SQL 的问题！ - Erwin Brandstetter

这似乎增加了不少新表、索引和异常处理的负担，目前我每秒处理200笔交易，并且还在上升。此外，我需要它在事务的所有工作完成后运行，而不是在特定表格的第一次插入之后运行。不过这是一个不错的技巧，谢谢。 - jjames

关于“似乎有点沉重”，可能是这样，但我建议进行分析。关于“我需要在事务之后运行它”，这由触发器约束定义的DEFERRABLE性处理，而不是要执行的函数的定义。 - pilcrow

Answer 3

这通常是做不到的，你需要一些技巧来实现。

例如，考虑一个包含余额的 balances(account_id, balance) 表格，使得您不希望在交易结束时任何余额变为负数，但由于表格的部分更新，它可以在交易期间变为负数。

如果您进行普通的 balance >= 0 检查，它无法被延迟执行并且不起作用。如果您创建一个延迟约束触发器并检查 new.balance >= 0，它也不起作用，因为在触发器被调度时，new 的值已经固定，而不是在执行时确定。

因此，一个潜在的解决方案是在触发器函数中实际查询表格：

create function check_balance_trigger()
returns trigger language plpgsql as $$
begin
    -- This queries the table at the time the trigger is executed:
    select * from balances into new where account_id = new.account_id;
    if new.balance < 0 then
        raise 'Balance cannot be negative: %, %', new.account_id, new.balance;
    end if;
    return new;
end $$;

create constraint trigger check_balance
after insert or update on balances deferrable initially deferred
for each row execute function check_balance_trigger();

- alecov