英文:
How to fix PostgreSQL errors telling tid from new index tuple overlaps with invalid duplicate tuple?
问题
当从Spring-Boot应用程序向我的某个具有多个索引的表中插入新行时，有时会出现以下错误：
ERROR: table tid from new index tuple (952110,19) overlaps with invalid duplicate tuple at offset 22 of block 31693 in index "bar_index"
ERROR: table tid from new index tuple (953430,24) overlaps with invalid duplicate tuple at offset 22 of block 31693 in index "bar_index"
ERROR: table tid from new index tuple (953440,39) overlaps with invalid duplicate tuple at offset 23 of block 31693 in index "bar_index"
ERROR: table tid from new index tuple (952368,27) overlaps with invalid duplicate tuple at offset 76 of block 55849 in index "foo_index"
ERROR: table tid from new index tuple (952368,32) overlaps with invalid duplicate tuple at offset 76 of block 55849 in index "foo_index"
ERROR: table tid from new index tuple (953046,13) overlaps with invalid duplicate tuple at offset 12 of block 49008 in index "foo_index"
所有这些错误都遵循一个警告 SQL Error: 0, SQLState: XX002，根据PostgreSQL文档，这意味着 index_corrupted。
服务器版本为 Ubuntu 13.9-1.pgdg22.04+1。
我应该采取什么措施来修复这个问题？
英文:
When inserting new rows to one of my tables (having multiple indexes) from a Spring-Boot application, I sometimes get:
ERROR: table tid from new index tuple (952367,9) overlaps with invalid duplicate tuple at offset 12 of block 49008 in index "foo_index"
ERROR: table tid from new index tuple (952110,19) overlaps with invalid duplicate tuple at offset 22 of block 31693 in index "bar_index"
ERROR: table tid from new index tuple (953430,24) overlaps with invalid duplicate tuple at offset 22 of block 31693 in index "bar_index"
ERROR: table tid from new index tuple (953440,39) overlaps with invalid duplicate tuple at offset 23 of block 31693 in index "bar_index"
ERROR: table tid from new index tuple (952368,27) overlaps with invalid duplicate tuple at offset 76 of block 55849 in index "foo_index"
ERROR: table tid from new index tuple (952368,32) overlaps with invalid duplicate tuple at offset 76 of block 55849 in index "foo_index"
ERROR: table tid from new index tuple (953046,13) overlaps with invalid duplicate tuple at offset 12 of block 49008 in index "foo_index"
All these follow a warning SQL Error: 0, SQLState: XX002, which means index_corrupted according to the PostgreSQL docs.
The server version is Ubuntu 13.9-1.pgdg22.04+1.
What can I do to fix it?
答案1
得分: 0
"Ran REINDEX (VERBOSE) TABLE CONCURRENTLY my_table;. It took ~30 minutes. (The table has ~30M rows.) So far, the errors did not reappear."
英文:
Ran REINDEX (VERBOSE) TABLE CONCURRENTLY my_table;. It took ~30 minutes. (The table has ~30M rows.) So far, the errors did not reappear.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论