The background update that marks unreferenced state groups for deletion
reads a batch of groups joined to their next groups, then decides each
row in turn: a group is a candidate if every next group is. That needs a
group's next groups decided before it, but the query had no outer ORDER
BY, so the order was whatever the join produced. When a group came
first, its next group looked undecided, the group was kept, and once the
next group was deleted it was left behind for good.
`test_clear_unreferenced_state_groups` caught this about one run in ten
on Postgres, and more often as other changes moved the timing. A next
group is always created after its prev group, so has a higher ID:
ordering by ID descending decides it first. The test now passes every
time.
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01PVLsNh6akt4ifEizY4D1ta
