Re: Strangeness with UNIQUE indexes and UTF-8

On Sun, 6 Jun 2021 at 22:55, Omar Kilani <omar(dot)kilani(at)gmail(dot)com> wrote:
> There seems to be a weird bug in Postgres (last tested 11.12) where it
> allows an INSERT into a table with a UNIQUE / UNIQUE CONSTRAINT index
> on a TEXT/VARCHAR when there's already a value present in that index,
> but only for UTF-8 input.

It would be good to know a bit about the history of this instance.
Was the initdb done on 11.12? Or some other 11.x version? Or was this
instance pg_upgraded from some previous major version?

There was a bug fixed in 11.11 that caused CREATE INDEX CONCURRENTLY
possibly to miss rows that were inserted by a prepared transaction.
Was this index created with CREATE INDEX CONCURRENTLY?

> What I sort of don't get is... before we insert anything into these
> tables, we always check to see if a value already exists. And Postgres
> must be returning no results for some reason. So it goes to insert a
> duplicate value which somehow succeeds despite the unique index, but
> then a reindex says it's a duplicate. Pretty weird.

That does not seem that weird to me. If the index is corrupt and
fails to find the record you're searching for using a scan of that
index, then it seems pretty likely that the record would also not be
found in the index when doing the INSERT.

The reindex will catch the problem because it uses the heap as the
source of truth to build the new index. It simply sounds like there
are two records in the heap because a subsequent one was added and a
corrupt index didn't find the original record either because it was
either missing from the index or because the index was corrupt in some
way that the record was just not found.

David