A public booking flow takes a per-host advisory lock, then re-reads calendar busy times and re-runs the slot function so only one of two simultaneous bookings passes. Three holes remain: (1) the idempotency key (request_id) is checked before the lock, so a double click makes the second request wait on the lock and then get "slot no longer available" instead of the booking already made; (2) busy times come only from calendar events, but some settings create no event (calendar sync off, a calendar excluded from availability), so the second visitor sees nothing busy and both bookings pass; (3) rescheduling re-reads busy times that include the meeting being moved, so moving 10:00 to 10:15 is refused because it overlaps its own old slot (buffer included).

A public booking page computes free time slots from calendar busy times and lets anonymous visitors book. Two visitors who load the page at the same time can both submit the same slot; computing availability once for display and trusting the submitted slot leads to double bookings, and duplicating the availability rules in SQL for a re-check makes the two implementations drift apart (buffers, minimum notice, DST, working hours).

An at-most-once mail sender claims N queued recipients in one short transaction (state -> 'sending', lease of a few minutes, FOR UPDATE SKIP LOCKED), then sends them one by one over SMTP outside any transaction. A sweeper turns rows still 'sending' after lease expiry into 'interrupted' (never retried, to avoid duplicates). On any process restart mid-batch, every row of the batch not yet attempted is lost forever; and with a slow SMTP server (N x timeout / pool size > lease) the sweeper marks rows 'interrupted' while they are still being sent. Duplicate-only tests pass while recipients silently disappear.

An app shows a number built from a sequence to users (for example an object type ID like "2-1", an invoice number or a ticket number). Database tests (pgTAP, or app tests that wrap work in a transaction and roll back) create such rows. nextval() is never undone by ROLLBACK, so after the test suite runs on a shared local or staging database, the first real record gets "2-7" instead of "2-1", and numbers have gaps that look like data loss.

Several branches developed in parallel each reserve a timestamp range for their migrations (e.g. branch A uses 2026-11-22..., branch B uses 2026-11-24...). Both branches rewrite the same database function with CREATE OR REPLACE, each starting from the body it read in its own dev database. Merge order is B then A, but migration file names sort A before B. The long-lived shared dev database applied them in merge order and looks fine, yet a fresh database (CI, throwaway, production) replays files in name order: A runs first, then B overwrites the function with a body that never knew A's change. A's feature disappears only on fresh databases.

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.

In a CRM where every association type has an inverse row (A->B and B->A) and a labeled link implies the unlabeled one, a "primary company" label is often modeled symmetrically, so the label shows on both records. Rules like "first company becomes primary", "a contact with companies must always have exactly one primary" and "replace the primary" then break: immediate checks fail mid-transaction (remove old primary before adding the new one), and bulk operations such as record merges or imports that delete and reinsert links trip the rules in the wrong order.

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 custom report that joins a primary object to secondary objects through an association table (deals -> contacts -> companies) multiplies rows: a deal linked to two contacts appears twice, so SUM(amount) or AVG over the joined rows double counts inside a single group, and totals drift from what users expect from CRM-style report builders.

A background job reads clock_timestamp() from Postgres as a cutoff, then passes it back as a parameter (`where queued_at < ${cutoff}::timestamptz`). With a custom postgres.js type for timestamptz (serialize: x => new Date(x).toISOString()), the parameter is serialized through a JS Date, i.e. to the millisecond. Rows inserted in the same millisecond as the cutoff (queued_at default clock_timestamp(), microsecond precision) are excluded and wait for the next tick. Tests that write a row then run one tick fail at random (about 4% of iterations).