How to avoid launching a trigger for every row of a prepared statement?

Question

I have a FOR EACH STATEMENT trigger on table1:

CREATE TRIGGER trg_table1 AFTER INSERT OR DELETE OR UPDATE OF field1 ON table1
FOR EACH STATEMENT EXECUTE PROCEDURE my_trigger_procedure();

I have to update rows of that table as follows:

UPDATE table1 SET field1='abc' WHERE field5=1;
UPDATE table1 SET field1='def' WHERE field5=2;
UPDATE table1 SET field1='ghi' WHERE field5=3;
...

Names and values are simplified for clarity.

Each UPDATE is considered a single statement, so the trigger is fired for every one of those rows.

To avoid that, I made a prepared statement:

PREPARE my_prep_stmnt AS
UPDATE table1
SET
field1=$1
WHERE field5=$2
;

EXECUTE my_prep_stmnt ('abc',1);
EXECUTE my_prep_stmnt ('def',2);
EXECUTE my_prep_stmnt ('ghi',3);

I was expecting the trigger to be fired only after the prepared statement was done, but no, the trigger is fired for every EXECUTE row.

The problem is that the trigger procedure takes time to execute. Any idea to go around this ?

perhaps this is what you're looking for: http://stackoverflow.com/questions/3942258/how-do-i-temporarily-disable-triggers-in-postgresql — Andreas, May 27 '14 at 19:26
Thanks Andreas, but I considered the option of disabling triggers as a last resort only. — Sébastien Clément, May 27 '14 at 19:40

score 2 · Accepted Answer · edited May 23 '17 at 11:57

Provide multiple rows in a subquery with a VALUES expression to make it a single UPDATE statement:

UPDATE table1 t
SET    field1 = u.field1
FROM (
   VALUES
    ('abc'::text, 1)   -- cast string literal in row 1 to make type unambiguous
   ,('def', 2)
   ,('ghi', 3)
   ) u (field1, filed5)
WHERE  t.field5 = u.field5;

You need to cast to a matching type, since the VALUES expressions stands alone and cannot derive the column type like in the UPDATE. Unquoted numbers with just digits default to integer (bigint / numeric if too big).

You can still use a prepared statement (with many parameters). For a large number of rows rather switch to bulk loading to a temporary staging table and update from there:
How to update selected rows with values from a CSV file in Postgres?

More options:
How to UPDATE table from csv file?

Thanks Erwin, very useful method, I applied it. – Sébastien Clément Jun 03 '14 at 15:41 — Sébastien Clément, Jun 03 '14 at 15:41

How to avoid launching a trigger for every row of a prepared statement?

1 Answers1