Inserting State Transitions in Postgres
Summary
The article discusses replacing a users.status column with an append-only user_statuses table to preserve history, and analyzes the race condition that occurs when two transactions concurrently update a user's status. It demonstrates using SELECT FOR UPDATE to serialize transitions, compares this with SERIALIZABLE isolation, and argues that append-only history requires explicit concurrency checks to avoid dual transitions and unintended side effects.