Re: BUG #19686: Rolling back SET TABLESPACE

Hi Manu,

What do you think of this approach?
Points that I know require some attention are:
1. CFBot windows test is failing.
2. Failure to copy the files at the end of transaction, e.g. the new
tablespace doesn't have enough space. Will the transaction roll back
cleanly reclaiming the space?

I had a quick look in your patch, that approach is simpler, but if I
understand
correctly ATExecSetTableSpaceNewIndexRelfilenumber copies the index
upfront while upstream doesn't copy the indexes at all.

It is not difficult to imagine a scenario where this is undesirable. For
instance,
if the user is moving the table because the original tablespace is full,
creating an additional copy of the index might make the problem worse.

My proposal was to do the other way, push the cost to the end of the
transaction instead, even the heap copy, so if the transaction fails it will
do no file copies. If the transaction completes, it will do exactly the same
copies that upstream does, just in a different order. At least that is the
rationale, maybe during review additional cases that I missed will come up.

Regards,
Alexandre

On Wed, Sep 16, 2026 at 11:06 PM Alexandre Felipe <
o(dot)alexandre(dot)felipe(at)gmail(dot)com> wrote:

> This patchset addresses the issue reported on the pgsql-bugs [1]
>
> The root cause is that after the relation files are copied to a new
> tablespace queries
> update the index in place but the heap is updated only in the tablespace
> copy. If
> the transaction is rolled back, the index and the heap becomes
> inconsistent.
>
> First I tried to fix the crash, easy for btree, manageable for hash, but
> for GiST that
> would not be feasible, AFAIK would have to perform array searches possibly
> over
> multiple pages. Later thinking about this I noticed something I didn't
> realise on my
> first read.
>
> Even without inserting duplicates, and no crashes, it can produce
> incorrect results.
>
> SET enable_seqscan = off;
> SET enable_bitmapscan = off;
> SET allow_in_place_tablespaces = true;
> CREATE TABLESPACE ts LOCATION '';
> CREATE TABLE t(a int);
> CREATE INDEX ON t(a);
> BEGIN;
> ALTER TABLE t SET TABLESPACE ts;
> INSERT INTO t VALUES (0); -- this adds (0, 1) | 0 to the index in the ts
> copy
> ROLLBACK;
> INSERT INTO t VALUES (41); -- this adds (0, 1) | 41 in the default
> tablespace
> SELECT ctid, a FROM t WHERE a = 0;
> ctid | a
> -------+----
> (0,1) | 41
> (1 row)
>
> So, I decided to fix the root cause: modifying a non-durable copy of the
> file.
> I thought it would be way harder, but the code was architected well enough
> that I could save a list of deferred copies, and keep modifying the the
> table
> in place. If the transaction is rolled back all the tuples in the index
> will have
> its (possibly dead) in the heap, effectively reserving those TID, this
> prevents
> both the insertion of duplicates, and the resuscitation of dead tuples by
> later changes.
>
>
>
> [1]
> https://www.postgresql.org/message-id/19686-30f4da834585129f%40postgresql.org
>