pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19686: Rolling back SET TABLESPACE + INSERT leads to index corruption
7+ messages / 6 participants
[nested] [flat]
* BUG #19686: Rolling back SET TABLESPACE + INSERT leads to index corruption
@ 2026-09-13 16:00 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 7+ messages in thread
From: PG Bug reporting form @ 2026-09-13 16:00 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: exclusion@gmail.com
The following bug has been logged on the website:
Bug reference: 19686
Logged by: Alexander Lakhin
Email address: exclusion@gmail.com
PostgreSQL version: 19beta3
Operating system: Ubuntu 24.04
Description:
The following script:
rm -rf /tmp/ts
mkdir /tmp/ts
echo "
CREATE TABLESPACE ts LOCATION '/tmp/ts';
CREATE TABLE t (a int);
CREATE INDEX ON t(a);
BEGIN;
ALTER TABLE t SET TABLESPACE ts;
INSERT INTO t VALUES (0);
ROLLBACK;
INSERT INTO t VALUES (0);
INSERT INTO t SELECT i FROM generate_series(1, 4000) i;
" | psql
triggers:
2026-09-13 18:09:42.598 EEST|postgres|regression|6aa6bcb6.297fd6|LOG:
statement: INSERT INTO t SELECT i FROM generate_series(1, 4000) i;
TRAP: failed Assert("_bt_posting_valid(itup)"), File: "nbtdedup.c", Line:
900, PID: 2719702
ExceptionalCondition at assert.c:51:13
_bt_form_posting at nbtdedup.c:905:7
_bt_dedup_finish_pending at nbtdedup.c:584:11
_bt_dedup_pass at nbtdedup.c:173:15
_bt_delete_or_dedup_one_page at nbtinsert.c:2827:3
_bt_findinsertloc at nbtinsert.c:918:4
_bt_doinsert at nbtinsert.c:261:16
btinsert at nbtree.c:219:11
index_insert at indexam.c:231:9
The assertion failure is reproduced starting from 0d861bbb7.
The index corruption is also reproduced with gist:
echo "
CREATE TABLESPACE ts LOCATION '/tmp/ts';
CREATE TABLE t (a int4range);
CREATE INDEX ON t USING gist (a);
BEGIN;
ALTER TABLE t SET TABLESPACE ts;
INSERT INTO t VALUES (int4range(0, 5));
ROLLBACK;
INSERT INTO t VALUES (int4range(0, 5));
" | psql
echo "
SELECT * FROM t;
SET enable_seqscan = off;
SET enable_bitmapscan = off;
EXPLAIN SELECT * FROM t WHERE a && int4range(0, 100);
SELECT * FROM t WHERE a && int4range(0, 100);
" | psql
returns:
ROLLBACK
INSERT 0 1
a
-------
[0,5)
(1 row)
SET
SET
QUERY PLAN
-------------------------------------------------------------------------
Index Only Scan using t_a_idx on t (cost=0.14..40.39 rows=14 width=32)
Index Cond: (a && '[0,100)'::int4range)
(2 rows)
a
-------
[0,5)
[0,5)
(2 rows)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19686: Rolling back SET TABLESPACE + INSERT leads to index corruption
@ 2026-09-15 08:59 Alexandre Felipe <o.alexandre.felipe@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 1 reply; 7+ messages in thread
From: Alexandre Felipe @ 2026-09-15 08:59 UTC (permalink / raw)
To: exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
I started investigating.
The first thing I noticed is that we have no debugging on what happens
during
the SET TABLESPACE, I think that creating or deleting files deserves a line
in the log, so I will start by adding DEBUG1 around it.
Anyone else looking into this?
Regards,
Alexandre
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19686: Rolling back SET TABLESPACE + INSERT leads to index corruption
@ 2026-09-27 16:11 Manu <manuelreyesbravo@gmail.com>
parent: Alexandre Felipe <o.alexandre.felipe@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Manu @ 2026-09-27 16:11 UTC (permalink / raw)
To: Alexandre Felipe <o.alexandre.felipe@gmail.com>; pgsql-bugs@lists.postgresql.org; +Cc: Alexander Lakhin <exclusion@gmail.com>
On 2026-09-15 08:59, Alexandre Felipe wrote:
> I started investigating.
> ...
> Anyone else looking into this?
I looked into it too; here is what I found, in case it is useful.
The trigger is that ALTER TABLE ... SET TABLESPACE gives the heap a new
relfilenode while the table's indexes deliberately keep theirs. The two
then unwind differently on abort: the heap's new file is discarded, so
the heap TIDs used by rows inserted after the SET TABLESPACE are free
again, but the index entries for those rows were written to the
unchanged index files and survive. A later insert reuses one of those
freed TIDs, and the index is left with two entries pointing at the same
live heap tuple.
That fits your point that it is not really an nbtree problem: gist shows
the same duplicate through an index-only scan, and the _bt_posting_valid
assert from 0d861bbb7 only makes btree notice it. Dropping the insert,
or the SET TABLESPACE, or turning the insert into an update all avoid
it, which lines up with the TID-reuse explanation.
Attached is a patch for discussion. After the heap is moved,
ATExecSetTableSpace gives each of the table's indexes a new
relfilenumber by copying it within its own tablespace, so the indexes
share the heap's rollback: on abort the new heap and index files are
discarded together, and on commit they are kept together. It closes
both the btree and the gist case here, and make check-world passes.
I first tried reindexing the indexes instead, which also fixes it, but
on a 1M-row table with three indexes that made SET TABLESPACE roughly
20x slower (about 100 ms to 1.9 s); copying the files keeps it near 2x.
As far as I can tell an abort-time fix is not possible, so SET TABLESPACE
has to make the indexes safe up front, but whether this is the right way
to do it is a question for people who know this code better than I do.
Manu
Attachments:
[text/x-patch] v1-0001-Fix-index-corruption-SET-TABLESPACE-rollback.patch (6.6K, ../../179052547394.318321.10043901456417388246@gmail.com/2-v1-0001-Fix-index-corruption-SET-TABLESPACE-rollback.patch)
download | inline diff:
From 3c5662ff268ac2b2e803130b7199517160f5a4e9 Mon Sep 17 00:00:00 2001
From: Manu <manuelreyesbravo@gmail.com>
Date: Sun, 27 Sep 2026 13:08:36 -0300
Subject: [PATCH] Fix index corruption after rolling back ALTER TABLE SET
TABLESPACE
ALTER TABLE ... SET TABLESPACE rewrites a table's heap to a new
relfilenode but deliberately leaves the table's indexes on their
existing relfilenodes. The two then roll back by different mechanisms:
on abort the heap's new file is discarded, so the heap TIDs consumed by
rows inserted after the SET TABLESPACE become free again, while the
index entries for those rows were written to the unchanged index files
and survive the abort. A later insert can reuse a freed heap TID,
leaving two index entries that point at the same live heap tuple. This
surfaces as a _bt_posting_valid assertion failure in nbtree
deduplication, and as duplicate rows through an index-only scan in gist.
Make the indexes share the heap's rewrite: after moving the heap, give
each of the table's indexes a new relfilenumber by copying it within its
own tablespace, so that an abort discards the new heap and index files
together and a commit keeps them together.
Bug: #19686
Reported-by: Alexander Lakhin
---
src/backend/commands/tablecmds.c | 93 ++++++++++++++++++++++++++++++++
1 file changed, 93 insertions(+)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 0274d892f2e..918d517ac23 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -695,6 +695,7 @@ static void ATPrepChangePersistence(AlteredTableInfo *tab, Relation rel,
static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
const char *tablespacename, LOCKMODE lockmode);
static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode);
+static void ATExecSetTableSpaceNewIndexRelfilenumber(Oid indexOid, LOCKMODE lockmode);
static void ATExecSetTableSpaceNoStorage(Relation rel, Oid newTableSpace);
static void ATExecSetRelOptions(Relation rel, List *defList,
AlterTableType operation,
@@ -17520,9 +17521,11 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode)
{
Relation rel;
Oid reltoastrelid;
+ char relkind;
RelFileNumber newrelfilenumber;
RelFileLocator newrlocator;
List *reltoastidxids = NIL;
+ List *reltabidxids = NIL;
ListCell *lc;
/*
@@ -17540,6 +17543,7 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode)
}
reltoastrelid = rel->rd_rel->reltoastrelid;
+ relkind = rel->rd_rel->relkind;
/* Fetch the list of indexes on toast relation if necessary */
if (OidIsValid(reltoastrelid))
{
@@ -17586,6 +17590,13 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode)
RelationAssumeNewRelfilelocator(rel);
+ /*
+ * If this is a table, collect its index list now, while the relation is
+ * still open, so we can give each index a fresh relfilenumber below.
+ */
+ if (relkind == RELKIND_RELATION || relkind == RELKIND_MATVIEW)
+ reltabidxids = RelationGetIndexList(rel);
+
relation_close(rel, NoLock);
/* Make sure the reltablespace change is visible */
@@ -17599,6 +17610,88 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode)
/* Clean up */
list_free(reltoastidxids);
+
+ /*
+ * 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.
+ */
+ foreach(lc, reltabidxids)
+ ATExecSetTableSpaceNewIndexRelfilenumber(lfirst_oid(lc), lockmode);
+ list_free(reltabidxids);
+}
+
+/*
+ * Give one of a table's indexes a fresh relfilenumber within its existing
+ * tablespace, copying the current index file to the new relfilenumber.
+ *
+ * ATExecSetTableSpace() calls this for each index of a table whose heap it has
+ * just rewritten to a new relfilenode. The indexes must share that rewrite's
+ * transactional fate, otherwise an abort discards the heap's new file (freeing
+ * its TIDs) while leaving behind index entries that were written during the
+ * transaction, which a later insert can then alias -- index corruption (bug
+ * #19686). Unlike a full reindex this merely copies the existing index file,
+ * so the added cost stays close to that of the heap move itself.
+ */
+static void
+ATExecSetTableSpaceNewIndexRelfilenumber(Oid indexOid, LOCKMODE lockmode)
+{
+ Relation ind;
+ RelFileNumber newrelfilenumber;
+ RelFileLocator newrlocator;
+ Relation pg_class;
+ HeapTuple tuple;
+ ItemPointerData otid;
+ Form_pg_class rd_rel;
+
+ ind = relation_open(indexOid, lockmode);
+
+ /* Only plain indexes have storage that can hold the stale entries. */
+ if (ind->rd_rel->relkind != RELKIND_INDEX ||
+ !RELKIND_HAS_STORAGE(ind->rd_rel->relkind))
+ {
+ relation_close(ind, NoLock);
+ return;
+ }
+
+ /* Allocate a new relfilenumber in the index's current tablespace. */
+ newrelfilenumber = GetNewRelFileNumber(ind->rd_rel->reltablespace, NULL,
+ ind->rd_rel->relpersistence);
+ newrlocator = ind->rd_locator;
+ newrlocator.relNumber = newrelfilenumber;
+
+ /* Copy the index into the new file and schedule the old one for cleanup. */
+ index_copy_data(ind, newrlocator);
+
+ /* Update the pg_class row; only the relfilenode changes. */
+ pg_class = table_open(RelationRelationId, RowExclusiveLock);
+ tuple = SearchSysCacheLockedCopy1(RELOID, ObjectIdGetDatum(indexOid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for index %u", indexOid);
+ otid = tuple->t_self;
+ rd_rel = (Form_pg_class) GETSTRUCT(tuple);
+ rd_rel->relfilenode = newrelfilenumber;
+ CatalogTupleUpdate(pg_class, &otid, tuple);
+ UnlockTuple(pg_class, &otid, InplaceUpdateTupleLock);
+ heap_freetuple(tuple);
+ table_close(pg_class, RowExclusiveLock);
+
+ InvokeObjectPostAlterHook(RelationRelationId, indexOid, 0);
+ RelationAssumeNewRelfilelocator(ind);
+
+ relation_close(ind, NoLock);
+
+ /* Make the relfilenode change visible. */
+ CommandCounterIncrement();
}
/*
--
2.55.0
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19686: Rolling back SET TABLESPACE + INSERT leads to index corruption
@ 2026-09-27 16:14 shihao zhong <zhong950419@gmail.com>
parent: Manu <manuelreyesbravo@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: shihao zhong @ 2026-09-27 16:14 UTC (permalink / raw)
To: Manu <manuelreyesbravo@gmail.com>; +Cc: Alexandre Felipe <o.alexandre.felipe@gmail.com>; pgsql-bugs@lists.postgresql.org, Alexander Lakhin <exclusion@gmail.com>
Hi Manu,
I think alexandre already had a fix but in a different thread. Maybe
you folks can merge two threads together?
https://commitfest.postgresql.org/patch/7312/
Thanks,
Shihao
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19686: Rolling back SET TABLESPACE + INSERT leads to index corruption
@ 2026-09-27 19:05 Manu <manuelreyesbravo@gmail.com>
parent: shihao zhong <zhong950419@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Manu @ 2026-09-27 19:05 UTC (permalink / raw)
To: Shihao Zhong <zhong950419@gmail.com>; pgsql-bugs@lists.postgresql.org; +Cc: Alexander Lakhin <exclusion@gmail.com>; Alexandre Felipe <o.alexandre.felipe@gmail.com>
On 2026-09-27, Shihao Zhong wrote:
> I think alexandre already had a fix but in a different thread. Maybe
> you folks can merge two threads together?
> https://commitfest.postgresql.org/patch/7312/
Thanks a lot for the pointer -- I hadn't come across #7312 when I
searched before posting, so I appreciate you connecting the two. I'll
take a proper look at Alexandre's patch and follow up on that thread so
the discussion stays in one place.
Manu
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19686: Rolling back SET TABLESPACE + INSERT leads to index corruption
@ 2026-09-27 20:35 Michael Paquier <michael@paquier.xyz>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 1 reply; 7+ messages in thread
From: Michael Paquier @ 2026-09-27 20:35 UTC (permalink / raw)
To: exclusion@gmail.com; pgsql-bugs@lists.postgresql.org; +Cc: Peter Geoghegan <pg@bowt.ie>
On Sun, Sep 13, 2026 at 04:00:01PM +0000, PG Bug reporting form wrote:
> triggers:
> 2026-09-13 18:09:42.598 EEST|postgres|regression|6aa6bcb6.297fd6|LOG:
> statement: INSERT INTO t SELECT i FROM generate_series(1, 4000) i;
> TRAP: failed Assert("_bt_posting_valid(itup)"), File: "nbtdedup.c", Line:
> 900, PID: 2719702
> ExceptionalCondition at assert.c:51:13
> _bt_form_posting at nbtdedup.c:905:7
> _bt_dedup_finish_pending at nbtdedup.c:584:11
> _bt_dedup_pass at nbtdedup.c:173:15
> _bt_delete_or_dedup_one_page at nbtinsert.c:2827:3
> _bt_findinsertloc at nbtinsert.c:918:4
> _bt_doinsert at nbtinsert.c:261:16
> btinsert at nbtree.c:219:11
> index_insert at indexam.c:231:9
>
> The assertion failure is reproduced starting from 0d861bbb7.
Err, Peter?
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../arl99XV0LOps1dT7@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19686: Rolling back SET TABLESPACE + INSERT leads to index corruption
@ 2026-09-28 04:13 Peter Geoghegan <pg@bowt.ie>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 0 replies; 7+ messages in thread
From: Peter Geoghegan @ 2026-09-28 04:13 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
On Sun, Sep 27, 2026 at 4:35 PM Michael Paquier <michael@paquier.xyz> wrote:
> > The assertion failure is reproduced starting from 0d861bbb7.
>
> Err, Peter?
The problem has nothing to do with nbtree deduplication -- Alexander
recreated the same problem with a GiST index.. It just so happens that
deduplication has assertions that tend to fail when the same TID is
stored multiple times (e.g., due to a recovery problem).
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-09-28 04:13 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-13 16:00 BUG #19686: Rolling back SET TABLESPACE + INSERT leads to index corruption PG Bug reporting form <noreply@postgresql.org>
2026-09-15 08:59 ` Alexandre Felipe <o.alexandre.felipe@gmail.com>
2026-09-27 16:11 ` Manu <manuelreyesbravo@gmail.com>
2026-09-27 16:14 ` shihao zhong <zhong950419@gmail.com>
2026-09-27 19:05 ` Manu <manuelreyesbravo@gmail.com>
2026-09-27 20:35 ` Michael Paquier <michael@paquier.xyz>
2026-09-28 04:13 ` Peter Geoghegan <pg@bowt.ie>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox