On Sun, Jun 6, 2021 at 11:20 PM Omar Kilani 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
Omar Kilani 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
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" -
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 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
> > table
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
Hey Chap,
Yeah, I understand. Just ruling out the bad hardware scenario.
Plus the next person to Google this will hopefully stumble upon this
thread. :)
Regards,
Omar
On Sun, Jun 6, 2021 at 9:36 AM Chapman Flack wrote:
> On 06/06/21 11:08, Omar Kilani wrote:
> > I'm running pg_verify_checksum
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 accor
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 scanne
On Sun, Jun 6, 2021 at 5:19 PM Omar Kilani 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.
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
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 RH
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 mig
Omar Kilani 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 cle
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 se
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_
On Sun, 6 Jun 2021 at 22:55, 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.
It w
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.
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
18 matches
Mail list logo
