Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pL0np-0000iS-Ir for pgsql-admin@arkaria.postgresql.org; Thu, 26 Jan 2023 11:52:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pL0nn-0003Oi-Ua for pgsql-admin@arkaria.postgresql.org; Thu, 26 Jan 2023 11:52:23 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pL0nn-0003OY-FT for pgsql-admin@lists.postgresql.org; Thu, 26 Jan 2023 11:52:23 +0000 Received: from mail.unimaticaspa.it ([89.97.230.136]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pL0nk-0005V8-9N for pgsql-admin@lists.postgresql.org; Thu, 26 Jan 2023 11:52:21 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=unimaticaspa.it; s=mail; h=Message-Id:In-Reply-To:From:References:To: Subject:MIME-Version:Date:Content-Type:Sender:Reply-To:Cc: Content-Transfer-Encoding:Content-ID:Content-Description:Resent-Date: Resent-From:Resent-Sender:Resent-To:Resent-Cc:Resent-Message-ID:List-Id: List-Help:List-Unsubscribe:List-Subscribe:List-Post:List-Owner:List-Archive; bh=J0uB9jPY7Dc3sMnDCugoEECbDAolyRCuTMkjTK1BYjI=; t=1674733940; x=1676029940; b=pwC6cPPmoEu0CmUQQ8ao3zIRuU5dT8pWyA+2ulDAk9u40/m9BhptPBJEX/o/J5Rg52HzFYLR+Ln vHRYBWvqPLtIWhKo8ihMc0sHajTEY216HZB7D5TtOMfWHuuPqo+yFt4a1wQ296JzQeDydT/pvCV2q cXTUEyYE1IAyUFbiHV00VYlm/U07M/PBeae5eGPhglboNlzZQLBAYhIMiY1SWKf5uscakvnjm2JIs sgfVViuwlWzqr9oCsY1No+tEWarMZ4PcC5fXO0QTKU6R/FISdFxFbxX60QLJhamLrjn9Fe1yQYpcH ZeWyqIEsE/RddNqtizTH8loMC8hCxNc7/usVOLHdX3qvioW9KR5osiHBMVlrRlQREkogsqSqSYSgk jDZRy1KG4wWGxpNxJpvjCtRlEILiwr+iioWaphvlWG7anXVz8OBj4e+l5aRYVKy7SZrchm61BK1L1 j2JZh9bcECnA63t0xQ6KS6rCztSDXgjMvWEQYPBc1VYGfg+rkyZ8gwUcrBzWIx37a0PzYmm24xddJ h/7/6FeHdj3HovmxboyTe8y16I8+XMmEcNH9g9EBapSOe0rGHSgUYd8SgcSeT0biWf1q8uuT9aMMe vOZ5RitHGic0BUX7ZYATcqltTxnML8WEZ0qFLcWfkXdhNXAySRSOPGNx2aBk6jbngHAtQ=; Received: from pc-ortensi-fisso.unimatica.lan ([10.143.90.127]) by mail.unimaticaspa.it with esmtpsa (TLS1.3) tls TLS_AES_256_GCM_SHA384 (Exim 4.95) (envelope-from ) id 1pL0ni-00CnZb-3m; Thu, 26 Jan 2023 12:52:18 +0100 Content-Type: multipart/alternative; boundary="------------3uiM8z3A3lLGGgj0WljZD0xA" Date: Thu, 26 Jan 2023 12:52:17 +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 , pgsql-admin@lists.postgresql.org References: <1pKzDp-00CgfG-JH@unimaticaspa.it> <1968679708.705665.1674730579540@office.mailbox.org> From: Massimo Ortensi In-Reply-To: <1968679708.705665.1674730579540@office.mailbox.org> X-Unimatica-LocalUser: true Message-Id: <1pL0ni-00CnZb-3m@unimaticaspa.it> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------3uiM8z3A3lLGGgj0WljZD0xA Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 Via Cristoforo Colombo, 21 - 40131, Bologna Tel.  051 4195069 Cell.  3351092560 _mortensi@unimaticaspa.it _ 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 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 --------------3uiM8z3A3lLGGgj0WljZD0xA Content-Type: multipart/related; boundary="------------WUKNY7fD6fXTPOkIKaHHMnDK" --------------WUKNY7fD6fXTPOkIKaHHMnDK Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

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

Via Cristoforo Colombo, 21 - 40131, Bologna 
Tel.  051 4195069
Cell.  3351092560

mortensi@unimaticaspa.it

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
--------------WUKNY7fD6fXTPOkIKaHHMnDK Content-Type: image/jpeg; name="UnimaticaRGI.jpg" Content-Disposition: inline; filename="UnimaticaRGI.jpg" Content-Id: Content-Transfer-Encoding: base64 /9j/4AAQSkZJRgABAQEAYABgAAD/2wBDAAoHBwkHBgoJCAkLCwoMDxkQDw4ODx4WFxIZJCAm JSMgIyIoLTkwKCo2KyIjMkQyNjs9QEBAJjBGS0U+Sjk/QD3/2wBDAQsLCw8NDx0QEB09KSMp PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT3/wAAR CAAbAI0DASIAAhEBAxEB/8QAHwAAAQUBAQEBAQEAAAAAAAAAAAECAwQFBgcICQoL/8QAtRAA AgEDAwIEAwUFBAQAAAF9AQIDAAQRBRIhMUEGE1FhByJxFDKBkaEII0KxwRVS0fAkM2JyggkK FhcYGRolJicoKSo0NTY3ODk6Q0RFRkdISUpTVFVWV1hZWmNkZWZnaGlqc3R1dnd4eXqDhIWG h4iJipKTlJWWl5iZmqKjpKWmp6ipqrKztLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uHi4+Tl 5ufo6erx8vP09fb3+Pn6/8QAHwEAAwEBAQEBAQEBAQAAAAAAAAECAwQFBgcICQoL/8QAtREA AgECBAQDBAcFBAQAAQJ3AAECAxEEBSExBhJBUQdhcRMiMoEIFEKRobHBCSMzUvAVYnLRChYk NOEl8RcYGRomJygpKjU2Nzg5OkNERUZHSElKU1RVVldYWVpjZGVmZ2hpanN0dXZ3eHl6goOE hYaHiImKkpOUlZaXmJmaoqOkpaanqKmqsrO0tba3uLm6wsPExcbHyMnK0tPU1dbX2Nna4uPk 5ebn6Onq8vP09fb3+Pn6/9oADAMBAAIRAxEAPwD1fT9Th1ON3g3AIdp3CoV1y1IuSA/+jff4 /lVDwjj7LcZ/vj+VZyf6rWTnv/WuD6xL2cZ97nZ7CPtJR7HU22oQ3dn9piJKAZI7iqg1y2Ng bvD+WH2YxzmuesbiXS1/eZNvdRnB96cv/Irv/wBd6lYuTSLeFim+1zpJdZtoriGF926YAqcV Pe3kVhbNPNnaPTvXK6yjSXFosed3kgimahqcup20MQB/dLuk9yKTxrjzJ/IFhFLla2e511rd x3VotwmQjDPNV7fWba5Sd4g2IfvDFZYuxaeEkYHDONqn61S0v/iW6lb7nDR3CbW56E1csTJO Me5EcOmpPtsa3/CVWXHyTc9ttTf8JBa+THJtkCyNsGV5zVHU40HiKyUKoBHIAp3iVFRrIKoA 83sKPa1UpNvYPZ024pLc0L/WLawwshLOeiqMmpLDU7fUUJgblfvKeCKybBFm8S3JkAYqoAz2 rTj0+0tLyS7jYrIVOUB4/KtKdSpN83QidOEVy9R02q28F/HaSE+Y/QgcUaxq0Gi2DXl0GMak A7Rk81ylyTdy3OoBwGjkBVc84HpVvxncLeeBmmU/eKfnmqwtb2s3FkYml7KCa36kkfxE0hnU SLcxqxwHeIhfzrX1PXrHTLBbq4uAIpB+72Dcz/QVwV1rk1xoltoraR5c06LHHJLwD7ird5pz WuveGdOumEojT5geRmvRdGO+xwqpL1NzTvEGja5ei3JuI7g8qk+V3fSql1qGnxavewSXN2Hi cAhV4HHr3qnrmqWz65bLe6NPE0NwEhnRtgbn6c1nart/4SrV8/8APVf/AEGpWEpVGuZblPFV KcbxZ1raVqVhcS/2Y6GGQ52sfu1ImhzR6TdJuD3U/JzwK6AgUYFcH1WCOz6xNmMdIM2gx2sw AmRcgg9DVJdGvBoJtiqecZd2N3GK6bAFGBTlhoPXysCxE1p53MOXSrhtRspgqlI02uSakn0K KO0u/si/vZwcAmtnFJgU1h4K4vbz0OZbRbyeOxt5VVYYx+8Ian33hlFhDWGRMrZAZuDXR4FG BU/VadtSvrNS+jMSbTrufUrK4KriNf3nzdDUmuadcXzWxgCkRvlsnHFa4FLVewjZruT7WV0+ xg32lXaXv23T5FEjABlNQLo+oCO4uHdXu5V2gA4CiukwPSjFJ4aA1XklYwLbwtbC1T7SG87H zENxmqOpWcmu+F7ix04JvWUKqMccA11pHFcXqpNjq7G1Ji3cnaetZzawrjOCNIReJvCTLev+ G5tT8N20Mexb61VTGxOBkdRmm6h4e1DV9PsLpnW11e0HDZ3K3/662tKXzYVkkJZyOSSa0QBi u2FeUopnJKkk2jkYvDurapqdtda/cxGO1bckMQ4Y+pqpfeFdSn17ULpFjEU7hkO7kgCu6xSY HpWka0ou6IdJNH//2Q== --------------WUKNY7fD6fXTPOkIKaHHMnDK-- --------------3uiM8z3A3lLGGgj0WljZD0xA--