PostgreSQL触发器和临时表

Question

我已经在PostgreSQL数据库表上创建了一个before update和after update触发器。

有一个要求是保留历史记录，同时为所述数据创建一个新记录。旧记录将被标记为已归档。

我计划使用临时表来跟踪NEW值并重置NEW值，使其标记为已归档。

在我的after update触发器中，我将从临时表中读取数据，并创建一个全新的活动记录。

我的问题是，在before update触发器中创建的临时表对after update触发器不可见。此外，我甚至无法将任何参数（类型为record）传递给after update触发器，因为这是不允许的。

我已经在Oracle数据库中使用Global Temporary table实现了所需的结果，但在PostgreSQL中遇到了困难。

以下是before update触发器函数的示例代码：

CREATE OR REPLACE FUNCTION trigger_fct_trig_trk_beforeupdate()
RETURNS trigger AS
$BODY$
DECLARE

    some variable declarations;
    BEGIN

    Drop table  IF EXISTS track_tmp_test;

    CREATE TEMPORARY TABLE track_tmp_test(
    ...
    );

    Insert into track_tmp_test (........)
    values(NEW., NEW..., NEW.., NEW...);

    NEW... := OLD...;
    NEW... := OLD.... ;
    NEW... := OLD...;
    Mark the NEW.status : = 'archived';

RETURN NEW;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE TRIGGER trig_trk_test_beforeupdate
BEFORE UPDATE ON test
FOR EACH ROW EXECUTE PROCEDURE trigger_fct_trig_trk_beforeupdate() ;

现在是UPDATE触发器的后续处理函数：

CREATE OR REPLACE FUNCTION trigger_fct_trg_trk_afterupdate()
  RETURNS trigger AS
$BODY$
DECLARE

    some variables;

-- insert into  original table the data from temporary that was inserted in before update trigger 
    INSERT into TEST (....)
    select ....
    from track_tmp_test ;

    -- delete data from temporary table after insert
    delete from track_tmp_test ;

 EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
RETURN NEW;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

在更新触发器中，有没有办法可以访问在更新前触发器函数中创建的临时表？

我不能使用永久表来保存值，因为触发器可能会由许多用户更新表中的数据。

- Anurag

---

我从未亲自操作过，但根据文档，“临时表会在会话结束时自动删除，或者在当前事务结束时（可选）”。这可能会让你明白为什么无法在另一个事务中访问该表。 - DrColossos

你的AFTER触发器丢失了，请将其添加到问题中。我打算采用完全不同的方法，但我需要这些信息。请澄清：您想要复制新行还是旧行 - 到相同表还是到具有相同结构的不同表？并且您是否想将新行标记为“已归档”？这很奇怪，但这就是您的评论所说的。 - Erwin Brandstetter

另外，请提供您使用的Postgres版本（像往常一样）。 - Erwin Brandstetter

Answer 1

在PostgreSQL 9.4中，从触发器访问临时表没有问题，以下代码可以正常运行：

CREATE OR REPLACE FUNCTION public.f1()
RETURNS trigger
LANGUAGE plpgsql
AS $function$ 
begin
  drop table if exists bubu; 
  create temp table bubu(a int); 
  insert into bubu values(10); 
  return new; 
end
$function$

CREATE OR REPLACE FUNCTION public.f2()
RETURNS trigger
LANGUAGE plpgsql
AS $function$ 
declare r record;
begin 
  for r in select * from bubu 
  loop 
    raise notice '%', r; 
  end loop; 
  return null; 
end 
$function$

create trigger xx
  before insert on omega
  for each row execute procedure f1();

create trigger yy
  after insert on omega
  for each row execute procedure f2();

postgres=# insert into omega values(333);
NOTICE:  (10)
INSERT 0 1

我相信，你的问题不在于访问临时表。这个功能很好用。但是，在一些8.2，8.3以及更老的版本中，由于引用了已删除对象，可能会出现无效计划的问题。这难道不是你的问题吗？

我可以说，你的设计是错误的——没有任何理由使用临时表。同样的工作可以在触发器中完成。触发器内的任何操作都应该很快，非常快。删除或创建临时表不是快速操作。

如果你使用较旧的PostgreSQL版本，则不需要每次都删除临时表。你只需要删除内容即可。请参阅文章http://postgres.cz/wiki/Automatic_execution_plan_caching_in_PL/pgSQL

- Pavel Stehule

Answer 2

正如@Pavel解释的那样，临时表应该是可见的，但这不是主要问题。

在Oracle中使用全局临时表，你的方法可能是有效的。但发帖的Postgres代码不行。

触发器会为每一行触发。你将为每一行（删除并）创建一个临时表，并调用另一个触发器，只是为了完成你可以直接在一个触发器中轻松完成的操作。

相反，为了保留旧行并将其设置为archived，同时插入新行的副本：

Demo table:

CREATE TEMP TABLE test (id int, txt text, archived bool DEFAULT FALSE);

触发函数：

CREATE OR REPLACE FUNCTION trg_test_beforeupdate()
  RETURNS trigger AS
$func$
BEGIN
   INSERT INTO test SELECT (NEW).*;  -- insert a copy of the NEW row

   SELECT (OLD).* INTO NEW;      -- revert row to previous state

   NEW.archived = TRUE;          -- just set it to "archived"

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

触发器：

CREATE TRIGGER beforeupdate
BEFORE UPDATE ON test
FOR EACH ROW EXECUTE PROCEDURE trg_test_beforeupdate();

测试：

INSERT INTO test VALUES (1, 'foo'), (2, 'bar');
UPDATE test SET txt = 'baz' WHERE id = 1;
SELECT * FROM test;

工作正常。

- Erwin Brandstetter

---

不幸的是，列出的解决方案都无法在我想要使用临时表的情况下起作用。我有一个名为A的表，在其中维护现有行的历史存档记录。如果修改了某些关键字段，则会为给定记录创建新行。 - Anurag