在PostgreSQL中每行执行一次延迟触发 [英] Execute deferred trigger only once per row in PostgreSQL

问题描述

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

我不是100％确定，但看起来像是在事务中增加特定列100次，触发器在事务结束时排队等待执行100次。

我希望触发器每行只能安排一次，不管我多少次，这个列增加了。

我可以这样做吗？
或者如果触发的触发器必须排队，无论它们是否重复，可以在触发器的第一次运行期间清除此队列？

Postgres版本为9.1 。这是我得到的：

  CREATE CONSTRAINT TRIGGER counter_change 
更新CounterONtable
 DEFERRABLE INITIALLY DEFERRED 
为每个R $ 
执行程序counter_change（）; 
 
 CREATE OR REPLACE FUNCTION counter_change（）
 RETURNS trigger 
 LANGUAGE plpgsql 
 AS $$ 
 DECLARE 
 BEGIN 
 
 PERFORM some_expensive_procedure（NEW。id）; 
 
返回新; 
 
 END; $$; 
  

解决方案

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

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

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

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

• ...它可以避免在根目录下多次调用触发器（缩放）


• ...不会更改其他行（最小化表膨胀）


• ...不需要昂贵的异常处理。

p>

演示

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

表和虚拟行

  DROP SCHEMA x; 
 CREATE SCHEMA x; 
 
 CREATE TABLE x.tbl（
 id int 
，counter int 
，trig_exec_count整数 - 用于监视有效负载执行
，更新的bool）; 
  

插入两行以演示它与多个行一起使用：

  INSERT INTO x.tbl VALUES 
（1，0，0，NULL）
，（2，0，0，NULL）; 
  

触发器功能和触发器

1。 ）执行昂贵的有效载荷

 创建或替换功能x.trg_upaft_counter_change_1（）
返回触发器AS 
 $ BODY $ 
 BEGIN 
 
  -  PERFORM some_expensive_procedure（NEW.id）; 
  - 更新trig_exec_count来计算昂贵的有效载荷的执行。 
  - 可以在另一个表中，为了简单起见，我使用相同的方法：
 
更新x.tbl t 
 SET trig_exec_count = trig_exec_count + 1 
 WHERE t。 id = NEW.id; 
 
 RETURN NULL; -  AFTER触发器的RETURN值无论如何也被忽略
 
 END; 
 $ BODY $ LANGUAGE plpgsql; 
  

2。）更新标记行。

 创建或替换功能x.trg_upaft_counter_change_2（）
返回触发器AS 
 $ BODY $ 
 BEGIN 
 
更新x。 tbl 
 SET updated = TRUE 
 WHERE id = NEW.id; 
 RETURN NULL; 
 
结束; 
 $ BODY $ LANGUAGE ppgsql; 
  

3。）重置更新标志。

 创建或替换功能x.trg_upaft_counter_change_3（）
 RETURNS触发器AS 
 $ BODY $ 
 BEGIN 
 
更新x .tbl 
 SET updated = NULL 
 WHERE id = NEW.id; 
 RETURN NULL; 
 
结束; 
 $ BODY $ LANGUAGE plpgsql; 
  

触发名称是相关的！被称为相同的事件，他们按字母顺序执行。

1。）有效载荷，只有当没有更新：

  CREATE CONSTRAINT TRIGGER upaft_counter_change_1 
计数器更新后x.tbl 
 DEFERRABLE INITIALLY DEFERRED 
为每个ROW 
 WHEN（ NEW.updated IS NULL）
 EXECUTE PROCEDURE x.trg_upaft_counter_change_1（）; 
  

2。）将行标记为已更新，仅当尚未更新时：

  CREATE TRIGGER upaft_counter_change_2  - 不推迟！ 
在更新计数器之后x.tbl 
每个ROW 
 WHEN（NEW.updated IS NULL）
 EXECUTE PROCEDURE x.trg_upaft_counter_change_2（）; 
  

3。）重置标志。没有无休止的循环，因为触发条件。

  CREATE CONSTRAINT TRIGGER upaft_counter_change_3 
更新后的更新ON x.tbl 
 DEFERRABLE INITIALLY DEFERRED 
 FOR EACH ROW 
 WHEN（NEW.updated） -  
 EXECUTE PROCEDURE x.trg_upaft_counter_change_3（）; 
  

测试

运行更新& SELECT 分开查看延期效果。如果一起执行（在一个事务中），SELECT将显示新的 tbl.counter ，而旧的 tbl2.trig_exec_count 。

 更新x.tbl SET counter = counter + 1; 
 
 SELECT * FROM x.tbl; 
  

现在，多次更新计数器（在一个事务中）。有效载荷只能执行一次。 Voilá！

 更新x.tbl SET counter = counter + 1; 
更新x.tbl SET counter = counter + 1; 
更新x.tbl SET counter = counter + 1; 
更新x.tbl SET counter = counter + 1; 
更新x.tbl SET counter = counter + 1; 
 
 SELECT * FROM x.tbl; 
  

I have a deferred AFTER UPDATE trigger on a table, set to fire when a certain column is updated. It's an integer type I'm using as a counter.

I'm not 100% certain but it looks like if I increment that particular column 100 times during a transaction, the trigger is queued up and executed 100 times at the end of the transaction.

I would like the trigger to only be scheduled once per row no matter how many times I've incremented that column.

Can I do that somehow? Alternatively if triggered triggers must queue up regardless if they are duplicates, can I clear this queue during the first run of the trigger?

Version of Postgres is 9.1. Here's what I got:

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;$$;

解决方案

This is a tricky problem. But it can be done with per-column triggers and conditional trigger execution introduced in PostgreSQL 9.0.

You need an "updated" flag per row for this solution. Use a boolean column in the same table for simplicity. But it could be in another table or even a temporary table per transaction.

The expensive payload is executed once per row where the counter is updated (once or multiple time).

This should also perform well, because ...

• ... it avoids multiple calls of triggers at the root (scales well)
• ... does not change additional rows (minimize table bloat)
• ... does not need expensive exception handling.

Consider the following

Demo

Tested in PostgreSQL 9.1 with a separate schema x as test environment.

Tables and dummy rows

-- 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 two rows to demonstrate it works with multiple rows:

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

Trigger functions and Triggers

1.) Execute expensive payload

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.) Flag row as updated.

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.) Reset "updated" flag.

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;

Trigger names are relevant! Called for the same event they are executed in alphabetical order.

1.) Payload, only if not "updated" yet:

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.) Flag row as updated, only if not "updated" yet:

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.) Reset Flag. No endless loop because of trigger condition.

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

Test

Run UPDATE & SELECT separately to see the deferred effect. If executed together (in one transaction) the SELECT will show the new tbl.counter but the old tbl2.trig_exec_count.

UPDATE x.tbl SET counter = counter + 1;

SELECT * FROM x.tbl;

Now, update the counter multiple times (in one transaction). The payload will only be executed once. Voilá!

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;

这篇关于在PostgreSQL中每行执行一次延迟触发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！