Row Locks With Joins Can Produce Surprising Results in PostgreSQL
Summary
The article reveals a PostgreSQL concurrency edge case where locking a row during a join can cause a concurrent transaction to return nothing or partial data after the lock is released. It explains EvalPlanQual re-evaluation and offers practical fixes, including locking the operation via a subquery/CTE, considering isolation levels, and splitting queries to avoid the issue.