Re: Strangeness with UNIQUE indexes and UTF-8 - Mailing list pgsql-hackers

Hey David,

Hmmm… it wasn’t init on 11.x.

This is a very old database (2004) that has moved forward via pg_upgrade. I think we did a pg_dump and pg_restore every time we hit some major incompatibility like float vs integer date times.

The current DB started as a pg_restore into 10.x. Then was pg_upgrade’d to 11.2. Has been minor upgraded a bunch of times since and we upgraded to 11.12… just before this happened.

As in, we just restarted our cluster on 11.12. Everything was working fine (and the index was working) and then the INSERT happened.

I have checksums on and I did a VACUUM on the table just before the REINDEX.

I’m 99.99999% confident the hardware isn’t bad.

The only time we’ve seen this is with Unicode input.

Regards,

Omar

On Sun, Jun 6, 2021 at 4:59 AM David Rowley <dgrowleyml@gmail.com> wrote:

On Sun, 6 Jun 2021 at 22:55, Omar Kilani <omar.kilani@gmail.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