PostgreSQL triggers and temporary table

Question

I have created an before update and after update trigger on a postgresql db table.

There is a requirement to preserve historical record and at the same time create a new record for the said data. Old record is to be marked as archived.

I was planning on using temporary table to keep track of the NEW values and reset the NEW values such that it is marked as archived.

In my after update trigger I would read the data from the temporary table, and create a brand new active record.

My problem is temporary table created in before update trigger is not visible to after update trigger. Moreover I cannot even pass on any argument (of type record) to the after update trigger as it is not allowed.

I have already achieved the desired result in Oracle db, using Global Temporary table, but having a tough time in PostgreSQL.

Here is the sample code for before update trigger function:

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

NOW the after UPDATE trigger function:

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;

Is there a way that after update trigger can access the temporary table created in before update trigger function?

I cannot have a permanent table hold he values, as trigger can be fired by many users updating the data in the table.

Answer 1

The temporary table should be visible as @Pavel explains, but that's not the main issue here.

Your approach might make sense in Oracle with a global temporary table. But the posted Postgres code does not.

The trigger is fired for each row. You would (drop and) create a temp table for every row, and call another trigger, just to do what you could easily do in one trigger directly.

Instead, to keep the old row and set it to archived, plus INSERT a copy of the NEW row:

Demo table:

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

Trigger func:

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;

Trigger:

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

Test:

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

Works.

Answer 2

There is no problem with access to temporary table from triggers, and following code working without issue (on 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

So I am sure, so your problem will not be in access to temporary tables. It works well. There can be a issue on some 8.2, 8.3 and older with invalid plans due reference on dropped objects. Isn't it your problem?

I can say, so your design is wrong - there is not any reason, why you have to use a temp table. Same job you can do in after trigger. Any operations inside triggers should be fast, pretty fast. Dropping or creating temporary table is not fast operation.

If you have a older PostgreSQL release, you have not to drop temp table every. You should to delete content only. See a article http://postgres.cz/wiki/Automatic_execution_plan_caching_in_PL/pgSQL