Re: BUG #19686: Rolling back SET TABLESPACE

Hi Manu,

On Sun, Sep 27, 2026 at 8:09 PM Manu <manuelreyesbravo(at)gmail(dot)com> wrote:

> One thing I ran into while testing: a second SET TABLESPACE in the same
> transaction, on a table with indexes, ends up in the wrong tablespace.
>
> 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;
> ALTER TABLE t SET TABLESPACE pg_default;
> COMMIT;
> -- master: t ends up in pg_default; with #7312: t ends up in ts
>

Noted

> The second ALTER calls CheckRelationTableSpaceMove() while pg_class still
> shows the original tablespace (the first move is deferred), so moving
> back to pg_default looks like a no-op and is dropped. The deferred move
> probably needs to be visible to a later SET TABLESPACE in the same
> transaction.
>

Visible to a later tablespace but not to other operations that might check
the tablespace to find the relation. I just noticed that if someone check
the pg_tablespaces inside the transaction they will get unexpected results.
(Is that something we need to fix?)

For v2 I am not using CheckRelationTableSpaceMove as a filter
for deferred copies, only for physical copies.
PreCommit_deferred_tablespace_moves is ignoring all intermediate
moves.
Added final tablespace verification to the test.

Output with debugging shows
+BEGIN;
+INSERT INTO defer_t VALUES (2);
+ALTER TABLE defer_t SET TABLESPACE regress_tblspace;
+DEBUG: ATExecSetTableSpace: rel 16793 to tblspace 16782 (deferred copy)
+INSERT INTO defer_t VALUES (3);
+ALTER TABLE defer_t SET TABLESPACE pg_default;
+DEBUG: ATExecSetTableSpace: rel 16793 to tblspace 1663 (deferred copy)
+INSERT INTO defer_t VALUES (4);
+COMMIT;
+EXECUTE check_tablespace;
+nspname | relname | tablespace
+---------+---------+------------
+ public | defer_t | (default)

On 0001: with 0003 in place I couldn't get the new "skip existing tuple"
> path to fire in any of my tests, and turning the assertion into a silent
> skip also drops a useful corruption check. Could it be removed now

Agreed, removed from v2.

Regards,
Alexandre