Trigger defined on defined pgtt-table is not copied on temporary instance

[casparsch]

Every scenario is executed with a new connection/session

SCENARIO 1 (regular table)

Created a regular table (within the pgtt_schema 🤷‍♂️) with a trigger just to verify everything works as expected:

CREATE TABLE IF NOT EXISTS pgtt_schema.persistant
(   id_temp serial,
    a_code character(3),
    descr text
);
-- the trigger implementation
CREATE OR REPLACE FUNCTION pgtt_schema.copy()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    new.descr := new.a_code;
    return new;
END;
$BODY$;
-- the trigger itself
CREATE OR REPLACE TRIGGER copy
    BEFORE INSERT
    ON pgtt_schema.persistant
    FOR EACH ROW
    EXECUTE FUNCTION pgtt_schema.copy();
-- An insert
INSERT INTO pgtt_schema.persistant (a_code) VALUES ('PQR');
-- and verify the output
select * from pgtt_schema.persistant;

id_temp	a_code	descr
1	PQR	PQR

So far so good.

SCENARIO 2 (postgres temp-table)

Trying with regular postgres-temp-table:
First disable the extension:
set pgtt.enabled=off;
Then do similar as above for a postgres-temp-table

DROP TABLE IF EXISTS pg_tmp;
CREATE GLOBAL TEMPORARY TABLE IF NOT EXISTS pg_tmp
(   id_temp serial,
    a_code character(3),
    descr text
)
ON COMMIT PRESERVE ROWS;
-- Reuse the trigger implementation for the create of the trigger
CREATE OR REPLACE TRIGGER copy
    BEFORE INSERT
    ON pg_tmp
    FOR EACH ROW
    EXECUTE FUNCTION pgtt_schema.copy();
-- An insert in the postgres-temp-table
INSERT INTO pg_tmp (a_code) VALUES ('PQR');
-- and verify the output
select * from pg_tmp;

id_temp	a_code	descr
1	PQR	PQR

Works as expected 👍

SCENARIO 3 (pgtt temp-table)

So now the scenario with the pgtt-temp-table:
Do not forget to re-enable the extension (and just to be sure):
set pgtt.enabled=on;
Then do similar as above for a pgtt-temp-table

DROP  TABLE IF EXISTS pgtt_schema.pg_tmp;
CREATE GLOBAL TEMPORARY TABLE pgtt_schema.pg_tmp
(   id_temp serial NOT NULL,
    a_code character(3),
    descr text
)
ON COMMIT PRESERVE ROWS
;
CREATE OR REPLACE TRIGGER copy
    BEFORE INSERT
    ON pgtt_schema.pg_tmp
    FOR EACH ROW
    EXECUTE FUNCTION pgtt_schema.copy();
INSERT INTO pgtt_schema.pg_tmp (a_code) VALUES ('PQR');
select * from pgtt_schema.pg_tmp;

Such a pitty 😨

id_temp	a_code	descr
1	PQR	[null]

After some investigation the pgtt-temp-table was found in one of the schemas: "pg_temp_..."
However the trigger was absent. Manually added it and then it worked as I expected it.

Used version 3.2.0 according the extension

[casparsch]

Following query could be helpful to determine the temp-tables not having the trigger(s).

It produces the statement which could be executed to create the requested trigger(s).

WITH
    defed AS
    (
    SELECT  gtt.*
    ,       pg_get_triggerdef(trg.oid)  AS  trgdef
    ,       trg.tgfoid::regproc         AS  proname
    FROM    pg_class                            tbl
    JOIN    pg_namespace                        nsp ON  (nsp.oid = tbl.relnamespace)
    JOIN    pg_trigger                          trg ON  (tbl.oid = trg.tgrelid)
    JOIN    pgtt_schema.pg_global_temp_tables   gtt ON  (gtt.relid = tbl.oid)
    /*
    WHERE   gtt.nspname = coalesce(param_schema_name, gtt.nspname)
    AND     gtt.relname = coalesce(param_table_name , gtt.relname)
    */
    )
SELECT  REPLACE
        (   REPLACE
            (   def.trgdef
            ,   ' ON ' || COALESCE(def.nspname || '.', '') || def.relname
            ,   ' ON ' || nsp.nspname || '.' || tbl.relname
            )
        ,   ' FUNCTION ' || def.proname
        ,   ' FUNCTION ' || def.nspname || '.' || def.proname
        )
    ||  ';'
FROM    defed           def
JOIN    pg_class        tbl ON  (def.relname = tbl.relname)
JOIN    pg_namespace    nsp ON  (   nsp.oid = tbl.relnamespace
                                AND nsp.nspname <> def.nspname
                                )
LEFT JOIN pg_trigger    trg ON  (tbl.oid = trg.tgrelid)
WHERE   true
AND     tbl.relpersistence = 't'    -- (t)emporary
AND     trg.oid IS NULL             -- trigger does not exists; not relying on: "tbl.hastriggers"
;

The commented part in the CTE can be used to filter on specific entry in the table administrating the pgtt-temp-tables.

[darold]

Right, the main reason of this is that CREATE TABLE ... LIKE ... do not support including triggers in PostgreSQL, the temporary table created on the fly use this form. I can add an ALTER statement after in case there are triggers defined on the global temporary table but I have never encountered this case so I'm not sure it's worth it. I mark this issue as needing enhancement, with some spare time I will work on it.

[casparsch]

Yes, I can imagine it has no priority.
There were 2 "triggers for me to request this enhancement:

• I know it can be constructed that way in Oracle which I have seen in the past.
• A way to resolve a functional request was to add such a trigger

I already did take a look at the pgtt-sources. I saw the use of postgres libraries and did in the end manage to compile, deploy and saw the results of that. I just added some message to get some feeling on the calls made. I do not know, yet, how to make/define a hook into the code to start step-by-step debugging. I see that as a learning point for me.
However due to my poor knowledge of C and lack of time I had to put it aside.
The "only" thing missing is the trigger itself, the trigger-function can be re-used.

I wonder what/how you were thinking about the ALTER-statement you mentioned: "I can add an ALTER statement after ..."
To my knowledge there must be a: "CREATE TRIGGER"-statement added, using the original trigger-function. On the moment the actual temp-table is initiated.
As I said just wondering because the "ALTER" confused me.

Thank in advance for making the effort if ... you find some spare time.

[darold]

Yes sorry I meant CREATE TRIGGER after the table creation.