Re: BUG #19686: Rolling back SET TABLESPACE

Hi,

On 2026-09-29 19:39:40 -0400, Tom Lane wrote:
> > What about forcing indexes to be copied to a new relfilenode when copying the
> > underlying table?
>
> That seems like the logical solution to me. Nobody will be surprised
> if ALTER SET TABLESPACE takes a long time for a big table; at least
> not if they understand that it requires copying the data somewhere
> else. Imposing costs at COMMIT time might well surprise people.

I wonder if we should try to apply two optimizations, even in the back
branches:

1) don't copy indexes if the SET TABLESPACE is executed at the top-level

I think most of the time that is what one should do anyway (to avoid holding
too many locks at once etc), and it'd give folks that are negatively
affected a way out.

I guess it could theoretically be possible to write to an index from an
event trigger and then trigger an abort? But event triggers are a superuser
only facility, and at some point a superuser gets to keep the pieces if they
are intent on breaking stuff.

2) Avoid the index copy if the index has been created in the current
subtransaction.

I don't think there's a danger of corruption in that case, since the
relfilenode of the index would be thrown away anyway, if the SET TABLESPACE
rolls back.

> The one disadvantage I see is that (I imagine) a common use-case is
> to move both a table and its indexes to a new tablespace, and this
> solution will imply that that sequence double-copies the indexes.
> Maybe it'd be worth providing a command variant that copies the
> table and its indexes to a new tablespace in one step. But that
> is a future optimization, not part of the bug fix; and I could be
> wrong about whether anyone even cares.

With the 2) from above, that could then be achieved by having a transaction
first move the indexes and then the table itself. Probably not as good as a
command doing both, but it can be done without a new syntax...q

Greetings,

Andres Freund