HackerNews中文版

最近，我在PostgreSQL上遇到了一次独特的索引失效问题。需要说明的是，这并非Postgres的错，而是我的问题。幸运的是，这个问题出现在了Staging环境而非Live环境，但以下内容可能会引起其他PostgreSQL用户的兴趣。在写入一组数据和依赖ON CONFLICT来更新/插入行的下一组数据之间（因此完全依赖于维护完整性的唯一索引），我切换了Docker镜像，以便内置一些工具。最初的镜像是基于alpine/musl的。后来的是基于Debian/Glib的。据我所知，字节排序规则有所不同，索引操作未能找到它应该找到的一组字节。如果我进行了转储/恢复操作，就不会出现问题，但我只是在现有的存储卷上切换了镜像。哎呀。从表面上看，索引是活动的，并且处于强制执行状态。然而，REINDEX操作失败了，因为存在重复项！对我来说，我修复了重复项，进行了REINDEX操作，然后进行了转储-恢复操作。但考虑到有多少备份机制依赖于存储卷快照，并且在恢复后进行全局重新索引可以捕获此类问题，如果这对任何人来说是一个潜在的问题，那么这一点值得注意。我在此欢迎即将到来的批评浪潮，但我会忽略它，纯粹是为了发布这篇文章，供其他人参考，以及那些可能犯了和我一样错误的人。

查看原文

I recently had a unique index failure on PostgreSQL. To be clear it wasn't Postgres' fault - it was mine.Thankfully it reared it's head on Staging not Live, but here's something other PostgreSQL users might find interesting.In between writing one set of data, and the next which relies on ON CONFLICT to update/insert rows, thus fully relying on the unique index holding the integrity, I switched docker image, to get some tooling built in.The original was alpine/musl based. The subsequent was Debian/Glib based.As far as I can tell the byte collation is different and the index operations just didn't find one set of bytes where it ought to be.If I had dumped/restored - it wouldn't have been a problem, but I just flipped the image on the existing storage volume. oops.On the surface, the index was active, and in an enforcing state.a REINDEX failed however, there were dupes!For me, I fixed the dupes, REINDEXed, then dump-restored.But worth bearing in mind given how many backup mechanisms rely on storage volume snapshots, and a global reindex after restore will catch these types of issues if that's a potential for anyone.I hereby invite the forthcoming tsunami of criticism, that I will ignore, purely so i can post this for others to find interest, and who might have done the same thing as me.

Postgres 上的唯一索引失败 – 我的错