How often will a FOR EACH STATEMENT trigger execute if the operation is caused by an FK constraint with UPDATE CASCADE?

I understand that a trigger on table t defined with FOR EACH STATEMENT will run once when I execute an UPDATE t ....

Now, when t is defined with FOREIGN KEY ... REFERENCES a ... ON UPDATE CASCADE, and I update N rows in a, will that cause the trigger to be called once, or N times?

Put differently, are changes to a table cascaded by an FK constraint more like a single UPDATE, or more like a series of UPDATEs?

Foreign key constraints are currently implemented with special internal triggers. All of them are run FOR EACH ROW.

Note that these are implementation details that can change, so don't rely on it. But the basics have not changed over the last couple of major versions, so major changes are unlikely.

I ran a quick test with a simple FK constraint from tbl to tbltype. A simple FK is implemented with four simple internal triggers FOR EACH ROW in my test on pg 9.4.

Here is a quick rundown on how to investigate:

SELECT oid  -- 74791
FROM   pg_constraint
WHERE  conrelid = 'tbl'::regclass
AND    contype = 'f';

SELECT objid, classid::regclass  -- 74792,74793,74794,74795 / 'pg_trigger'
FROM   pg_depend
WHERE  refobjid = 74791
AND   deptype = 'i'

SELECT tgrelid::regclass, tgname, tgfoid, tgtype FROM pg_trigger
WHERE  oid IN (74792,74793,74794,74795) ORDER BY tgfoid;

'tbl'    ;'RI_ConstraintTrigger_c_74794';1644;5
'tbl'    ;'RI_ConstraintTrigger_c_74795';1645;17
'tbltype';'RI_ConstraintTrigger_a_74792';1654;9
'tbltype';'RI_ConstraintTrigger_a_74793';1655;17

SELECT oid, proname FROM pg_proc
WHERE oid IN (1654,1655,1644,1645);

1644;'RI_FKey_check_ins'
1645;'RI_FKey_check_upd'
1654;'RI_FKey_noaction_del'
1655;'RI_FKey_noaction_upd'

Two internal "noaction" triggers on tbltype.

Two internal "check" triggers on tbl.

All of them are run FOR EACH ROW, as indicated by odd numbers in tgtype.

The 2 bytes of the Postgres tgtype smallint represent an int16 in C source code where the least significant bit encodes TRIGGER_TYPE_ROW. Detailed explanation here:

• Meanings of bits in trigger type field (tgtype) of Postgres pg_trigger

You can easily test this with a pair of identical triggers where you only change FOR ROW / STATEMENT ...

StackExchange Database Administrators Q#102463, answer score: 7