UPDATE rechecks its WHERE after the wait

Two sessions under PostgreSQL Read Committed, one account with a balance of 15, and both sessions run the same statement.

UPDATE accounts
SET balance = balance - 10
WHERE id = 1 AND balance >= 10;

Session A updates the row and has not committed. Session B finds the row, sees it is being updated, and waits. A commits. B does not apply its change to the version it originally found. It takes the updated version, evaluates the WHERE clause again, sees a balance of 5, and updates nothing. The balance never goes negative, and B's row count of zero tells the caller the withdrawal did not happen.

The docs describe this recheck for UPDATE, DELETE and SELECT FOR UPDATE, and they also say where it stops helping. It covers the target row only. A condition that reads other rows through a subquery or a join is not reevaluated against their newer versions, so such a statement can act on a mix of states. The docs' own example is a DELETE whose WHERE matches the value a concurrent UPDATE is changing, and it ends with nothing deleted although a matching row exists before and after.

So a single-row guard in the WHERE holds under Read Committed. A guard that looks at a second table is where I'd reach for a row lock or a stronger isolation level.