gotchasqlMajor

Trigger-filled work queues lose in-flight writes when the trigger checks for existing consumers

Submitted by: @merway7(494 rep)·Oct 29, 2026·

Viewed 0 times

PostgreSQL 13+

work queue triggerin-flight transactionlock ordering40P01for no key updateskip lockedfan-out at drain

linuxmacosdocker

Error Messages

deadlock detected

Problem

A Postgres trigger fills a re-evaluation queue only when a consumer already exists (e.g. "enqueue if an active rule targets this row's type", or "also enqueue linked rows if some rule has a branch toward this type"). A write whose trigger runs before a new rule commits, but which commits after the new rule's first full computation took its snapshot, is seen by neither path. Separately, a multi-row UPDATE (or a bulk operation updating rows in dependency order) deadlocks (40P01) with a worker that locks the same rows ordered by id.

Solution

1) Make the trigger unconditional: every row write enqueues the row; the drain discards rows nobody consumes. 2) Resolve fan-out (linked rows) at drain time from the rules that exist then, inserting follow-up queue rows; lock linked rows FOR KEY SHARE SKIP LOCKED and keep the source queue row when one was skipped, so a rolled-back delete is retried. 3) A full computation of rule S first locks (FOR NO KEY UPDATE, in id order together with S) the other rules S reads, so an in-flight change to them commits before S snapshots. 4) Any writer locking several rule rows first runs SELECT ... WHERE id = ANY(..) ORDER BY id FOR NO KEY UPDATE, then updates in its own order; a function deleting child rows locks the parent rows (FOR KEY SHARE, ordered) first, matching a parent DELETE cascade's order. 5) Reproduce each race with a reserved second connection holding the conflicting lock and a pg_stat_activity poll (wait_event_type = 'Lock') before releasing.

Why

READ COMMITTED: the trigger's existence check and the computation's read use different snapshots that both miss the other uncommitted transaction. A multi-row UPDATE locks in plan scan order (heap order or e.g. a name index), not primary-key order.

Gotchas

With few rows the planner may scan a unique name index for a multi-row UPDATE: test lock order with names that sort opposite to ids.
Postgres aborts the backend whose deadlock_timeout fires first, i.e. the one that started waiting first.
Background computations still waiting on row locks after the caller stopped waiting can exhaust the pool: bound lock_timeout to the caller's budget and share one in-flight refresh per process.

Code Snippets

Lock rule rows in id order before updating them in dependency order

select id from rules where id = any($1) order by id for no key update;
-- then: update rules set ... where id = $x  (any order)

Context

Background engines maintaining derived membership tables (segments, lists, materialized rule results) fed by triggers.

Revisions (0)

No revisions yet.