Re: BUG #19686: Rolling back SET TABLESPACE

*Indexes on the table, if any, are not moved; but they can be moved*
*separately with additional SET TABLESPACE commands.* [1]

On Tue, Sep 29, 2026 at 10:44 PM Andres Freund <andres(at)anarazel(dot)de> wrote:

> What about forcing indexes to be copied to a new relfilenode when copying
> the
> underlying table?

That would be slower ...

On Wed, Sep 30, 2026 at 1:09 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:

> Yes, putting the cost within the ALTER TABLE would feel less
> surprising.

If I am moving to a different tablespace, chances are that the current
tablespace
is too full, and adding files there would possibly make it worse. It would
make
sense to take up space in the target tablespace not the source.

Manu,

This comment only exist after the patch, and talk about how things work
before the patch.
+ /*
+ * Moving a table's heap assigns it a new relfilenode, but its indexes are
+ * deliberately left in place with their existing relfilenodes. That mix
+ * is unsafe across a rollback: if the transaction inserts into the table
+ * after this and then aborts, the heap's new relfilenode is discarded and
+ * its file reverts to the pre-move contents, freeing the TIDs used by the
+ * aborted rows; but the matching index entries were written to the
+ * unchanged index files and survive the abort. A later insert can reuse a
+ * freed heap TID, leaving two index entries pointing at the same live heap
+ * tuple -- index corruption (bug #19686). Give each index a fresh
+ * relfilenumber, copied within its own tablespace, so it shares the heap's
+ * new-relfilenode fate: on abort the new heap and index files are all
+ * discarded together, and on commit they are all kept.
+ */

And you need test cases.

In that case it seems that it is better to go forward with your approach,
and I am stepping down as an author.

[1]
https://www.postgresql.org/docs/18/sql-altertable.html#SQL-ALTERTABLE-DESC-SET-TABLESPACE