pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedProblems with unique constraints
8+ messages / 5 participants
[nested] [flat]
* Problems with unique constraints
@ 2023-01-26 10:11 Massimo Ortensi <mortensi@unimaticaspa.it>
0 siblings, 2 replies; 8+ messages in thread
From: Massimo Ortensi @ 2023-01-26 10:11 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Problems with unique constraints
@ 2023-01-26 10:56 Erik Wienhold <ewie@ewie.name>
parent: Massimo Ortensi <mortensi@unimaticaspa.it>
1 sibling, 1 reply; 8+ messages in thread
From: Erik Wienhold @ 2023-01-26 10:56 UTC (permalink / raw)
To: Massimo Ortensi <mortensi@unimaticaspa.it>; pgsql-admin@lists.postgresql.org
> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Problems with unique constraints
@ 2023-01-26 11:52 Massimo Ortensi <mortensi@unimaticaspa.it>
parent: Erik Wienhold <ewie@ewie.name>
0 siblings, 2 replies; 8+ messages in thread
From: Massimo Ortensi @ 2023-01-26 11:52 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; pgsql-admin@lists.postgresql.org
we had OS upgrades, but with the same locale.
Is there any chance to avoid rebuilding of all indexes/constraints by
checking the correctness of indexes ?
*Massimo Ortensi - Responsabile ICT *
logo <http://www.unimaticaspa.it/;
Via Cristoforo Colombo, 21 - 40131, Bologna
Tel. 051 4195069
Cell. 3351092560
_mortensi@unimaticaspa.it <javascript:message_new('mailto',
{mailto:'mortensi@unimaticaspa.it', folder:'SU5CT1g=', msgid:'',
subject: '', body:'0'})> _
Questa e-mail e i suoi allegati contengono informazioni di proprietà di
Unimatica-RGI S.p.A. e devono essere utilizzati esclusivamente dal
destinatario in relazione alle finalità per le quali sono stati
ricevuti. E’ vietata qualsiasi forma di riproduzione o di divulgazione
senza l’esplicito consenso di Unimatica-RGI S.p.A. Qualora la presente
e-mail fosse stata ricevuta per errore, si prega di informare
tempestivamente il mittente e distruggere la copia in possesso.
Privacy: I dati personali contenuti in questa e-mail, nonché nei file
ivi inclusi, risultano oggetto di tutela ai sensi del Reg. UE 2016/679
(GDPR). Il Titolare del trattamento dei suddetti dati è Unimatica-RGI
S.p.A. Gli interessati potranno esercitare tutti i diritti ex artt. 15 e
ss. del GDPR inviando un messaggio all’indirizzo
privacy@pec.unimaticaspa.it. Qualsiasi trattamento effettuato da chi ha
ricevuto per errore tali dati costituisce violazione delle disposizioni
previste dal GDPR. In ogni momento è possibile proporre reclamo
all’Autorità competente. Per maggiori informazioni si rinvia al sito
www.unimaticaspa.it.
Ambiente: Considera la responsabilità che hai verso l’ambiente prima di
stampare questa e-mail
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
Attachments:
[image/jpeg] UnimaticaRGI.jpg (1.6K, ../../1pL0ni-00CnZb-3m@unimaticaspa.it/3-UnimaticaRGI.jpg)
download | view image
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Problems with unique constraints
@ 2023-01-26 12:39 Erik Wienhold <ewie@ewie.name>
parent: Massimo Ortensi <mortensi@unimaticaspa.it>
1 sibling, 1 reply; 8+ messages in thread
From: Erik Wienhold @ 2023-01-26 12:39 UTC (permalink / raw)
To: Massimo Ortensi <mortensi@unimaticaspa.it>; pgsql-admin@lists.postgresql.org
> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Problems with unique constraints
@ 2023-01-26 12:42 Holger Jakobs <holger@jakobs.com>
parent: Massimo Ortensi <mortensi@unimaticaspa.it>
1 sibling, 1 reply; 8+ messages in thread
From: Holger Jakobs @ 2023-01-26 12:42 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Am 26.01.23 um 12:52 schrieb Massimo Ortensi:
>
> we had OS upgrades, but with the same locale.
>
> Is there any chance to avoid rebuilding of all indexes/constraints by
> checking the correctness of indexes ?
>
> *Massimo Ortensi - Responsabile ICT *
> logo <http://www.unimaticaspa.it/;
>
> Via Cristoforo Colombo, 21 - 40131, Bologna
> Tel. 051 4195069
> Cell. 3351092560
>
> _mortensi@unimaticaspa.it <javascript:message_new('mailto',
> {mailto:'mortensi@unimaticaspa.it', folder:'SU5CT1g=', msgid:'',
> subject: '', body:'0'})> _
>
In case of a change in the locale properties - albeit the name of the
locale remained the same - index corruption is possible.
This has been reported on RedHat platforms. The reaction of the
PostgreSQL project was to incorporate the locale libraries by ICU. They
can be used on demand. If they change, the database server will
automatically reindex if necessary.
If something in the OS libraries changes, the database server won't be
informed.
When indexes are corrupt (and data are fine), the best solution is to
reindex all indexes over varchar and text columns.
Kind Regards,
Holger
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
[image/jpeg] UnimaticaRGI.jpg (1.6K, ../../47693153-3704-d084-fded-62ff16dadbf0@jakobs.com/3-UnimaticaRGI.jpg)
download | view image
[application/pgp-signature] OpenPGP_signature (202B, ../../47693153-3704-d084-fded-62ff16dadbf0@jakobs.com/4-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Problems with unique constraints
@ 2023-01-26 13:14 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Holger Jakobs <holger@jakobs.com>
0 siblings, 0 replies; 8+ messages in thread
From: Scott Ribe @ 2023-01-26 13:14 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org
> On Jan 26, 2023, at 5:42 AM, Holger Jakobs <holger@jakobs.com> wrote:
>
> This has been reported on RedHat platforms.
Happened to me on Ubuntu--I think it's Linux in general.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Problems with unique constraints
@ 2023-01-26 14:31 Massimo Ortensi <mortensi@unimaticaspa.it>
parent: Erik Wienhold <ewie@ewie.name>
0 siblings, 0 replies; 8+ messages in thread
From: Massimo Ortensi @ 2023-01-26 14:31 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Problems with unique constraints
@ 2023-01-26 20:44 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Massimo Ortensi <mortensi@unimaticaspa.it>
1 sibling, 0 replies; 8+ messages in thread
From: Laurenz Albe @ 2023-01-26 20:44 UTC (permalink / raw)
To: Massimo Ortensi <mortensi@unimaticaspa.it>; pgsql-admin@lists.postgresql.org
On Thu, 2023-01-26 at 11:11 +0100, Massimo Ortensi 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 ?
Yes, there were quite a number of such reports.
> How can I find out if similar problems are present ? constraint and
> index looked ok and were used
This is almost certainly caused by an update to the library that provides your
collations. The update must have changed the definition of those collations
and consequently the sort order, corrupting your collations.
These articles describe the problem and ways to a solution:
https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html
https://www.cybertec-postgresql.com/en/icu-collations-against-glibc-2-28-data-corruption/
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-01-26 20:44 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-26 10:11 Problems with unique constraints Massimo Ortensi <mortensi@unimaticaspa.it>
2023-01-26 10:56 ` Erik Wienhold <ewie@ewie.name>
2023-01-26 11:52 ` Massimo Ortensi <mortensi@unimaticaspa.it>
2023-01-26 12:39 ` Erik Wienhold <ewie@ewie.name>
2023-01-26 14:31 ` Massimo Ortensi <mortensi@unimaticaspa.it>
2023-01-26 12:42 ` Holger Jakobs <holger@jakobs.com>
2023-01-26 13:14 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-01-26 20:44 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox