Duplicate key violation when trigger populates pgtt table

[abb9979]

I am testing pgtt with some (unusual) scenarios that are supported for Oracle GTTs (and regular PostgreSQL TTs).

One such scenario is to have a trigger on a permanent table replicate its data to a GTT with an INSERT .. SELECT .. WHERE NOT EXISTS (...) statement in the trigger.

The test case succeeds when the target table is a regular PostgreSQL temporary table, but raises a key violation when the target table is a pgtt table.

Test case:

CREATE TABLE t ( id int primary key, data text );

CREATE /*GLOBAL*/ TEMPORARY TABLE gtt ( id int primary key, data text );

CREATE OR REPLACE FUNCTION t_trigger() RETURNS trigger AS $$
BEGIN
  INSERT INTO gtt (id, data)
  SELECT t.*
  FROM   t
  WHERE NOT EXISTS ( SELECT NULL
                     FROM   gtt
                     WHERE  t.id = gtt.id );
  RETURN new;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER t_trigger AFTER INSERT ON t
EXECUTE PROCEDURE t_trigger();

CREATE OR REPLACE PROCEDURE test()
  LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO t (id, data) VALUES (1, 'Row 1');
  INSERT INTO t (id, data) VALUES (2, 'Row 2');
END; $$;

BEGIN;
CALL test();
SELECT * FROM gtt;
ROLLBACK;

If I run the test case with pgtt enabled:

postgres=# BEGIN;
BEGIN

postgres=*# CALL test();
ERROR:  duplicate key value violates unique constraint "gtt_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  SQL statement "INSERT INTO gtt (id, data)
  SELECT t.*
  FROM   t
  WHERE NOT EXISTS ( SELECT NULL
                     FROM   gtt
                     WHERE  t.id = gtt.id )"
PL/pgSQL function pgtt_schema.t_trigger() line 3 at SQL statement
SQL statement "INSERT INTO t (id, data) VALUES (2, 'Row 2')"
PL/pgSQL function test() line 4 at SQL statement

If I run the test case with pgtt.enabled set to off:

postgres=# BEGIN;
BEGIN

postgres=*# CALL test();
CALL

postgres=*# SELECT * FROM gtt;
 id | data
----+-------
  1 | Row 1
  2 | Row 2
(2 rows)

postgres=*# ROLLBACK;
ROLLBACK

Version info:

• PostgreSQL 15.8
• pgtt 4.0.0