A batch loader that writes N rows per transaction, each row inside a BEGIN ... EXCEPTION block so one bad row does not abort the batch, is usually capped at a fixed number of rows (e.g. 50) to stay under PostgreSQL's 64 cached subtransaction XIDs per backend. When one input line starts writing several records plus links between them, each in its own exception block, a 50-row batch can open 250+ subtransactions. Lookups by other sessions then slow down (subtrans overflow) with no error.

A queue worker (per-row: rewrite derived key rows, then upsert a status row) and a periodic full recompute (lock status row FOR UPDATE, delete all derived rows, recompute, reinsert) running on the same database deadlock because they take locks in opposite order. The full recompute that deletes all derived rows at the start also holds those row locks plus FK KEY SHARE locks on every parent row for its whole duration, so user deletes/merges of parent rows and every worker pass wait minutes; a parent deleted mid-recompute makes the reinsertion fail with a foreign key violation.

A PL/pgSQL function that merges two rows (locks them FOR UPDATE, deletes their link rows, re-inserts links to a new row, deletes the old rows) deadlocks (40P01) with a concurrent merge of a linked row or a concurrent 'unlink' (delete of one link row). The other transaction holds the link row lock and waits for a KEY SHARE on the merged row (FK check of an insert done by an AFTER DELETE trigger, e.g. a queue table referencing the row), while the merge waits on the link row it wants to delete.

A batch importer writes each row inside a plpgsql BEGIN ... EXCEPTION WHEN data_exception OR integrity_constraint_violation block so that one bad row is refused while the rest of the batch commits. A value stored in a btree-indexed side table (e.g. normalized unique values: email, domain, custom unique property) longer than about 2.7 kB raises 'index row size N exceeds btree version 4 maximum 2704' with SQLSTATE 54000 (program_limit_exceeded, class 54). It is not class 22 or 23, so it escapes the row handler, aborts the whole batch transaction, and a retrying worker eventually marks the whole import as failed.

A background job that calls a nested PL/pgSQL function thousands of times (batch of rows, each row in its own BEGIN/EXCEPTION block) on the Supabase local Postgres image failed once in ~3,000 transactions with an internal error raised by an extension hook, not by the job's own code. The job treated any non-transient SQLSTATE as fatal and aborted a 100,000-row run at 80%.

Record merge implemented in SQL fails with a unique violation (23505) as soon as the secondary record has side data. Unique values (email, domain) live in a side table rebuilt by an AFTER UPDATE trigger on the record (delete all its rows, re-insert from props). If the merge removes the old records' unique rows by hand, then moves associations or visits, other triggers (recomputed activity properties) update the OLD record, the sync trigger re-inserts its email, which is already taken by the new record, and the whole merge transaction aborts.

A background sweep locks child rows (select ... for update of child skip locked), then takes the parent row FOR SHARE. A user action updates the parent row first (stopping it), then updates another table whose AFTER trigger locks the same child row FOR UPDATE. Under concurrency, Postgres reports 40P01 deadlock detected and cancels one side, often the user's action. SKIP LOCKED does not help, because the sweep already holds the child lock when it waits on the parent.

A reader built from small `language sql` helpers (each with a FROM clause, so not inlinable) was 40x slower per row than the same logic in plpgsql: 5,000 values took 2.5 s versus 0.05 s, and 200,000 rows took 2-3 minutes in a filter. Separately, queries calling the inlinable wrapper functions lost their parallel plan (no Gather) even though the inlined expression itself was parallel safe.

A plpgsql function that turns a user-supplied epoch timestamp (string or JSON number) into timestamptz and promises to raise only check_violation (23514) can still fail with other SQLSTATEs. A guard like abs(n) >= 1e15 is not enough: negative values between about -2.1e14 and -1e15 ms come before 4713 BC, where timestamptz stops, and raise 22008 "timestamp out of range". Digit strings longer than 131072 characters make text::numeric raise 22003 "value overflows numeric format". Postgres also accepts '24:00' and ':60' in timestamp input, so pg_input_is_valid by itself is not a strict ISO 8601 check.

A business rule enforced by a BEFORE INSERT ROW trigger (e.g. 'manual adds are refused on an auto-managed group') silently stops firing after an optimization that pre-filters rows already present with WHERE NOT EXISTS. With plain INSERT ... ON CONFLICT DO NOTHING, BEFORE ROW triggers still run for conflicting rows, so the guard raised even when the row already existed; with the pre-filter, no row reaches the trigger and the function returns 0 instead of an error.