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: <lqlli2yiai6dimhlzm47qiwuiz3qczt7snhzkx6qvrmmbltw6w@i7pvrw556qwg>
References: 
 <CAE8JnxP+Ubdj-kaxfgNt=3_FzD3GgOc3gEvPAvrXKfgTgH2q5w@mail.gmail.com>
 <lqlli2yiai6dimhlzm47qiwuiz3qczt7snhzkx6qvrmmbltw6w@i7pvrw556qwg>
Comments: In-reply-to Andres Freund <andres@anarazel.de>
	message dated "Tue, 29 Sep 2026 17:44:57 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <714052.1790725180.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 29 Sep 2026 19:39:40 -0400
Message-ID: <714053.1790725180@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/714053.1790725180%40sss.pgh.pa.us>
Precedence: bulk

Andres Freund <andres@anarazel.de> writes:
> I don't think copying the file at commit is a good path, we shouldn't ma=
ke
> commits take arbitrarily long without pretty darn good reason. I don't t=
hink
> this is that.

+1.  Moving operations with a significant probability of failure into
COMMIT is also an anti-pattern.

> What about forcing indexes to be copied to a new relfilenode when copyin=
g 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.

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.

			regards, tom lane