PostgreSQL fixes index corruption after rolled-back tablespace moves

PostgreSQL developers are closing a long-standing correctness bug in which rolling back ALTER TABLE SET TABLESPACE inside an open transaction could corrupt indexes and make later queries return the wrong rows.

The flaw, tracked as bug 19686, stems from how the server handles a tablespace move that is still uncommitted. The heap is rewritten into a new file under the target tablespace, but indexes keep their existing files. Inserts and other changes after the ALTER update the live index in place while the heap changes land only in the new copy. On rollback the heap reverts and frees those tuple IDs; the index entries survive. A later insert can reuse a freed ID, so two index entries point at one live heap row. With sequential scans disabled, a simple equality lookup can return a value that was never supposed to match.

Alexandre Felipe reported the issue and first tried to paper over the crashes per index type, then proposed deferring the physical file copies until commit so the server would keep modifying the durable files in place. Senior developers rejected that path. Andres Freund and Tom Lane argued that commits must not take arbitrarily long or absorb operations that can fail for lack of space or permissions. Michael Paquier agreed the cost belongs on the ALTER itself, where operators already expect a data copy.

The approach that gained consensus is to give each index a fresh relation file number, still inside its own tablespace, so indexes share the heap's rewrite fate: on abort the new heap and index files are discarded together; on commit they are kept. Manu refined the patch so a plain SET TABLESPACE copies nothing extra; indexes are rewritten only if the table is modified again in the same transaction (or truncated in place), the only cases that can create the inconsistency. The pure move therefore needs no extra space in a full source tablespace, matching documented behavior that SET TABLESPACE leaves indexes where they are.

The fix is aimed at current releases back through the 14 series, with regression coverage for the rollback-then-reinsert sequence and related paths such as two-phase commit and crash recovery.