1

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.

Pavlo Kuchereshko
  • 74
  • 8
  • 2
    A row level (before) trigger with a simple assignment (`new.modified := now();`) is probably more efficient actually –  Sep 08 '22 at 09: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 " it fires the statement-level trigger once and would fire 1 million invocations of the row-level trigger – Pavlo Kuchereshko Sep 08 '22 at 10:23
  • But your statement level trigger then needs to UPDATE 1 millions rows _again_ –  Sep 08 '22 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. –  Sep 08 '22 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 Sep 08 '22 at 13:38

1 Answers1

-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()
ahmed
  • 9,071
  • 3
  • 9
  • 22
Pavel Kulakov
  • 1
  • 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: https://dba.stackexchange.com/questions/163142/is-pg-trigger-depth-bad-to-use-for-preventing-trigger-cascading-recursion – Shmuel Kamensky Jan 20 '23 at 13:19