Thread: Strangeness with UNIQUE indexes and UTF-8

Hi,

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.

I just had this happen on our user table and it somehow made it so
that Postgres returned no results for *any* SELECT ... FROM x WHERE
unique_col = 'x', which unfortunately meant no one could login to our
service.

I had to:

SET enable_indexscan = off;
SET enable_bitmapscan = off;

And then the data was returned properly. I thought maybe the index was
corrupt somehow, so I tried to reindex the unique index, which failed
because "nur" was present twice.

I modified the value in that column by the primary key (which is an
integer), and that allowed me to reindex, after which queries against
the column started working properly again.

My collation settings:

 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

I've had this happen before on a different table with cyerrlic UTF-8
input, but didn't really have much to go on debugging wise.

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.

Regards,
Omar

On Sun, 2021-06-06 at 03:54 -0700, Omar Kilani 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.
> 
> I just had this happen on our user table and it somehow made it so
> that Postgres returned no results for *any* SELECT ... FROM x WHERE
> unique_col = 'x', which unfortunately meant no one could login to our
> service.
> 
> I had to:
> 
> SET enable_indexscan = off;
> SET enable_bitmapscan = off;
> 
> And then the data was returned properly.

Sounds like data corruption.
REINDEX the index and see if that fixes the problem.
Try to figure out the cause (bad hardware?).

Yours,
Laurenz Albe

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

Omar Kilani <omar.kilani@gmail.com> writes:
> 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.

If it's that old, it's likely also survived multiple OS upgrades.
It seems clear that this index has been corrupt for awhile, and
I'm wondering whether the corruption was brought on by an OS
locale change.  There's useful info at

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

            regards, tom lane

I was able to restore a snapshot where the database was fully consistent.

2021-06-06 14:52:34.748 UTC [0/48529] LOG:  database system was
interrupted while in recovery at log time 2021-06-06 06:57:27 UTC
2021-06-06 14:52:34.748 UTC [0/48529] HINT:  If this has occurred more
than once some data might be corrupted and you might need to choose an
earlier recovery target.
2021-06-06 14:52:40.847 UTC [0/48529] LOG:  database system was not
properly shut down; automatic recovery in progress
2021-06-06 14:52:40.856 UTC [0/48529] LOG:  invalid record length at
8849/32000098: wanted 24, got 0
2021-06-06 14:52:40.856 UTC [0/48529] LOG:  redo is not required
2021-06-06 14:52:40.865 UTC [0/48529] LOG:  checkpoint starting:
end-of-recovery immediate
2021-06-06 14:52:40.909 UTC [0/48529] LOG:  checkpoint complete: wrote
0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled;
write=0.006 s, sync=0.001 s, total=0.050 s; sync files=0,
longest=0.000 s, average=0.000 s; distance=0 kB, estimate=0 kB
2021-06-06 14:52:40.964 UTC [0/48527] LOG:  database system is ready
to accept connections

I'm running pg_verify_checksums on the cluster, but the database is
many TB so it'll be a bit.

I missed one of your questions before -- no, it wasn't created with
CREATE INDEX CONCURRENTLY. That index was created by 11.2's pg_restore
roughly 2 years ago.

On Sun, Jun 6, 2021 at 6:53 AM Omar Kilani <omar.kilani@gmail.com> wrote:
>
> I just remembered, I have… many… snapshots of the on disk data prior to starting 11.12.
>
> It should be possible to start at a specific point in time with the index in the state it was in prior to the insert.
>
> How do I prove or disprove… hardware issues?
>
> Also… I ran the select on 3 of our standby servers and they all had the same issue. Presumably the index would be
“corrupt”in the same way across multiple very different machines from WAL apply? 
>
> On Sun, Jun 6, 2021 at 6:41 AM Omar Kilani <omar.kilani@gmail.com> wrote:
>>
>> 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
everytime 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
timessince 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
INSERThappened. 
>>
>> 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

Hey Tom,

The database was pg_dump'ed out of 10.4 and pg_restore'd into 11.2 on
a RHEL 7.x machine.

The only other upgrade has been to RHEL 8.x. So the locale data change
might have changed something -- thanks for that information.

We've seen this issue on a different table before upgrading to RHEL
8.x, though. And only on that table, because it's user-generated and
gets a bunch of Unicode data input into a UNIQUE index.

I'm not saying the index isn't corrupt as in something's not wrong
with it. I'm saying that during normal Postgres operation the index
has somehow got itself into this state, and I'm fairly sure it's not
the hardware.

Thanks again.

Regards,
Omar

On Sun, Jun 6, 2021 at 8:08 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Omar Kilani <omar.kilani@gmail.com> writes:
> > 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.
>
> If it's that old, it's likely also survived multiple OS upgrades.
> It seems clear that this index has been corrupt for awhile, and
> I'm wondering whether the corruption was brought on by an OS
> locale change.  There's useful info at
>
> https://wiki.postgresql.org/wiki/Locale_data_changes
>
>                         regards, tom lane

Hmmm.

Is it possible that in some version of 11.x, the corrupt index stopped
"working"? As in, yes, it may have been corrupt but still returned
data on version 11.y, whereas on version 11.z it's no longer working
and returns nothing?

David mentions that change in 11.11...?

I guess I can try some older versions of 11.x on this cluster for
completeness' sake.

Regards,
Omar

On Sun, Jun 6, 2021 at 8:18 AM Omar Kilani <omar.kilani@gmail.com> wrote:
>
> Hey Tom,
>
> The database was pg_dump'ed out of 10.4 and pg_restore'd into 11.2 on
> a RHEL 7.x machine.
>
> The only other upgrade has been to RHEL 8.x. So the locale data change
> might have changed something -- thanks for that information.
>
> We've seen this issue on a different table before upgrading to RHEL
> 8.x, though. And only on that table, because it's user-generated and
> gets a bunch of Unicode data input into a UNIQUE index.
>
> I'm not saying the index isn't corrupt as in something's not wrong
> with it. I'm saying that during normal Postgres operation the index
> has somehow got itself into this state, and I'm fairly sure it's not
> the hardware.
>
> Thanks again.
>
> Regards,
> Omar
>
> On Sun, Jun 6, 2021 at 8:08 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > Omar Kilani <omar.kilani@gmail.com> writes:
> > > 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.
> >
> > If it's that old, it's likely also survived multiple OS upgrades.
> > It seems clear that this index has been corrupt for awhile, and
> > I'm wondering whether the corruption was brought on by an OS
> > locale change.  There's useful info at
> >
> > https://wiki.postgresql.org/wiki/Locale_data_changes
> >
> >                         regards, tom lane

On Sun, Jun 6, 2021 at 5:19 PM Omar Kilani <omar.kilani@gmail.com> wrote:
>
> Hey Tom,
>
> The database was pg_dump'ed out of 10.4 and pg_restore'd into 11.2 on
> a RHEL 7.x machine.
>
> The only other upgrade has been to RHEL 8.x. So the locale data change
> might have changed something -- thanks for that information.

There is no might -- if you upgraded from RHEL 7 to RHEL 8 without
doing a reindex or a dump/reload there, you are pretty much guaranteed
to have corrupt text indexes from that. Regardless of PostgreSQL
versions, this was about the RHEL upgrade not the Postgres one.



> We've seen this issue on a different table before upgrading to RHEL
> 8.x, though. And only on that table, because it's user-generated and
> gets a bunch of Unicode data input into a UNIQUE index.

This indicates you may have more than one problem.

But that doesn't mean it's not both, sadly.


-- 
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

Hey Magnus,

Hmmm, okay -- that's unfortunate. :)

I apparently wrote a script in 2017 to find duplicates from this issue
on the other table and fix them up. Maybe a similar locale thing
happened back then?

Anyway, for what it's worth:

Checksum scan completed
Data checksum version: 1
Files scanned:  7068
Blocks scanned: 524565247
Bad checksums:  0

Regards,
Omar

On Sun, Jun 6, 2021 at 9:15 AM Magnus Hagander <magnus@hagander.net> wrote:
>
> On Sun, Jun 6, 2021 at 5:19 PM Omar Kilani <omar.kilani@gmail.com> wrote:
> >
> > Hey Tom,
> >
> > The database was pg_dump'ed out of 10.4 and pg_restore'd into 11.2 on
> > a RHEL 7.x machine.
> >
> > The only other upgrade has been to RHEL 8.x. So the locale data change
> > might have changed something -- thanks for that information.
>
> There is no might -- if you upgraded from RHEL 7 to RHEL 8 without
> doing a reindex or a dump/reload there, you are pretty much guaranteed
> to have corrupt text indexes from that. Regardless of PostgreSQL
> versions, this was about the RHEL upgrade not the Postgres one.
>
>
>
> > We've seen this issue on a different table before upgrading to RHEL
> > 8.x, though. And only on that table, because it's user-generated and
> > gets a bunch of Unicode data input into a UNIQUE index.
>
> This indicates you may have more than one problem.
>
> But that doesn't mean it's not both, sadly.
>
>
> --
>  Magnus Hagander
>  Me: https://www.hagander.net/
>  Work: https://www.redpill-linpro.com/

On 06/06/21 11:08, Omar Kilani wrote:
> I'm running pg_verify_checksums on the cluster, but the database is
> many TB so it'll be a bit.

Index corruption because of a locale change would not be the sort of thing
checksums would detect. Entries would be put into the index in the correct
order according to the old collation. The same entries can be still there,
intact, just fine according to the checksums, only the new collation would
have put them in a different order. Index search algorithms that are fast,
because they assume the entries to be correctly ordered, will skip regions
of the index where the desired key "couldn't possibly be", and if that's
where the old ordering put it, it won't be found.

Regards,
-Chap

On Sun, Jun 06, 2021 at 03:54:48AM -0700, Omar Kilani wrote:
> 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.

In addition to the other issues, this is racy.

You 1) check if a key exists, and if not then 2) INSERT (or maybe you UPDATE if
it did exist).

https://en.wikipedia.org/wiki/Time-of-check_to_time-of-use

Maybe you'll say that "this process only runs once", but it's not hard to
imagine that might be violated.  For example, if you restart a multi-threaded
process, does the parent make sure that the child processes die before itself
dying?  Do you create a pidfile, and do you make sure the children are dead
before removing the pidfile ?

The right way to do this since v9.6 is INSERT ON CONFLICT, which is also more
efficient in a couple ways.

-- 
Justin

I mean, maybe it's because I've been awake since... 7am yesterday, but
it seems to me that if Postgres fails catastrophically silently (and I
would say "it looks like all your data in this table disappeared
because of some arcane locale / btree issue that no one except Tom
Lane even knows exists" -- see the replies about hardware issues and
ON CONFLICT as an example) -- then maybe that is... not good, and
Postgres shouldn't do that?

Not only that, it's only indices which have non-ASCII or whatever in
them that silently fail, so it's like 95% of your indices work just
fine, but the ones that don't... look fine. They're not corrupt on
disk, they have their full size, etc.

How is anyone supposed to know about this issue? I've been using
Postgres since 1999, built the Postgres website, worked with Neil and
Gavin on Postgres, submitted patches to Postgres and various
Postgres-related projects, and this is the first time I've become
aware of it. I mean, maybe I'm dumb, and... fine. But your average
user is going to have no idea about this.

Why can't some "locale signature" or something be encoded into the
index so Postgres can at least warn you? Or not use the messed up
index altogether instead of silently returning no data?

On Sun, Jun 6, 2021 at 2:06 PM Omar Kilani <omar.kilani@gmail.com> wrote:
>
> We do use ON CONFLICT… it doesn’t work because the index is both “good” and “bad” at the same time.
>
> On Sun, Jun 6, 2021 at 2:03 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
>>
>> On Sun, Jun 06, 2021 at 03:54:48AM -0700, Omar Kilani wrote:
>> > 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.
>>
>> In addition to the other issues, this is racy.
>>
>> You 1) check if a key exists, and if not then 2) INSERT (or maybe you UPDATE if
>> it did exist).
>>
>> https://en.wikipedia.org/wiki/Time-of-check_to_time-of-use
>>
>> Maybe you'll say that "this process only runs once", but it's not hard to
>> imagine that might be violated.  For example, if you restart a multi-threaded
>> process, does the parent make sure that the child processes die before itself
>> dying?  Do you create a pidfile, and do you make sure the children are dead
>> before removing the pidfile ?
>>
>> The right way to do this since v9.6 is INSERT ON CONFLICT, which is also more
>> efficient in a couple ways.
>>
>> --
>> Justin

Omar Kilani <omar.kilani@gmail.com> writes:
> How is anyone supposed to know about this issue?

We're working on infrastructure to help detect OS locale changes,
but it's not shipped yet.

            regards, tom lane

On Sun, Jun 6, 2021 at 11:20 PM Omar Kilani <omar.kilani@gmail.com> wrote:
>
> I mean, maybe it's because I've been awake since... 7am yesterday, but
> it seems to me that if Postgres fails catastrophically silently (and I
> would say "it looks like all your data in this table disappeared
> because of some arcane locale / btree issue that no one except Tom
> Lane even knows exists" -- see the replies about hardware issues and
> ON CONFLICT as an example) -- then maybe that is... not good, and
> Postgres shouldn't do that?

It is most definitely not an "arcane issue no one except Tom lane even
knows exists". I would assume most people who work with consulting or
support around PostgreSQL know it exists, because some of their
customers have hit it :/

I think it's more in the other direction -- those people are more
likely to dismiss that issue as "the person reporting this will
already have checked this, it must be something else"...



> Not only that, it's only indices which have non-ASCII or whatever in
> them that silently fail, so it's like 95% of your indices work just
> fine, but the ones that don't... look fine. They're not corrupt on
> disk, they have their full size, etc.

No it's not. ASCII will also fail in many cases. Did you read the page
that you were linked to? It even includes an example of why ASCII
cases will also fail.

It's only non-text indexes that are "safe".


> How is anyone supposed to know about this issue? I've been using
> Postgres since 1999, built the Postgres website, worked with Neil and
> Gavin on Postgres, submitted patches to Postgres and various
> Postgres-related projects, and this is the first time I've become
> aware of it. I mean, maybe I'm dumb, and... fine. But your average
> user is going to have no idea about this.

This problem has been around before, just usually doesn't affect the
English locale. Surely if you've spent that much time around Postgres
and in the community you must've heard about it before?

And this particular issue has been written about numerous times, which
has been published through the postgres website and blog aggregators.

It is definitely a weakness in how PostgreSQL does things, but it's a
pretty well known weakness by now.


> Why can't some "locale signature" or something be encoded into the
> index so Postgres can at least warn you? Or not use the messed up
> index altogether instead of silently returning no data?

If you use ICU for your text indexes, it does exactly that. The page
at https://www.postgresql.org/docs/13/sql-altercollation.html shows
you examples of what wouldh appen in that case. (This page also
documents that there is no version tracking for the built-in
collections, but I definitely agree that's pretty well hidden-away by
being on the reference page of alter collation..)

-- 
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/