Re: BUG #19686: Rolling back SET TABLESPACE + INSERT leads to index corruption

On 2026-09-15 08:59, Alexandre Felipe wrote:
> I started investigating.
> ...
> Anyone else looking into this?

I looked into it too; here is what I found, in case it is useful.

The trigger is that ALTER TABLE ... SET TABLESPACE gives the heap a new
relfilenode while the table's indexes deliberately keep theirs. The two
then unwind differently on abort: the heap's new file is discarded, so
the heap TIDs used by rows inserted after the SET TABLESPACE are free
again, but the index entries for those rows were written to the
unchanged index files and survive. A later insert reuses one of those
freed TIDs, and the index is left with two entries pointing at the same
live heap tuple.

That fits your point that it is not really an nbtree problem: gist shows
the same duplicate through an index-only scan, and the _bt_posting_valid
assert from 0d861bbb7 only makes btree notice it. Dropping the insert,
or the SET TABLESPACE, or turning the insert into an update all avoid
it, which lines up with the TID-reuse explanation.

Attached is a patch for discussion. After the heap is moved,
ATExecSetTableSpace gives each of the table's indexes a new
relfilenumber by copying it within its own tablespace, so the indexes
share the heap's rollback: on abort the new heap and index files are
discarded together, and on commit they are kept together. It closes
both the btree and the gist case here, and make check-world passes.

I first tried reindexing the indexes instead, which also fixes it, but
on a 1M-row table with three indexes that made SET TABLESPACE roughly
20x slower (about 100 ms to 1.9 s); copying the files keeps it near 2x.
As far as I can tell an abort-time fix is not possible, so SET TABLESPACE
has to make the indexes safe up front, but whether this is the right way
to do it is a question for people who know this code better than I do.

Manu