In PostgreSQL, how do you maintain a per-warehouse stock summary with a FOR EACH STATEMENT trigger using REFERENCING NEW TABLE, and what restrictions apply?
answer
- set-based, once per statement
- AFTER only, plain tables
- one event per trigger
- no UPDATE OF column list
- parent sees all partitions
basics
~20 sDeclare AFTER INSERT ON stock_movement REFERENCING NEW TABLE AS new_moves FOR EACH STATEMENT and aggregate new_moves into the summary with one upsert. Transition tables need an AFTER trigger on a plain table, exactly one event and no column list.
solid answer
~50 sA PostgreSQL AFTER trigger can declare `REFERENCING NEW TABLE AS new_moves` (and `OLD TABLE` for UPDATE or DELETE), and the trigger function then queries `new_moves` like a read-only table. For a stock summary you write `AFTER INSERT ON stock_movement REFERENCING NEW TABLE AS new_moves FOR EACH STATEMENT`, and the function runs one `INSERT ... SELECT warehouse_id, sku, sum(qty_delta) ... GROUP BY ... ON CONFLICT DO UPDATE` per statement instead of one upsert per row. The restrictions: AFTER triggers only; plain tables, not views or foreign tables; exactly one event per trigger, so INSERT, UPDATE and DELETE need three triggers; no `UPDATE OF` column list; not a constraint trigger; no TRUNCATE. On a partitioned parent the statement trigger fires once and sees rows from every partition, while row triggers with transition tables cannot be defined on partitions.
code
sql · 25 linesCREATE TABLE stock_summary (
warehouse_id integer NOT NULL,
sku text NOT NULL,
qty bigint NOT NULL,
PRIMARY KEY (warehouse_id, sku)
);
CREATE FUNCTION stock_summary_add() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO stock_summary AS s (warehouse_id, sku, qty)
SELECT warehouse_id, sku, sum(qty_delta)
FROM new_moves
GROUP BY warehouse_id, sku
ON CONFLICT (warehouse_id, sku)
DO UPDATE SET qty = s.qty + EXCLUDED.qty;
RETURN NULL;
END;
$$;
CREATE TRIGGER stock_movement_summarise
AFTER INSERT ON stock_movement
REFERENCING NEW TABLE AS new_moves
FOR EACH STATEMENT
EXECUTE FUNCTION stock_summary_add();go deeper
Remember that REFERENCING NEW TABLE gives an AFTER trigger a table of all rows the statement inserted or updated, queryable with ordinary SQL.
Write the statement-level trigger and its aggregating upsert, and explain why it is cheaper than one upsert per row for batch inserts.
Know the restrictions (AFTER only, plain tables, one event, no column list, partition rules) and the operational traps: hot summary rows, ON CONFLICT and MERGE splitting, subscribers not firing statement triggers, reconciliation.
Weigh a trigger-maintained summary against a materialized view, an application-side aggregate or a stream processor, considering contention, rebuild cost and how many write paths must stay correct.
## From per-row to per-statement A warehouse ledger such as `stock_movement` receives batches: a goods-receipt import inserts thousands of rows in one statement. A row-level AFTER trigger that upserts `stock_summary` for every row does thousands of small upserts, often against the same few summary rows. PostgreSQL's **transition tables** let one statement-level trigger see the whole set of affected rows and apply them as one aggregated, set-based change. `REFERENCING NEW TABLE AS new_moves` asks PostgreSQL to collect the after-images of every row the statement inserted or updated; `REFERENCING OLD TABLE AS old_moves` collects the before-images of rows updated or deleted. Inside the trigger function, written in PL/pgSQL or another language with SPI access, those names behave like read-only temporary tables that ordinary SQL can query. ## Writing the trigger 1. Give the summary a key the upsert can target, here a primary key on `(warehouse_id, sku)`. 2. Write a function `RETURNS trigger` that aggregates the transition table and upserts the totals, ending with `RETURN NULL`, because a statement trigger's return value is ignored. 3. Create `AFTER INSERT ON stock_movement REFERENCING NEW TABLE AS new_moves FOR EACH STATEMENT EXECUTE FUNCTION ...`. 4. If the ledger also allows UPDATE or DELETE, create separate triggers for those events, with `OLD TABLE` and `NEW TABLE` for UPDATE and `OLD TABLE` for DELETE. ## Restrictions PostgreSQL enforces | Restriction | Consequence | |---|---| | Only `AFTER` triggers | A BEFORE statement trigger cannot see or adjust the batch | | Only plain tables | Views and foreign tables cannot have transition tables | | Exactly one event | `AFTER INSERT OR UPDATE` with `REFERENCING` is rejected; write one trigger per event | | No `UPDATE OF col` list | You cannot capture only rows whose listed columns were targeted | | Not a constraint trigger, not TRUNCATE | Transition sets are never deferred to commit and never cover TRUNCATE | | `OLD TABLE` only for UPDATE/DELETE, `NEW TABLE` only for INSERT/UPDATE | Each name may appear once | | No ROW trigger with transition tables on a partition or inheritance child | Put such triggers on the parent, at statement level | The one-event rule is enforced in the server source with the error that transition tables cannot be specified for triggers with more than one event; the reason given is that `INSERT ... ON CONFLICT` can fire both kinds, and a combined trigger would have no clear row set. ## Edge cases interviewers ask about - **Zero rows.** A statement trigger fires even when the statement changes nothing; `new_moves` is then empty and a well-written aggregate does nothing. - **Partitioned tables.** When a statement targets a partitioned `stock_movement`, only the parent's statement-level triggers fire, once, and the transition table holds rows from every affected partition. The partitions' own statement triggers do not fire. - **ON CONFLICT DO UPDATE.** The statement can fire both INSERT and UPDATE triggers. The INSERT trigger's transition table holds only inserted rows and the UPDATE trigger's only updated rows. - **MERGE.** There are no MERGE triggers; the INSERT, UPDATE and DELETE triggers fire, each with its own transition table. - **Foreign-key cascades.** Referential actions run as part of the original command, and their changes land in the referencing table's transition tables. ## Operating it - **Hot summary rows.** Every concurrent statement that touches the same warehouse and SKU locks the same summary row until commit, so the summary can become the contention point the ledger was designed to avoid. Aggregating per statement shrinks that window; it does not remove it. - **Reconciliation.** Keep a query that rebuilds the summary from the ledger, run it after bulk loads done with triggers disabled, and compare totals periodically. - **Replicas.** A logical-replication subscriber's apply process runs with `session_replication_role = replica` and fires only row triggers, never statement triggers, so a summary maintained this way is not rebuilt on a subscriber. - **Row triggers can use them too.** An `AFTER ... FOR EACH ROW` trigger may also declare transition tables to see the whole statement's changes alongside the current row, but that combination still runs once per row.
- The ledger also allows UPDATE and DELETE. Can one trigger with OLD TABLE and NEW TABLE cover all three events?No. PostgreSQL rejects transition tables on a trigger with more than one event. Create three AFTER statement triggers: INSERT with `NEW TABLE`, UPDATE with both `OLD TABLE` and `NEW TABLE` (subtract old, add new), and DELETE with `OLD TABLE`. They may call separate functions or one function that branches on `TG_OP`.
- stock_movement is partitioned by month. Where should the summary trigger live?On the partitioned parent, at statement level. A statement aimed at the parent fires only the parent's statement triggers, once, and the transition table contains rows from every affected partition. Statement triggers on the partitions would not fire, and row triggers with transition tables cannot be defined on partitions at all.
- A reporting database receives stock_movement through logical replication. Is stock_summary maintained there by this trigger?No. The apply process runs with `session_replication_role = replica`, so ordinarily enabled triggers do not fire, and it fires only row triggers, never statement triggers. Either replicate `stock_summary` itself or maintain it there with a row trigger set to `ENABLE ALWAYS` or `ENABLE REPLICA`.
- Transition tables work in BEFORE triggers, so you can adjust the batch first.
- One AFTER INSERT OR UPDATE OR DELETE trigger can declare both transition tables.
- A statement trigger on a partitioned parent fires once for each affected partition.
- UPDATE OF qty_delta can be combined with REFERENCING to capture changed rows only.
- A statement trigger with transition tables is skipped when no rows change.