Tuesday 14 August 2018

Simulating ON COMMIT DELETE Global Temporary Tables on PostgreSQL

Vibhor Kumar did a great post on Leveraging Local Temporary Table for Global Temporary Table in EDB Postgres 9.6, including both ON COMMIT PRESERVE, and ON COMMIT DELETE options:

https://vibhorkumar.blog/2017/07/06/leveraging-local-temporary-table-for-global-temporary-table-in-edb-postgres-9-6/


The code example only covers the INSERT case, so here is an extension on that code which includes INSERT/UPDATE/DELETE:

CREATE UNLOGGED TABLE gtt_test_definition (id int);
CREATE VIEW gtt_test AS SELECT * FROM gtt_test_definition;

CREATE OR REPLACE FUNCTION gtt_test_function()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$function$
BEGIN
  /* attempt to run the DML */
  IF (TG_OP = 'INSERT') THEN
    INSERT INTO gtt_test_temp VALUES(NEW.*);
    RETURN NEW;
  ELSIF (TG_OP = 'UPDATE') THEN
    UPDATE gtt_test_temp SET id = NEW.id WHERE id = OLD.id;
    RETURN NEW;
  ELSIF (TG_OP = 'DELETE') THEN
    DELETE FROM gtt_test_temp WHERE id = OLD.id;
    RETURN NEW;
  END IF;

  /* when the temp table doesn't already exist */
  EXCEPTION WHEN undefined_table THEN
    /* create local temporary table */
    CREATE TEMP TABLE gtt_test_temp
      (LIKE gtt_test_definition INCLUDING ALL )
      INHERITS (gtt_test_definition)
      ON COMMIT DELETE ROWS;

    /* re-run the DML */
    IF (TG_OP = 'INSERT') THEN
      INSERT INTO gtt_test_temp VALUES(NEW.*);
      RETURN NEW;
    ELSIF (TG_OP = 'UPDATE') THEN
      UPDATE gtt_test_temp SET id = NEW.id WHERE id = OLD.id;
      RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
      DELETE FROM gtt_test_temp WHERE id = OLD.id;
      RETURN NEW;
    END IF;

END;
$function$;

CREATE TRIGGER gtt_test_trigger INSTEAD OF INSERT OR UPDATE OR DELETE ON gtt_test

  FOR EACH ROW EXECUTE PROCEDURE gtt_test_function();


And here is a test case, showing it working for ON COMMIT DELETE:

postgres=# \d
No relations found.

postgres=# \i test_gtt.sql
CREATE TABLE
CREATE VIEW
CREATE FUNCTION
CREATE TRIGGER

postgres=# \d
                List of relations
 Schema |        Name         | Type  |  Owner   
--------+---------------------+-------+----------
 public | gtt_test            | view  | postgres
 public | gtt_test_definition | table | postgres
(2 rows)

postgres=# select * from gtt_test;
 id 
----
(0 rows)

postgres=# insert into gtt_test values(1);
NOTICE:  merging column "id" with inherited definition
INSERT 0 1
postgres=# select * from gtt_test;
 id 
----
(0 rows)

postgres=# \d
                 List of relations
  Schema   |        Name         | Type  |  Owner   
-----------+---------------------+-------+----------
 pg_temp_2 | gtt_test_temp       | table | postgres
 public    | gtt_test            | view  | postgres
 public    | gtt_test_definition | table | postgres
(3 rows)

postgres=# begin;
BEGIN
postgres=# insert into gtt_test values(1);
INSERT 0 1
postgres=# select * from gtt_test;
 id 
----
  1
(1 row)

postgres=# insert into gtt_test values(2);
INSERT 0 1
postgres=# select * from gtt_test;
 id 
----
  1
  2
(2 rows)

postgres=# commit;
COMMIT
postgres=# select * from gtt_test;
 id 
----
(0 rows)

postgres=# begin;
BEGIN
postgres=# insert into gtt_test values(1);
INSERT 0 1
postgres=# insert into gtt_test values(2);
INSERT 0 1
postgres=# select * from gtt_test;
 id 
----
  1
  2
(2 rows)

postgres=# update gtt_test set id=100 where id=1;
UPDATE 1
postgres=# select * from gtt_test;
 id  
-----
   2
100
(2 rows)

postgres=# commit;
COMMIT
postgres=# select * from gtt_test;
 id 
----
(0 rows)

postgres=# begin;
BEGIN
postgres=# insert into gtt_test values(1);
INSERT 0 1
postgres=# insert into gtt_test values(2);
INSERT 0 1
postgres=# insert into gtt_test values(3);
INSERT 0 1
postgres=# select * from gtt_test;
 id 
----
  1
  2
  3
(3 rows)

postgres=# delete from gtt_test where id=2;
DELETE 0
postgres=# select * from gtt_test;
 id 
----
  1
  3
(2 rows)

postgres=# commit;
COMMIT
postgres=# select * from gtt_test;
 id 
----
(0 rows)

No comments:

Post a Comment

New PostgreSQL RDS versions - v10.14 , v11.9, and v12.4

It appears that new patch versions of PostgreSQL RDS are being rolled out in AWS.. so we should expect an announcement soon: REGION: us-east...