From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andres Freund <andres@anarazel.de>
cc: Alexandre Felipe <o.alexandre.felipe@gmail.com>,
        PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: BUG #19686: Rolling back SET TABLESPACE
In-reply-to: <apatdp3ikxzpotmbsd5dfgocimljrtenuyjgmvnuk3njoworw7@xatncdnxgpwy>
References: 
 <CAE8JnxP+Ubdj-kaxfgNt=3_FzD3GgOc3gEvPAvrXKfgTgH2q5w@mail.gmail.com>
 <lqlli2yiai6dimhlzm47qiwuiz3qczt7snhzkx6qvrmmbltw6w@i7pvrw556qwg>
 <714053.1790725180@sss.pgh.pa.us>
 <apatdp3ikxzpotmbsd5dfgocimljrtenuyjgmvnuk3njoworw7@xatncdnxgpwy>
Comments: In-reply-to Andres Freund <andres@anarazel.de>
	message dated "Fri, 02 Oct 2026 14:30:33 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1383372.1790967327.1@sss.pgh.pa.us>
Date: Fri, 02 Oct 2026 14:55:27 -0400
Message-ID: <1383373.1790967327@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1383373.1790967327%40sss.pgh.pa.us>
Precedence: bulk

Andres Freund <andres@anarazel.de> writes:
> 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

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

+1 to both things, doesn't seem too difficult.  (1) probably covers
the majority of existing usage --- else, we'd have noticed this
problem a long time ago.

>> 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

Agreed, that at least provides a workaround.

			regards, tom lane