[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: Corruption or wrong results with 14.10?
From: Jeremy Schneider <schneider () ardentperf ! com>
Date: 2023-11-24 20:18:01
Message-ID: CA+fnDAZufFS-4-6=O3L+qG9iFT8tm6BvtZXNnSm1dkJ8GciCkA () mail ! gmail ! com
[Download RAW message or body]
On Thu, Nov 23, 2023 at 7:34 AM Torsten Förtsch <tfoertsch123@gmail.com>
wrote:
> On Thu, Nov 23, 2023 at 2:29 PM Daniel Westermann (DWE) <
> daniel.westermann@dbi-services.com> wrote:
>
>> smrdbprod=# select crart_id, chemin, count(*) from smrr_mgr.formula group
>> by crart_id, chemin having count(*) > 1;
>> crart_id | chemin | count
>> -------------+--------+-------
>> 35054630000 | @ | 2
>> 4737310000 | @ | 2
>> 10632380000 | @ | 2
>> 14680880000 | @ | 2
>> 4627230000 | @ | 2
>> 10993780000 | @ | 2
>> ....
>>
>> I think I know what I have to do.
>>
>
> If you have moved the database from a system with glibc <2.28 to >=2.28,
> that could be the reason.
>
> https://wiki.postgresql.org/wiki/Collations
>
Just one note here... the reason can be _any_ operating systems move or
update. It can happen with ICU and it can happen with any version of glibc
(this is easily reproducible and we've seen it happen on production PG
deployments that didn't involve glibc 2.28)
glibc 2.28 has certainly been the most obvious and impactful case, so the
focus is understandable, but there's a bit of a myth that the problem is
only with glibc 2.28 (and not ICU or other glibc versions or data
structures other than indexes)
The only truly safe way to update an operating system under PosgreSQL is
with logical dump/load or logical replication, or continuing to compile and
use the identical older version of ICU from the old OS (if you use ICU). I
think the ICU folks are generally careful enough that it'll be unlikely for
compiler changes and new compiler optimizations to inadvertently change
collation on newer operating systems and build toolchains.
Ironically I just did a detailed talk on this topic at PASS Data Summit
last week, but unfortunately the recording probably won't be released for
awhile. 🙂
-Jeremy
--
http://about.me/jeremy_schneider
[Attachment #3 (text/html)]
<div dir="ltr"><div dir="ltr"><br></div><br><div class="gmail_quote"><div dir="ltr" \
class="gmail_attr">On Thu, Nov 23, 2023 at 7:34 AM Torsten Förtsch <<a \
href="mailto:tfoertsch123@gmail.com">tfoertsch123@gmail.com</a>> \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div \
dir="ltr">On Thu, Nov 23, 2023 at 2:29 PM Daniel Westermann (DWE) <<a \
href="mailto:daniel.westermann@dbi-services.com" \
target="_blank">daniel.westermann@dbi-services.com</a>> wrote:<br></div><div \
class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div>
<div dir="ltr">
<div style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)"><span \
style="font-size:11pt">smrdbprod=# select crart_id, chemin, count(*) from \
smrr_mgr.formula group by crart_id, chemin having count(*) > \
1;</span><br></div> <div><span \
style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:11pt;color:rgb(0,0,0)"> \
crart_id | chemin | count</span></div> <div><span \
style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans \
-serif;font-size:11pt;color:rgb(0,0,0)">-------------+--------+-------</span></div> \
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:11pt;color:rgb(0,0,0)"> \
35054630000 | @ | 2</span></div> <div><span \
style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:11pt;color:rgb(0,0,0)"> \
4737310000 | @ | 2</span></div> <div><span \
style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:11pt;color:rgb(0,0,0)"> \
10632380000 | @ | 2</span></div> <div><span \
style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:11pt;color:rgb(0,0,0)"> \
14680880000 | @ | 2</span></div> <div><span \
style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:11pt;color:rgb(0,0,0)"> \
4627230000 | @ | 2</span></div> <div><span \
style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:11pt;color:rgb(0,0,0)"> \
10993780000 | @ | 2</span></div> <div><span \
style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:11pt;color:rgb(0,0,0)">....<br>
</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:11pt;color:rgb(0,0,0)"><br>
</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:11pt;color:rgb(0,0,0)">I \
think I know what I have to \
do.</span></div></div></div></blockquote><div><br></div><div>If you have moved the \
database from a system with glibc <2.28 to >=2.28, that could be the \
reason.</div><div><br></div><div><a \
href="https://wiki.postgresql.org/wiki/Collations" \
target="_blank">https://wiki.postgresql.org/wiki/Collations</a><br></div></div></div> \
</blockquote></div><div><br></div><div><br></div><div>Just one note here... the \
reason can be _any_ operating systems move or update. It can happen with ICU and it \
can happen with any version of glibc (this is easily reproducible and we've seen \
it happen on production PG deployments that didn't involve glibc \
2.28)</div><div><br></div><div>glibc 2.28 has certainly been the most obvious and \
impactful case, so the focus is understandable, but there's a bit of a myth that \
the problem is only with glibc 2.28 (and not ICU or other glibc versions or data \
structures other than indexes)</div><div><br></div><div>The only truly safe way to \
update an operating system under PosgreSQL is with logical dump/load or logical \
replication, or continuing to compile and use the identical older version of ICU from \
the old OS (if you use ICU). I think the ICU folks are generally careful enough \
that it'll be unlikely for compiler changes and new compiler optimizations to \
inadvertently change collation on newer operating systems and build \
toolchains.</div><div><br></div><div>Ironically I just did a detailed talk on this \
topic at PASS Data Summit last week, but unfortunately the recording probably \
won't be released for awhile. 🙂</div><div><br></div><div>-Jeremy</div><br \
clear="all"><div><br></div><span class="gmail_signature_prefix">-- </span><br><div \
dir="ltr" class="gmail_signature"><a href="http://about.me/jeremy_schneider" \
target="_blank">http://about.me/jeremy_schneider</a></div></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic