BUG #19686: Rolling back SET TABLESPACE
This patchset addresses the issue reported on the pgsql-bugs [1]/messages/by-id/19686-30f4da834585129f@postgresql.org
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]: /messages/by-id/19686-30f4da834585129f@postgresql.org
/messages/by-id/19686-30f4da834585129f@postgresql.org
Attachments:
t253815_1v1-0001-nbtree-skip-insertion-of-existing-tuples.patchapplication/octet-stream; name=v1-0001-nbtree-skip-insertion-of-existing-tuples.patchDownload+31-11
v1-0002-logging-and-testcase.patchapplication/octet-stream; name=v1-0002-logging-and-testcase.patchDownload+192-1
v1-0004-establish-expected-tablespace.out.patchapplication/octet-stream; name=v1-0004-establish-expected-tablespace.out.patchDownload+226-2
v1-0003-fix-deferred-relation-copy.patchapplication/octet-stream; name=v1-0003-fix-deferred-relation-copy.patchDownload+170-2
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.alexandre.felipe@gmail.com> wrote:
Show quoted text
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.
I wrote a patch for the same bug before I saw this thread -- thanks to
Shihao for pointing me here. Having read #7312, I think your deferred
copy is the right approach, and better than what I had: it fixes the
root cause without touching the indexes.
What do you think of this approach?
On performance it looks very good. On a 1M-row table with three indexes
I measured SET TABLESPACE at about 104 ms with #7312 versus about 108 ms
on master -- no measurable overhead, since the indexes are left alone.
(My own patch copies each index to a new relfilenode, which came out
around 1.7x, so #7312 is the better direction and I'm happy to drop mine.)
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
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.
2. Failure to copy the files at the end of transaction [...]
Agreed this one deserves care -- moving the copy into PreCommit means a
full tablespace now fails at commit time rather than during the command.
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?
Happy to help test further.
Manu
Hi Manu,
On Sun, Sep 27, 2026 at 8:09 PM Manu <manuelreyesbravo@gmail.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
Attachments:
t253815_4v2-0003-establish-expected-tablespace.out.patchapplication/octet-stream; name=v2-0003-establish-expected-tablespace.out.patchDownload+322-2
v2-0002-fix-deferred-relation-copy.patchapplication/octet-stream; name=v2-0002-fix-deferred-relation-copy.patchDownload+199-2
v2-0001-logging-and-testcase.patchapplication/octet-stream; name=v2-0001-logging-and-testcase.patchDownload+224-1
Hi Al,
I built v2 on master (82d31451606) with --enable-cassert and checked the
two cases, plus your question.
v2 fixes both. The double SET TABLESPACE now lands right: a table moved
to ts and then back to pg_default in one transaction ends in pg_default,
where v1 left it in ts. The original recipe is still fixed: the
rollback + INSERT btree case does not trap and bt_index_check reports
nothing, against master where it fails the _bt_posting_valid assertion.
One build problem: v2-0002 does not compile with --enable-cassert.
deferred_tablespace_move_compare_relid() reads its cells with
lfirst_node(PendingTablespaceMove, ...), but PendingTablespaceMove is a
plain struct, not a Node, so T_PendingTablespaceMove is undeclared.
Without assertions castNode() is a plain cast, so the build passes, which
is why it slips through, but the cassert buildfarm animals would fail on
it. PreCommit_on_commit_actions() walks on_commits with a plain
(OnCommitItem *) lfirst() cast; the same here builds under cassert.
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?)
I can reproduce it. With v2, inside the transaction
pg_class.reltablespace for an indexed table still reads the old
tablespace until commit, since the whole move is deferred. On master
SET TABLESPACE updates the catalog at execution time, so a query in the
same transaction sees the new tablespace right away. So v2 does change
that observable behavior.
Whether it is worth fixing is your call. The one thing I'd note is that
the alternative, showing the pending tablespace in the catalog within
the transaction, would leave reltablespace pointing at a tablespace the
file has not reached until commit, so it isn't only a matter of moving
the catalog update earlier. I'll leave the design to you; I mainly
wanted to confirm the behavior is real and that it differs from master.
Regards,
Manu
Thank you Manu,
Trying to keep iterations quick :)
On Tue, Sep 29, 2026 at 12:45 PM Manu <manuelreyesbravo@gmail.com> wrote:
Hi Al,
I built v2 on master (82d31451606) with --enable-cassert and checked the
two cases, plus your question.
v2 fixes both. The double SET TABLESPACE now lands right: a table moved
to ts and then back to pg_default in one transaction ends in pg_default,
where v1 left it in ts. The original recipe is still fixed: the
rollback + INSERT btree case does not trap and bt_index_check reports
nothing, against master where it fails the _bt_posting_valid assertion.
Yes
One build problem: v2-0002 does not compile with --enable-cassert.
Fixed, using pointer casts directly (indentation looks weird to me, but that
was pg_indent's choice).
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?)I can reproduce it. With v2, inside the transaction
pg_class.reltablespace for an indexed table still reads the old
tablespace until commit, since the whole move is deferred. On master
SET TABLESPACE updates the catalog at execution time, so a query in the
same transaction sees the new tablespace right away. So v2 does change
that observable behavior.Whether it is worth fixing is your call. The one thing I'd note is that
the alternative, showing the pending tablespace in the catalog within
the transaction, would leave reltablespace pointing at a tablespace the
file has not reached until commit, so it isn't only a matter of moving
the catalog update earlier. I'll leave the design to you; I mainly
wanted to confirm the behavior is real and that it differs from master.
v3 adds a paragraph about deferred copy and clarifies that catalog
updates might not be visible within the transaction, I think it is good
that it reflects the physical location of the relation. Reading the
preceding
paragraph I decided that it was worth adding a test case for partitioned
tables.
The tests for this are already getting long so I moved to a separate file,
tablespace_xact, in parallel with tablespace instead of appending on it.
Regards,
Alexandre
Attachments:
t253815_6v3-0003-establish-expectations.patchapplication/octet-stream; name=v3-0003-establish-expectations.patchDownload+482-4
v3-0002-fix-deferred-relation-copy.patchapplication/octet-stream; name=v3-0002-fix-deferred-relation-copy.patchDownload+199-2
v3-0004-establish-expected-tablespace.out.patchapplication/octet-stream; name=v3-0004-establish-expected-tablespace.out.patchDownload+0-1
v3-0001-logging-and-testcase.patchapplication/octet-stream; name=v3-0001-logging-and-testcase.patchDownload+287-2
Hi,
On 2026-09-16 23:06:34 +0100, Alexandre Felipe 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.
I think it'd also just hide corruption. That's definitely not the way to go.
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.
I don't think copying the file at commit is a good path, we shouldn't make
commits take arbitrarily long without pretty darn good reason. I don't think
this is that.
What about forcing indexes to be copied to a new relfilenode when copying the
underlying table?
Greetings,
Andres Freund
Hello
v3 adds a paragraph about deferred copy and clarifies that catalog
updates might not be visible within the transaction, I think it is good
that it reflects the physical location of the relation. Reading the
preceding
paragraph I decided that it was worth adding a test case for partitioned
tables.
0004 is empty, the documentation is missing.
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?
The patch could keep at least the creation of the storage (directory) at command time, and leave only the data copy to the end. That won't help against out of space errors, but it could properly handle all cases of permission / preexisting directory issues.
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.
This is only true when nothing modifies the relation between the ALTER TABLESPACE and the COMMIT. When something does, the deferred copy copies the post-modification relation, so every row written after the move is written twice, once into the old file and once into the new by the copy. This is especially visible with wal_level=minimal, where previously we logged nothing and with the patch we log the entire table in this case.
This doesn't seem fixable to me, but it also doesn't seem to be a blocker for the patch/approach to me. If a user does things the other way around, first modifying the relation and then doing alter, master already does the same amount of writes, so it only affects one specific order.
And some specific comments for the patches:
1.
+ /*
+ * Cluster moves by relation id, then for each relation apply the last
+ * change.
+ */
+ list_sort(deferred_tablespace_moves, deferred_tablespace_move_compare_relid);
and
+static int
+deferred_tablespace_move_compare_relid(const ListCell *a, const ListCell *b)
+{
+ PendingTablespaceMove *ca = (PendingTablespaceMove *) a;
+ PendingTablespaceMove *cb = (PendingTablespaceMove *) b;
+ int t = pg_cmp_u32(ca->relid, cb->relid);
+
+ if (t == 0) /* compare pointers for stability */
+ t = (a < b) ? -1 : 1;
+ return t;
+}
This comparator is wrong in multiple ways, see the comment for list_sort:
* The comparator function is declared to receive arguments of type
* const ListCell *; this allows it to use lfirst() and variants
* without casting its arguments. Otherwise it behaves the same as
* the comparator function for standard qsort().
*
* Like qsort(), this provides no guarantees about sort stability
* for equal keys.
The issue is also easily verifiable with an SQL reproducer:
BEGIN;
SAVEPOINT s;
ALTER TABLE b SET TABLESPACE regress_ts1;
ALTER TABLE b SET TABLESPACE pg_default;
ROLLBACK TO s;
ALTER TABLE a SET TABLESPACE regress_ts1;
ALTER TABLE a SET TABLESPACE pg_default;
COMMIT;
Do we need sort at all here? Why can't the code walk the list, and resolve the last entry per relid?
2. dropping a relation with a pending move now makes COMMIT fail with an internal error
CREATE TABLESPACE regress_ts1 LOCATION '';
CREATE TABLE d1(a int primary key);
BEGIN; ALTER TABLE d1 SET TABLESPACE regress_ts1; DROP TABLE d1; COMMIT;
Skipping entries whose relation no longer exists seems safe to me.
3. tablespace_xact.sql fails with max_prepared_transaction = 0
this is a test only issue, but should be fixed, this causes a clear CI failure
Another issue with it is the naming of the tablespaces, those should start with regress_:
+CREATE TABLESPACE xact_tblspace LOCATION '';
+CREATE TABLESPACE xact_tblspace2 LOCATION '';
See ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS
4. ALTER TABLE ALL IN TABLESPACE acts on a stale catalog
This is another visible effect of the staleness question that was already discussed.
SET allow_in_place_tablespaces = on;
CREATE TABLESPACE ts1 LOCATION '';
CREATE TABLESPACE ts2 LOCATION '';
CREATE TABLE t (a int PRIMARY KEY) TABLESPACE ts1;
BEGIN;
ALTER TABLE t SET TABLESPACE pg_default;
ALTER TABLE ALL IN TABLESPACE ts1 SET TABLESPACE ts2;
COMMIT;
SELECT coalesce(spcname, 'pg_default') AS tablespace
FROM pg_class c LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace
WHERE c.relname = 't';
And it seems to me that ALTER TABLE ALL IN TABLESPACE could follow the intent of the transaction based on the pending list.
Andres Freund <andres@anarazel.de> writes:
I don't think copying the file at commit is a good path, we shouldn't make
commits take arbitrarily long without pretty darn good reason. I don't think
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 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.
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
On Tue, Sep 29, 2026 at 07:39:40PM -0400, Tom Lane wrote:
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.
Yes, putting the cost within the ALTER TABLE would feel less
surprising.
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.
And it's something that would qualify as a new feature, not really
backpatchable. At least it feels risky compared to the idea of
copying the index relfilenode.
--
Michael
Import Notes
Reply to msg id not found: 714053.1790725180@sss.pgh.pa.us
Thank you all.
Was good to see so many reviews in such a short time.
Given that the expected behaviour is to copy only the heap.
Indexes get corrupted on modifications.
We shouldn't push the cost to commit.
What if we simply block modifications to the table in a transaction
after it moved to a new tablespace?
Regards,
Alexandre
Alexandre Felipe <o.alexandre.felipe@gmail.com> writes:
What if we simply block modifications to the table in a transaction
after it moved to a new tablespace?
If we were looking for a quick-n-dirty functionality-losing patch,
we'd just reject ALTER SET TABLESPACE within transaction blocks.
Perhaps that's the right answer for the back branches, but
I'd prefer not to go that way.
It seems to me that there is consensus among the senior hackers
who have looked at this about what to do. I'm not sure why you
are pushing for a more complex and more risky answer.
regards, tom lane
Thank you Tom,
On Wed, Sep 30, 2026 at 2:40 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Alexandre Felipe <o.alexandre.felipe@gmail.com> writes:
What if we simply block modifications to the table in a transaction
after it moved to a new tablespace?If we were looking for a quick-n-dirty functionality-losing patch,
we'd just reject ALTER SET TABLESPACE within transaction blocks.
Perhaps that's the right answer for the back branches, but
I'd prefer not to go that way.
Noted, but could you please address the question directly.
Currently ALTER SET TABLESPACE already locks the tables,
blocking changes to the old file, this prevents corrupted indices on
committed transactions.
What I suggested was to mirror that restriction inside the transaction,
i.e. you can't modify the table in the new tablespace during the
transaction.
We lose one feature but one could even argue that it makes the
behaviour more consistent in some sense.
It seems to me that there is consensus among the senior hackers
I see, there is a consensus that deferring file copies is
not a good trade-off and I am not pushing for that.
Regards,
Alexandre Felipe
Import Notes
Reply to msg id not found: 769661.1790775621@sss.pgh.pa.us
*Indexes on the table, if any, are not moved; but they can be moved*
*separately with additional SET TABLESPACE commands.* [1]https://www.postgresql.org/docs/18/sql-altertable.html#SQL-ALTERTABLE-DESC-SET-TABLESPACE
On Tue, Sep 29, 2026 at 10:44 PM Andres Freund <andres@anarazel.de> wrote:
What about forcing indexes to be copied to a new relfilenode when copying
the
underlying table?
That would be slower ...
On Wed, Sep 30, 2026 at 1:09 AM Michael Paquier <michael@paquier.xyz> wrote:
Yes, putting the cost within the ALTER TABLE would feel less
surprising.
If I am moving to a different tablespace, chances are that the current
tablespace
is too full, and adding files there would possibly make it worse. It would
make
sense to take up space in the target tablespace not the source.
Manu,
This comment only exist after the patch, and talk about how things work
before the patch.
+ /*
+ * Moving a table's heap assigns it a new relfilenode, but its indexes are
+ * deliberately left in place with their existing relfilenodes. That mix
+ * is unsafe across a rollback: if the transaction inserts into the table
+ * after this and then aborts, the heap's new relfilenode is discarded and
+ * its file reverts to the pre-move contents, freeing the TIDs used by the
+ * aborted rows; but the matching index entries were written to the
+ * unchanged index files and survive the abort. A later insert can reuse a
+ * freed heap TID, leaving two index entries pointing at the same live heap
+ * tuple -- index corruption (bug #19686). Give each index a fresh
+ * relfilenumber, copied within its own tablespace, so it shares the heap's
+ * new-relfilenode fate: on abort the new heap and index files are all
+ * discarded together, and on commit they are all kept.
+ */
And you need test cases.
In that case it seems that it is better to go forward with your approach,
and I am stepping down as an author.
[1]: https://www.postgresql.org/docs/18/sql-altertable.html#SQL-ALTERTABLE-DESC-SET-TABLESPACE
https://www.postgresql.org/docs/18/sql-altertable.html#SQL-ALTERTABLE-DESC-SET-TABLESPACE
Attachments:
t253815_12v4-copy-indices-on-alter-table-set-tablespace.patchapplication/octet-stream; name=v4-copy-indices-on-alter-table-set-tablespace.patchDownload+93-1
Hi Alexandre,
Thanks -- and for going with this approach. You raised two things: the
comment still described the pre-patch behavior, and it needed test
cases. Both are addressed in the attached v2:
- The comment no longer narrates the pre-patch behavior. It now says,
in the present tense, that the heap has just been rewritten and the
indexes must share that fate, and leaves the detail to
ATExecSetTableSpaceNewIndexRelfilenumber.
- There is a regression test now, in the tablespace suite. It runs
the rollback-then-reinsert sequence and checks, with seqscans off,
that the index agrees with the single live heap row -- it returns
two against the bug, where the aborted transaction's index entry
survives and aliases the reused TID. check-world is green with the
patch.
The approach is the one we settled on: after the heap move, each of the
table's indexes gets a fresh relfilenumber copied within its own
tablespace, so an abort discards the new heap and index files together.
It does not change the indexes' tablespace, matching the documented
behavior that SET TABLESPACE leaves indexes in place.
Regards,
Manu
Attachments:
t253815_15v2-0001-Fix-index-corruption-SET-TABLESPACE-rollback.patchtext/x-patchDownload+132-1
Hi Manu,
I reviewed v2 and it looks correct. I asked Fable to test rollback,
savepoints, 2PC, crash recovery and a standby, and they all pass. The
test fails without the fix.
v2 also fixes SET TABLESPACE, TRUNCATE, ROLLBACK, which empties the
index on master.
Alexandre's full tablespace case does fail with v2. Moving the
indexes first works.
It applies to 16 and up. 14 and 15 need the old RelFileNode names.
Thanks,
Shihao
Thank you Manu,
Again talking about past,
I am insisting on this point, I don't see the codebase talking about past
bugs,
the test just describes the correct behaviour.
+-- With seqscans disabled this count is answered from the index; it must
match
+-- the single live heap row (it returns 2 against the bug, where the
aborted
+-- transaction's index entry survives and aliases the reused TID).
If we are recreating the indices, a better way to observe the effect
is by inserting enough data to grow one page inside the transaction
and checking pg_relation_size before and after the rollback.
All that story about visibility map, reindexing, vacuuming indices,
or imagine that in the future we have another way to satisfy the query
without the indices.
+CREATE TABLE tbspace_19686 (a int);
Do we need to keep that ticket number here? Maybe in the commit message.
That suffix impairs readability in my opinion.
+ * Give one of a table's indexes a fresh relfilenumber within its existing
+ * tablespace, copying the current index file to the new relfilenumber.
This is very counterintuitive,
moving to a different tablespace should not take up more space in the
original tablespace.
If one is moving to a different tablespace, chances are that the current
tablespace
is too full, and adding files there would possibly make it worse. It
would make
sense to take up space in the target tablespace not the source.
Regards,
Alexandre
Hi Alexandre, Shihao,
Thanks to both. v4 attached; it changes the approach because of
Alexandre's last point, so let me take that one first.
moving to a different tablespace should not take up more space in the
original tablespace.
Agreed, and v4 does not. The indexes only need to follow the heap's
rewrite if the table is modified again in the same transaction, which
is also the only way the corruption can happen. So v4 leaves SET
TABLESPACE itself alone and gives an index a new relfilenumber (copied
within its own tablespace, so the indexes stay where the documentation
says) when the executor opens it to modify a table whose storage was
replaced in the current transaction, and before an in-place TRUNCATE of
such a table -- the case Shihao found. The ALTER alone in its
transaction copies nothing.
Measured with a 162 MB table and 188 MB of indexes in a nearly full
source tablespace: the move succeeds, the source needs no extra space
(v3 needed the size of the indexes there), the target receives the
162 MB heap, and the ALTER takes 0.13 s instead of 0.20 s. Modifying
the table in the same transaction after the move still needs room for
the index copies; with the source full that fails with ENOSPC at the
INSERT, cleanly (relfilenodes unchanged, nothing left behind beyond the
usual zero-length files until the next checkpoint, amcheck passes, same
after a restart).
The hook is one call in ExecOpenIndices and one in the in-place branch
of TRUNCATE. It keys on rd_firstRelfilelocatorSubid, which says whether
a relation's storage differs from what it was at the start of the top
transaction and is kept accurate for RelationNeedsWAL().
I don't see the codebase talking about past bugs,
the test just describes the correct behaviour.
Agreed. The comments and the test describe the behavior only; the bug
number is in the commit message.
If we are recreating the indices, a better way to observe the effect
is by inserting enough data to grow one page inside the transaction
and checking pg_relation_size before and after the rollback.
Done, and it is a better test: on master the index goes from 16384 to
65536 bytes and stays there after the rollback; with the fix it is back
to 16384. The test also checks that the move alone leaves the index
file untouched, the same across a subtransaction, and the TRUNCATE
case.
Do we need to keep that ticket number here?
Renamed to tbspace_rollback.
Shihao: thanks for the broader tests. Patches for REL_15 and REL_14
(old RelFileNode names; in 14 the test lives in tablespace.source) are
attached; each passes the regression suite on its branch.
Regards,
Manu
Thank you Manu,
was v4 supposed to be attached?
On Fri, Oct 2, 2026 at 3:36 PM Manu <manuelreyesbravo@gmail.com> wrote:
Show quoted text
Hi Alexandre, Shihao,
Thanks to both. v4 attached; it changes the approach because of
Alexandre's last point, so let me take that one first.moving to a different tablespace should not take up more space in the
original tablespace.Agreed, and v4 does not. The indexes only need to follow the heap's
rewrite if the table is modified again in the same transaction, which
is also the only way the corruption can happen. So v4 leaves SET
TABLESPACE itself alone and gives an index a new relfilenumber (copied
within its own tablespace, so the indexes stay where the documentation
says) when the executor opens it to modify a table whose storage was
replaced in the current transaction, and before an in-place TRUNCATE of
such a table -- the case Shihao found. The ALTER alone in its
transaction copies nothing.Measured with a 162 MB table and 188 MB of indexes in a nearly full
source tablespace: the move succeeds, the source needs no extra space
(v3 needed the size of the indexes there), the target receives the
162 MB heap, and the ALTER takes 0.13 s instead of 0.20 s. Modifying
the table in the same transaction after the move still needs room for
the index copies; with the source full that fails with ENOSPC at the
INSERT, cleanly (relfilenodes unchanged, nothing left behind beyond the
usual zero-length files until the next checkpoint, amcheck passes, same
after a restart).The hook is one call in ExecOpenIndices and one in the in-place branch
of TRUNCATE. It keys on rd_firstRelfilelocatorSubid, which says whether
a relation's storage differs from what it was at the start of the top
transaction and is kept accurate for RelationNeedsWAL().I don't see the codebase talking about past bugs,
the test just describes the correct behaviour.Agreed. The comments and the test describe the behavior only; the bug
number is in the commit message.If we are recreating the indices, a better way to observe the effect
is by inserting enough data to grow one page inside the transaction
and checking pg_relation_size before and after the rollback.Done, and it is a better test: on master the index goes from 16384 to
65536 bytes and stays there after the rollback; with the fix it is back
to 16384. The test also checks that the move alone leaves the index
file untouched, the same across a subtransaction, and the TRUNCATE
case.Do we need to keep that ticket number here?
Renamed to tbspace_rollback.
Shihao: thanks for the broader tests. Patches for REL_15 and REL_14
(old RelFileNode names; in 14 the test lives in tablespace.source) are
attached; each passes the regression suite on its branch.Regards,
Manu