2026-09-26 · 6 min read · Rahul Chitturi
- sql
- postgresql
- concurrency
PostgreSQL MERGE evaluates source rows against a target and chooses the first qualifying action for each candidate change row. It can express synchronized inserts, updates, and deletes, but it does not turn an ambiguous source or weak uniqueness model into an atomic business operation.
Failures often appear only under load when another transaction changes match visibility, multiple source rows target the same row, or triggers transform values after the selected action.
Capture the exact statement contract
Record the PostgreSQL version, isolation level, source query, join condition, ordered WHEN clauses, target constraints, triggers, row-level security policies, privileges, and command result. Format the deployed SQL rather than a simplified reconstruction.
- Make source-row uniqueness explicit.
- Keep target-only filters out of accidental join semantics.
- Order WHEN clauses from specific to general.
- Do not infer per-action counts from a generic command tag.
Reproduce competing changes
Run two controlled transactions against a small synthetic table and pause them at known points. Observe locks, snapshots, unique violations, serialization failures, trigger activity, and final rows rather than assuming MERGE behaves like a vendor-specific upsert.
Retries must replay the whole transaction only when the application operation is safe to repeat. External side effects should use an idempotent outbox or another commit-aware boundary.
Test every action path
Use Flashman's SQL formatter for the statement and diagnostics, JSON formatter for source fixtures, diff for before-and-after rows, and timestamp converter for transaction timelines.
Cover matched and unmatched rows, multiple qualifying clauses, duplicate source keys, null join values, concurrent insert and delete, triggers, generated columns, partitions, RLS, permissions, deadlocks, retries, replicas, and rollback.