From: | Erik Wienhold <ewie(at)ewie(dot)name> |
---|---|
To: | Massimo Ortensi <mortensi(at)unimaticaspa(dot)it>, pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Re: Problems with unique constraints |
Date: | 2023-01-26 12:39:44 |
Message-ID: | 2137860386.717969.1674736784938@office.mailbox.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
> On 26/01/2023 12:52 CET Massimo Ortensi <mortensi(at)unimaticaspa(dot)it> wrote:
>
> we had OS upgrades, but with the same locale.
Which OS? Locale may still be the same but collation information may have
changed, resulting in a different sort order.
https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html
Has libc version changed with the OS upgrades?
> Is there any chance to avoid rebuilding of all indexes/constraints by checking
> the correctness of indexes ?
Extension amcheck (functions bt_index_check and bt_index_parent_check) can
verify that btree entries for text are in lexical order.
https://www.postgresql.org/docs/current/amcheck.html
> Il 26/01/2023 11:56, Erik Wienhold ha scritto:
>
> > On 26/01/2023 11:11 CET Massimo Ortensi <mortensi(at)unimaticaspa(dot)it> wrote:
> >
> > I had troubles with some unique constraints recently.
> >
> > They simply didn't work, not seeing some records (select found only 47
> > records with specific values instead of 65, insert could create
> > duplicate records).
> >
> > The constraint were on multiple columns. Drop constraint and create
> > constraint fixed the problem.
> >
> > This happened on two servers :
> >
> > - a version 10 server, where an abnormal poweroff happened 6 months
> > ago, found 3 tables with the problem throughout last 6 months.
> >
> > - a test server, found the problem on 1 table after upgrading version 10
> > to 14
> >
> > Anythng similar happened to anybody ?
> > How can I find out if similar problems are present ? constraint and
> > index looked ok and were used
> >
> Are the indexes on string types? Changes to the locale data is one cause:
> https://www.postgresql.org/message-id/328626.1647362495@sss.pgh.pa.us
--
Erik
From | Date | Subject | |
---|---|---|---|
Next Message | Holger Jakobs | 2023-01-26 12:42:24 | Re: Problems with unique constraints |
Previous Message | Massimo Ortensi | 2023-01-26 11:52:17 | Re: Problems with unique constraints |