Corrupt Index

Started by Jorel Casalabout 4 years ago2 messagesgeneral
Jump to latest
#1Jorel Casal
jcasal@ixl.com

Hi All, has anyone experienced corrupted unique indexes?

During a Patroni leader switchover, we had a situation where a unique index
stopped "working", the index did not have records
that were in fact in the table and the table had duplicate records which
should have been prevented by said unique index.

Fixing the duplicate records and reindexing seems to have fixed the
problems, but I would like to be aware of it next time.

Is there a way to test for index corruption? Anyone else had this issue
before?

Thanks
-Jorel

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Jorel Casal (#1)
Re: Corrupt Index

Jorel Casal <jcasal@ixl.com> writes:

During a Patroni leader switchover, we had a situation where a unique index
stopped "working", the index did not have records
that were in fact in the table and the table had duplicate records which
should have been prevented by said unique index.

If the index was on a string type (text/varchar), the most likely
explanation is a switch to a different OS version with inconsistent
collation rules. See

https://wiki.postgresql.org/wiki/Locale_data_changes

regards, tom lane