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
