Date: Thu, 26 Jan 2023 15:31:46 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:102.0) Gecko/20100101
 Thunderbird/102.6.1
Subject: Re: Problems with unique constraints
To: Erik Wienhold <ewie@ewie.name>, pgsql-admin@lists.postgresql.org
References: 
 <CAK+uD7heWzrAjdRcSYq-z=crnb5mEs8JgsfGYtrK+vb0yrjeKg@mail.gmail.com>
 <b977effa32ce6e3088469871e294bea5d4d513b1.camel@cybertec.at>
 <1pKzDp-00CgfG-JH@unimaticaspa.it>
 <1968679708.705665.1674730579540@office.mailbox.org>
 <1pL0ni-00CnZb-3m@unimaticaspa.it>
 <2137860386.717969.1674736784938@office.mailbox.org>
From: Massimo Ortensi <mortensi@unimaticaspa.it>
In-Reply-To: <2137860386.717969.1674736784938@office.mailbox.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Message-Id: <1pL3I2-00D3P1-A2@unimaticaspa.it>
Archived-At: 
 <https://www.postgresql.org/message-id/1pL3I2-00D3P1-A2%40unimaticaspa.it>
Precedence: bulk

Confirmed, the local is the same but sorting changed.

Thanks

Il 26/01/2023 13:39, Erik Wienhold ha scritto:
>> On 26/01/2023 12:52 CET Massimo Ortensi <mortensi@unimaticaspa.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@unimaticaspa.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