From: Manu <manuelreyesbravo@gmail.com>
To: Alexandre Felipe <o.alexandre.felipe@gmail.com>,
 pgsql-bugs@lists.postgresql.org
Cc: Alexander Lakhin <exclusion@gmail.com>
Subject: Re: BUG #19686: Rolling back SET TABLESPACE + INSERT leads to index
 corruption
Date: Sun, 27 Sep 2026 13:11:13 -0300
Message-ID: <179052547394.318321.10043901456417388246@gmail.com>
In-Reply-To: 
 <CAE8JnxOE2rerDxuapP-14LcR+8HdD-8+1-PQ644VNQpsjkXK-w@mail.gmail.com>
References: 
 <CAE8JnxOE2rerDxuapP-14LcR+8HdD-8+1-PQ644VNQpsjkXK-w@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============0682097667753335172=="
Archived-At: 
 <https://www.postgresql.org/message-id/179052547394.318321.10043901456417388246%40gmail.com>
Precedence: bulk

--===============0682097667753335172==
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: 7bit

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

--===============0682097667753335172==
Content-Type: text/x-patch
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="v1-0001-Fix-index-corruption-SET-TABLESPACE-rollback.patch"
MIME-Version: 1.0

RnJvbSAzYzU2NjJmZjI2OGFjMmIyZTgwMzEzMGI3MTk5NTE3MTYwZjVhNGU5IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBNYW51IDxtYW51ZWxyZXllc2JyYXZvQGdtYWlsLmNvbT4KRGF0
ZTogU3VuLCAyNyBTZXAgMjAyNiAxMzowODozNiAtMDMwMApTdWJqZWN0OiBbUEFUQ0hdIEZpeCBp
bmRleCBjb3JydXB0aW9uIGFmdGVyIHJvbGxpbmcgYmFjayBBTFRFUiBUQUJMRSBTRVQKIFRBQkxF
U1BBQ0UKCkFMVEVSIFRBQkxFIC4uLiBTRVQgVEFCTEVTUEFDRSByZXdyaXRlcyBhIHRhYmxlJ3Mg
aGVhcCB0byBhIG5ldwpyZWxmaWxlbm9kZSBidXQgZGVsaWJlcmF0ZWx5IGxlYXZlcyB0aGUgdGFi
bGUncyBpbmRleGVzIG9uIHRoZWlyCmV4aXN0aW5nIHJlbGZpbGVub2Rlcy4gIFRoZSB0d28gdGhl
biByb2xsIGJhY2sgYnkgZGlmZmVyZW50IG1lY2hhbmlzbXM6Cm9uIGFib3J0IHRoZSBoZWFwJ3Mg
bmV3IGZpbGUgaXMgZGlzY2FyZGVkLCBzbyB0aGUgaGVhcCBUSURzIGNvbnN1bWVkIGJ5CnJvd3Mg
aW5zZXJ0ZWQgYWZ0ZXIgdGhlIFNFVCBUQUJMRVNQQUNFIGJlY29tZSBmcmVlIGFnYWluLCB3aGls
ZSB0aGUKaW5kZXggZW50cmllcyBmb3IgdGhvc2Ugcm93cyB3ZXJlIHdyaXR0ZW4gdG8gdGhlIHVu
Y2hhbmdlZCBpbmRleCBmaWxlcwphbmQgc3Vydml2ZSB0aGUgYWJvcnQuICBBIGxhdGVyIGluc2Vy
dCBjYW4gcmV1c2UgYSBmcmVlZCBoZWFwIFRJRCwKbGVhdmluZyB0d28gaW5kZXggZW50cmllcyB0
aGF0IHBvaW50IGF0IHRoZSBzYW1lIGxpdmUgaGVhcCB0dXBsZS4gIFRoaXMKc3VyZmFjZXMgYXMg
YSBfYnRfcG9zdGluZ192YWxpZCBhc3NlcnRpb24gZmFpbHVyZSBpbiBuYnRyZWUKZGVkdXBsaWNh
dGlvbiwgYW5kIGFzIGR1cGxpY2F0ZSByb3dzIHRocm91Z2ggYW4gaW5kZXgtb25seSBzY2FuIGlu
IGdpc3QuCgpNYWtlIHRoZSBpbmRleGVzIHNoYXJlIHRoZSBoZWFwJ3MgcmV3cml0ZTogYWZ0ZXIg
bW92aW5nIHRoZSBoZWFwLCBnaXZlCmVhY2ggb2YgdGhlIHRhYmxlJ3MgaW5kZXhlcyBhIG5ldyBy
ZWxmaWxlbnVtYmVyIGJ5IGNvcHlpbmcgaXQgd2l0aGluIGl0cwpvd24gdGFibGVzcGFjZSwgc28g
dGhhdCBhbiBhYm9ydCBkaXNjYXJkcyB0aGUgbmV3IGhlYXAgYW5kIGluZGV4IGZpbGVzCnRvZ2V0
aGVyIGFuZCBhIGNvbW1pdCBrZWVwcyB0aGVtIHRvZ2V0aGVyLgoKQnVnOiAjMTk2ODYKUmVwb3J0
ZWQtYnk6IEFsZXhhbmRlciBMYWtoaW4KLS0tCiBzcmMvYmFja2VuZC9jb21tYW5kcy90YWJsZWNt
ZHMuYyB8IDkzICsrKysrKysrKysrKysrKysrKysrKysrKysrKysrKysrCiAxIGZpbGUgY2hhbmdl
ZCwgOTMgaW5zZXJ0aW9ucygrKQoKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2NvbW1hbmRzL3Rh
YmxlY21kcy5jIGIvc3JjL2JhY2tlbmQvY29tbWFuZHMvdGFibGVjbWRzLmMKaW5kZXggMDI3NGQ4
OTJmMmUuLjkxOGQ1MTdhYzIzIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9jb21tYW5kcy90YWJs
ZWNtZHMuYworKysgYi9zcmMvYmFja2VuZC9jb21tYW5kcy90YWJsZWNtZHMuYwpAQCAtNjk1LDYg
KzY5NSw3IEBAIHN0YXRpYyB2b2lkIEFUUHJlcENoYW5nZVBlcnNpc3RlbmNlKEFsdGVyZWRUYWJs
ZUluZm8gKnRhYiwgUmVsYXRpb24gcmVsLAogc3RhdGljIHZvaWQgQVRQcmVwU2V0VGFibGVTcGFj
ZShBbHRlcmVkVGFibGVJbmZvICp0YWIsIFJlbGF0aW9uIHJlbCwKIAkJCQkJCQkJY29uc3QgY2hh
ciAqdGFibGVzcGFjZW5hbWUsIExPQ0tNT0RFIGxvY2ttb2RlKTsKIHN0YXRpYyB2b2lkIEFURXhl
Y1NldFRhYmxlU3BhY2UoT2lkIHRhYmxlT2lkLCBPaWQgbmV3VGFibGVTcGFjZSwgTE9DS01PREUg
bG9ja21vZGUpOworc3RhdGljIHZvaWQgQVRFeGVjU2V0VGFibGVTcGFjZU5ld0luZGV4UmVsZmls
ZW51bWJlcihPaWQgaW5kZXhPaWQsIExPQ0tNT0RFIGxvY2ttb2RlKTsKIHN0YXRpYyB2b2lkIEFU
RXhlY1NldFRhYmxlU3BhY2VOb1N0b3JhZ2UoUmVsYXRpb24gcmVsLCBPaWQgbmV3VGFibGVTcGFj
ZSk7CiBzdGF0aWMgdm9pZCBBVEV4ZWNTZXRSZWxPcHRpb25zKFJlbGF0aW9uIHJlbCwgTGlzdCAq
ZGVmTGlzdCwKIAkJCQkJCQkJQWx0ZXJUYWJsZVR5cGUgb3BlcmF0aW9uLApAQCAtMTc1MjAsOSAr
MTc1MjEsMTEgQEAgQVRFeGVjU2V0VGFibGVTcGFjZShPaWQgdGFibGVPaWQsIE9pZCBuZXdUYWJs
ZVNwYWNlLCBMT0NLTU9ERSBsb2NrbW9kZSkKIHsKIAlSZWxhdGlvbglyZWw7CiAJT2lkCQkJcmVs
dG9hc3RyZWxpZDsKKwljaGFyCQlyZWxraW5kOwogCVJlbEZpbGVOdW1iZXIgbmV3cmVsZmlsZW51
bWJlcjsKIAlSZWxGaWxlTG9jYXRvciBuZXdybG9jYXRvcjsKIAlMaXN0CSAgICpyZWx0b2FzdGlk
eGlkcyA9IE5JTDsKKwlMaXN0CSAgICpyZWx0YWJpZHhpZHMgPSBOSUw7CiAJTGlzdENlbGwgICAq
bGM7CiAKIAkvKgpAQCAtMTc1NDAsNiArMTc1NDMsNyBAQCBBVEV4ZWNTZXRUYWJsZVNwYWNlKE9p
ZCB0YWJsZU9pZCwgT2lkIG5ld1RhYmxlU3BhY2UsIExPQ0tNT0RFIGxvY2ttb2RlKQogCX0KIAog
CXJlbHRvYXN0cmVsaWQgPSByZWwtPnJkX3JlbC0+cmVsdG9hc3RyZWxpZDsKKwlyZWxraW5kID0g
cmVsLT5yZF9yZWwtPnJlbGtpbmQ7CiAJLyogRmV0Y2ggdGhlIGxpc3Qgb2YgaW5kZXhlcyBvbiB0
b2FzdCByZWxhdGlvbiBpZiBuZWNlc3NhcnkgKi8KIAlpZiAoT2lkSXNWYWxpZChyZWx0b2FzdHJl
bGlkKSkKIAl7CkBAIC0xNzU4Niw2ICsxNzU5MCwxMyBAQCBBVEV4ZWNTZXRUYWJsZVNwYWNlKE9p
ZCB0YWJsZU9pZCwgT2lkIG5ld1RhYmxlU3BhY2UsIExPQ0tNT0RFIGxvY2ttb2RlKQogCiAJUmVs
YXRpb25Bc3N1bWVOZXdSZWxmaWxlbG9jYXRvcihyZWwpOwogCisJLyoKKwkgKiBJZiB0aGlzIGlz
IGEgdGFibGUsIGNvbGxlY3QgaXRzIGluZGV4IGxpc3Qgbm93LCB3aGlsZSB0aGUgcmVsYXRpb24g
aXMKKwkgKiBzdGlsbCBvcGVuLCBzbyB3ZSBjYW4gZ2l2ZSBlYWNoIGluZGV4IGEgZnJlc2ggcmVs
ZmlsZW51bWJlciBiZWxvdy4KKwkgKi8KKwlpZiAocmVsa2luZCA9PSBSRUxLSU5EX1JFTEFUSU9O
IHx8IHJlbGtpbmQgPT0gUkVMS0lORF9NQVRWSUVXKQorCQlyZWx0YWJpZHhpZHMgPSBSZWxhdGlv
bkdldEluZGV4TGlzdChyZWwpOworCiAJcmVsYXRpb25fY2xvc2UocmVsLCBOb0xvY2spOwogCiAJ
LyogTWFrZSBzdXJlIHRoZSByZWx0YWJsZXNwYWNlIGNoYW5nZSBpcyB2aXNpYmxlICovCkBAIC0x
NzU5OSw2ICsxNzYxMCw4OCBAQCBBVEV4ZWNTZXRUYWJsZVNwYWNlKE9pZCB0YWJsZU9pZCwgT2lk
IG5ld1RhYmxlU3BhY2UsIExPQ0tNT0RFIGxvY2ttb2RlKQogCiAJLyogQ2xlYW4gdXAgKi8KIAls
aXN0X2ZyZWUocmVsdG9hc3RpZHhpZHMpOworCisJLyoKKwkgKiBNb3ZpbmcgYSB0YWJsZSdzIGhl
YXAgYXNzaWducyBpdCBhIG5ldyByZWxmaWxlbm9kZSwgYnV0IGl0cyBpbmRleGVzIGFyZQorCSAq
IGRlbGliZXJhdGVseSBsZWZ0IGluIHBsYWNlIHdpdGggdGhlaXIgZXhpc3RpbmcgcmVsZmlsZW5v
ZGVzLiAgVGhhdCBtaXgKKwkgKiBpcyB1bnNhZmUgYWNyb3NzIGEgcm9sbGJhY2s6IGlmIHRoZSB0
cmFuc2FjdGlvbiBpbnNlcnRzIGludG8gdGhlIHRhYmxlCisJICogYWZ0ZXIgdGhpcyBhbmQgdGhl
biBhYm9ydHMsIHRoZSBoZWFwJ3MgbmV3IHJlbGZpbGVub2RlIGlzIGRpc2NhcmRlZCBhbmQKKwkg
KiBpdHMgZmlsZSByZXZlcnRzIHRvIHRoZSBwcmUtbW92ZSBjb250ZW50cywgZnJlZWluZyB0aGUg
VElEcyB1c2VkIGJ5IHRoZQorCSAqIGFib3J0ZWQgcm93czsgYnV0IHRoZSBtYXRjaGluZyBpbmRl
eCBlbnRyaWVzIHdlcmUgd3JpdHRlbiB0byB0aGUKKwkgKiB1bmNoYW5nZWQgaW5kZXggZmlsZXMg
YW5kIHN1cnZpdmUgdGhlIGFib3J0LiAgQSBsYXRlciBpbnNlcnQgY2FuIHJldXNlIGEKKwkgKiBm
cmVlZCBoZWFwIFRJRCwgbGVhdmluZyB0d28gaW5kZXggZW50cmllcyBwb2ludGluZyBhdCB0aGUg
c2FtZSBsaXZlIGhlYXAKKwkgKiB0dXBsZSAtLSBpbmRleCBjb3JydXB0aW9uIChidWcgIzE5Njg2
KS4gIEdpdmUgZWFjaCBpbmRleCBhIGZyZXNoCisJICogcmVsZmlsZW51bWJlciwgY29waWVkIHdp
dGhpbiBpdHMgb3duIHRhYmxlc3BhY2UsIHNvIGl0IHNoYXJlcyB0aGUgaGVhcCdzCisJICogbmV3
LXJlbGZpbGVub2RlIGZhdGU6IG9uIGFib3J0IHRoZSBuZXcgaGVhcCBhbmQgaW5kZXggZmlsZXMg
YXJlIGFsbAorCSAqIGRpc2NhcmRlZCB0b2dldGhlciwgYW5kIG9uIGNvbW1pdCB0aGV5IGFyZSBh
bGwga2VwdC4KKwkgKi8KKwlmb3JlYWNoKGxjLCByZWx0YWJpZHhpZHMpCisJCUFURXhlY1NldFRh
YmxlU3BhY2VOZXdJbmRleFJlbGZpbGVudW1iZXIobGZpcnN0X29pZChsYyksIGxvY2ttb2RlKTsK
KwlsaXN0X2ZyZWUocmVsdGFiaWR4aWRzKTsKK30KKworLyoKKyAqIEdpdmUgb25lIG9mIGEgdGFi
bGUncyBpbmRleGVzIGEgZnJlc2ggcmVsZmlsZW51bWJlciB3aXRoaW4gaXRzIGV4aXN0aW5nCisg
KiB0YWJsZXNwYWNlLCBjb3B5aW5nIHRoZSBjdXJyZW50IGluZGV4IGZpbGUgdG8gdGhlIG5ldyBy
ZWxmaWxlbnVtYmVyLgorICoKKyAqIEFURXhlY1NldFRhYmxlU3BhY2UoKSBjYWxscyB0aGlzIGZv
ciBlYWNoIGluZGV4IG9mIGEgdGFibGUgd2hvc2UgaGVhcCBpdCBoYXMKKyAqIGp1c3QgcmV3cml0
dGVuIHRvIGEgbmV3IHJlbGZpbGVub2RlLiAgVGhlIGluZGV4ZXMgbXVzdCBzaGFyZSB0aGF0IHJl
d3JpdGUncworICogdHJhbnNhY3Rpb25hbCBmYXRlLCBvdGhlcndpc2UgYW4gYWJvcnQgZGlzY2Fy
ZHMgdGhlIGhlYXAncyBuZXcgZmlsZSAoZnJlZWluZworICogaXRzIFRJRHMpIHdoaWxlIGxlYXZp
bmcgYmVoaW5kIGluZGV4IGVudHJpZXMgdGhhdCB3ZXJlIHdyaXR0ZW4gZHVyaW5nIHRoZQorICog
dHJhbnNhY3Rpb24sIHdoaWNoIGEgbGF0ZXIgaW5zZXJ0IGNhbiB0aGVuIGFsaWFzIC0tIGluZGV4
IGNvcnJ1cHRpb24gKGJ1ZworICogIzE5Njg2KS4gIFVubGlrZSBhIGZ1bGwgcmVpbmRleCB0aGlz
IG1lcmVseSBjb3BpZXMgdGhlIGV4aXN0aW5nIGluZGV4IGZpbGUsCisgKiBzbyB0aGUgYWRkZWQg
Y29zdCBzdGF5cyBjbG9zZSB0byB0aGF0IG9mIHRoZSBoZWFwIG1vdmUgaXRzZWxmLgorICovCitz
dGF0aWMgdm9pZAorQVRFeGVjU2V0VGFibGVTcGFjZU5ld0luZGV4UmVsZmlsZW51bWJlcihPaWQg
aW5kZXhPaWQsIExPQ0tNT0RFIGxvY2ttb2RlKQoreworCVJlbGF0aW9uCWluZDsKKwlSZWxGaWxl
TnVtYmVyIG5ld3JlbGZpbGVudW1iZXI7CisJUmVsRmlsZUxvY2F0b3IgbmV3cmxvY2F0b3I7CisJ
UmVsYXRpb24JcGdfY2xhc3M7CisJSGVhcFR1cGxlCXR1cGxlOworCUl0ZW1Qb2ludGVyRGF0YSBv
dGlkOworCUZvcm1fcGdfY2xhc3MgcmRfcmVsOworCisJaW5kID0gcmVsYXRpb25fb3BlbihpbmRl
eE9pZCwgbG9ja21vZGUpOworCisJLyogT25seSBwbGFpbiBpbmRleGVzIGhhdmUgc3RvcmFnZSB0
aGF0IGNhbiBob2xkIHRoZSBzdGFsZSBlbnRyaWVzLiAqLworCWlmIChpbmQtPnJkX3JlbC0+cmVs
a2luZCAhPSBSRUxLSU5EX0lOREVYIHx8CisJCSFSRUxLSU5EX0hBU19TVE9SQUdFKGluZC0+cmRf
cmVsLT5yZWxraW5kKSkKKwl7CisJCXJlbGF0aW9uX2Nsb3NlKGluZCwgTm9Mb2NrKTsKKwkJcmV0
dXJuOworCX0KKworCS8qIEFsbG9jYXRlIGEgbmV3IHJlbGZpbGVudW1iZXIgaW4gdGhlIGluZGV4
J3MgY3VycmVudCB0YWJsZXNwYWNlLiAqLworCW5ld3JlbGZpbGVudW1iZXIgPSBHZXROZXdSZWxG
aWxlTnVtYmVyKGluZC0+cmRfcmVsLT5yZWx0YWJsZXNwYWNlLCBOVUxMLAorCQkJCQkJCQkJCSAg
IGluZC0+cmRfcmVsLT5yZWxwZXJzaXN0ZW5jZSk7CisJbmV3cmxvY2F0b3IgPSBpbmQtPnJkX2xv
Y2F0b3I7CisJbmV3cmxvY2F0b3IucmVsTnVtYmVyID0gbmV3cmVsZmlsZW51bWJlcjsKKworCS8q
IENvcHkgdGhlIGluZGV4IGludG8gdGhlIG5ldyBmaWxlIGFuZCBzY2hlZHVsZSB0aGUgb2xkIG9u
ZSBmb3IgY2xlYW51cC4gKi8KKwlpbmRleF9jb3B5X2RhdGEoaW5kLCBuZXdybG9jYXRvcik7CisK
KwkvKiBVcGRhdGUgdGhlIHBnX2NsYXNzIHJvdzsgb25seSB0aGUgcmVsZmlsZW5vZGUgY2hhbmdl
cy4gKi8KKwlwZ19jbGFzcyA9IHRhYmxlX29wZW4oUmVsYXRpb25SZWxhdGlvbklkLCBSb3dFeGNs
dXNpdmVMb2NrKTsKKwl0dXBsZSA9IFNlYXJjaFN5c0NhY2hlTG9ja2VkQ29weTEoUkVMT0lELCBP
YmplY3RJZEdldERhdHVtKGluZGV4T2lkKSk7CisJaWYgKCFIZWFwVHVwbGVJc1ZhbGlkKHR1cGxl
KSkKKwkJZWxvZyhFUlJPUiwgImNhY2hlIGxvb2t1cCBmYWlsZWQgZm9yIGluZGV4ICV1IiwgaW5k
ZXhPaWQpOworCW90aWQgPSB0dXBsZS0+dF9zZWxmOworCXJkX3JlbCA9IChGb3JtX3BnX2NsYXNz
KSBHRVRTVFJVQ1QodHVwbGUpOworCXJkX3JlbC0+cmVsZmlsZW5vZGUgPSBuZXdyZWxmaWxlbnVt
YmVyOworCUNhdGFsb2dUdXBsZVVwZGF0ZShwZ19jbGFzcywgJm90aWQsIHR1cGxlKTsKKwlVbmxv
Y2tUdXBsZShwZ19jbGFzcywgJm90aWQsIElucGxhY2VVcGRhdGVUdXBsZUxvY2spOworCWhlYXBf
ZnJlZXR1cGxlKHR1cGxlKTsKKwl0YWJsZV9jbG9zZShwZ19jbGFzcywgUm93RXhjbHVzaXZlTG9j
ayk7CisKKwlJbnZva2VPYmplY3RQb3N0QWx0ZXJIb29rKFJlbGF0aW9uUmVsYXRpb25JZCwgaW5k
ZXhPaWQsIDApOworCVJlbGF0aW9uQXNzdW1lTmV3UmVsZmlsZWxvY2F0b3IoaW5kKTsKKworCXJl
bGF0aW9uX2Nsb3NlKGluZCwgTm9Mb2NrKTsKKworCS8qIE1ha2UgdGhlIHJlbGZpbGVub2RlIGNo
YW5nZSB2aXNpYmxlLiAqLworCUNvbW1hbmRDb3VudGVySW5jcmVtZW50KCk7CiB9CiAKIC8qCi0t
IAoyLjU1LjAKCg==

--===============0682097667753335172==--