Postgres statement-level trigger ERROR: transition tables cannot be specified for triggers with column lists

Question

I have a table my_table with columns id (int), value (varchar), and modified (timestamp). I want to update the timestamp by the trigger each time I set a new value. I'm changing thousands of rows in one query so I do not want to use the row-level trigger (aka FOR EACH ROW) and modify the whole updated set in one invocation and not the thousands for each row. This is the trigger I'm trying to create:

CREATE TRIGGER my_trigger
AFTER UPDATE OF value
ON my_schema.my_table
REFERENCING NEW TABLE AS updated OLD TABLE AS prev
FOR EACH STATEMENT
EXECUTE PROCEDURE my_schema.my_trigger_function()

I'm getting an error during the creation:

[0A000] ERROR: transition tables cannot be specified for triggers with column lists

How can I access the updated data set if I remove the "REFERENCING NEW TABLE AS updated OLD TABLE AS prev" and won't be able to query and join on updated or prev transition tables? If I remove the "OF value" part I'd get the recursive trigger invocation since the trigger fires itself again by changing the same table but a different column. So the best solution I've come up with it's to eliminate the second recursive invocation by the IF condition:

CREATE TRIGGER my_trigger
AFTER UPDATE
ON my_schema.my_table
REFERENCING NEW TABLE AS updated OLD TABLE AS prev
FOR EACH STATEMENT
EXECUTE PROCEDURE my_schema.my_trigger_function()

CREATE OR REPLACE FUNCTION my_schema.my_trigger_function()
    RETURNS TRIGGER
    LANGUAGE PLPGSQL
AS
$$
BEGIN

    IF EXISTS(SELECT 1
               FROM updated
                        INNER JOIN prev ON updated.modified = prev.modified) THEN
        UPDATE my_schema.my_table
        SET modified = NOW()
        WHERE id IN (SELECT id FROM updated);
    END IF;

    RETURN NULL;
END;
$$;

If I knew how to access all updated rows with "AFTER UPDATE OF value" without updated and prev I'd make it better, that's why I'm here.

A row level (before) trigger with a simple assignment (new.modified := now();) is probably more efficient actually — user330315
– user330315, Commented Sep 8, 2022 at 9:47
@a_horse_with_no_name can you explain deeply why is it more efficient? if I update a million rows by one statement "update my table set value = 'new value' where <some condition>" it fires the statement-level trigger once and would fire 1 million invocations of the row-level trigger — Pavlo Kuchereshko
– Pavlo Kuchereshko, Commented Sep 8, 2022 at 10:23
But your statement level trigger then needs to UPDATE 1 millions rows again — user330315
– user330315, Commented Sep 8, 2022 at 10:27
But the error is pretty obvious, isn't it? You can't use a column list (OF value) in the trigger definition when using a statement level trigger. Remove that option, and your trigger should work. — user330315
– user330315, Commented Sep 8, 2022 at 10:34
so, as I understand, the row-level trigger works like the filter chain/interceptor before or after the actual modification of each row and a million invocations of it along with the update operation still being one million update operations and it is more efficient than the 2 million simple update operations, yes? — Pavlo Kuchereshko
– Pavlo Kuchereshko, Commented Sep 8, 2022 at 13:38

ahmed · Accepted Answer · 2022-12-18 01:03:59Z

-1

You could avoid recursive invocation of your trigger by adding WHEN (PG_TRIGGER_DEPTH() = 0) to the declaration:

CREATE TRIGGER my_trigger
AFTER UPDATE ON my_schema.my_table
REFERENCING NEW TABLE AS updated OLD TABLE AS prev
FOR EACH STATEMENT
WHEN (PG_TRIGGER_DEPTH() = 0)
EXECUTE PROCEDURE my_schema.my_trigger_function()

edited Dec 18, 2022 at 1:03

ahmed

9,2013 gold badges11 silver badges23 bronze badges

answered Dec 14, 2022 at 9:25

Pavel Kulakov

1

Sign up to request clarification or add additional context in comments.

1 Comment

Shmuel Kamensky Over a year ago

You shouldn't use PG_TRIGGER_DEPTH to prevent recursion since it measures all trigger depth, not the depth of the trigger the function is called from. See: dba.stackexchange.com/questions/163142/…

Collectives™ on Stack Overflow

Postgres statement-level trigger ERROR: transition tables cannot be specified for triggers with column lists

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related