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 1pL1aJ-0002Rh-Ce for pgsql-admin@arkaria.postgresql.org; Thu, 26 Jan 2023 12:42:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pL1aI-0004QQ-Ah for pgsql-admin@arkaria.postgresql.org; Thu, 26 Jan 2023 12:42:30 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pL1aH-0004QG-Ue for pgsql-admin@lists.postgresql.org; Thu, 26 Jan 2023 12:42:29 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pL1aF-0000H4-86 for pgsql-admin@lists.postgresql.org; Thu, 26 Jan 2023 12:42:29 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id 21EB338044E; Thu, 26 Jan 2023 13:42:25 +0100 (CET) Received: from rs.plausibolo.de ([127.0.0.1]) by localhost (h2367442.stratoserver.net [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id A0wmuAX0pf8W; Thu, 26 Jan 2023 13:42:24 +0100 (CET) Received: from [192.168.178.28] (ip-037-201-152-231.um10.pools.vodafone-ip.de [37.201.152.231]) by rs.plausibolo.de (Postfix) with ESMTPSA id AA7CA380322; Thu, 26 Jan 2023 13:42:24 +0100 (CET) Message-ID: <47693153-3704-d084-fded-62ff16dadbf0@jakobs.com> Date: Thu, 26 Jan 2023 13:42:24 +0100 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.6.1 To: pgsql-admin@lists.postgresql.org References: <1pKzDp-00CgfG-JH@unimaticaspa.it> <1968679708.705665.1674730579540@office.mailbox.org> <1pL0ni-00CnZb-3m@unimaticaspa.it> From: Holger Jakobs Subject: Re: Problems with unique constraints In-Reply-To: <1pL0ni-00CnZb-3m@unimaticaspa.it> Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="------------oJGXU2ttmOOXyac0lcr7RuXG" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --------------oJGXU2ttmOOXyac0lcr7RuXG Content-Type: multipart/mixed; boundary="------------xFlCaxJ9eiAj7PJ6OAcRN00H"; protected-headers="v1" From: Holger Jakobs To: pgsql-admin@lists.postgresql.org Message-ID: <47693153-3704-d084-fded-62ff16dadbf0@jakobs.com> Subject: Re: Problems with unique constraints References: <1pKzDp-00CgfG-JH@unimaticaspa.it> <1968679708.705665.1674730579540@office.mailbox.org> <1pL0ni-00CnZb-3m@unimaticaspa.it> In-Reply-To: <1pL0ni-00CnZb-3m@unimaticaspa.it> --------------xFlCaxJ9eiAj7PJ6OAcRN00H Content-Type: multipart/alternative; boundary="------------paMZRYPkdDxKDUjQBcbchYP4" --------------paMZRYPkdDxKDUjQBcbchYP4 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: base64 DQpBbSAyNi4wMS4yMyB1bSAxMjo1MiBzY2hyaWViIE1hc3NpbW8gT3J0ZW5zaToNCj4NCj4g d2UgaGFkIE9TIHVwZ3JhZGVzLCBidXQgd2l0aCB0aGUgc2FtZSBsb2NhbGUuDQo+DQo+IElz IHRoZXJlIGFueSBjaGFuY2UgdG8gYXZvaWQgcmVidWlsZGluZyBvZiBhbGwgaW5kZXhlcy9j b25zdHJhaW50cyBieSANCj4gY2hlY2tpbmcgdGhlIGNvcnJlY3RuZXNzIG9mIGluZGV4ZXMg Pw0KPg0KPiAqTWFzc2ltbyBPcnRlbnNpIC0gUmVzcG9uc2FiaWxlIElDVCAqDQo+IGxvZ28g PGh0dHA6Ly93d3cudW5pbWF0aWNhc3BhLml0Lz4NCj4NCj4gVmlhIENyaXN0b2Zvcm8gQ29s b21ibywgMjEgLSA0MDEzMSwgQm9sb2duYQ0KPiBUZWwuwqAgMDUxIDQxOTUwNjkNCj4gQ2Vs bC7CoCAzMzUxMDkyNTYwDQo+DQo+IF9tb3J0ZW5zaUB1bmltYXRpY2FzcGEuaXQgPGphdmFz Y3JpcHQ6bWVzc2FnZV9uZXcoJ21haWx0bycsIA0KPiB7bWFpbHRvOidtb3J0ZW5zaUB1bmlt YXRpY2FzcGEuaXQnLCBmb2xkZXI6J1NVNUNUMWc9JywgbXNnaWQ6JycsIA0KPiBzdWJqZWN0 OiAnJywgYm9keTonMCd9KT4gXw0KPg0KSW4gY2FzZSBvZiBhIGNoYW5nZSBpbiB0aGUgbG9j YWxlIHByb3BlcnRpZXMgLSBhbGJlaXQgdGhlIG5hbWUgb2YgdGhlIA0KbG9jYWxlIHJlbWFp bmVkIHRoZSBzYW1lIC0gaW5kZXggY29ycnVwdGlvbiBpcyBwb3NzaWJsZS4NCg0KVGhpcyBo YXMgYmVlbiByZXBvcnRlZCBvbiBSZWRIYXQgcGxhdGZvcm1zLiBUaGUgcmVhY3Rpb24gb2Yg dGhlIA0KUG9zdGdyZVNRTCBwcm9qZWN0IHdhcyB0byBpbmNvcnBvcmF0ZSB0aGUgbG9jYWxl IGxpYnJhcmllcyBieSBJQ1UuIFRoZXkgDQpjYW4gYmUgdXNlZCBvbiBkZW1hbmQuIElmIHRo ZXkgY2hhbmdlLCB0aGUgZGF0YWJhc2Ugc2VydmVyIHdpbGwgDQphdXRvbWF0aWNhbGx5IHJl aW5kZXggaWYgbmVjZXNzYXJ5Lg0KDQpJZiBzb21ldGhpbmcgaW4gdGhlIE9TIGxpYnJhcmll cyBjaGFuZ2VzLCB0aGUgZGF0YWJhc2Ugc2VydmVyIHdvbid0IGJlIA0KaW5mb3JtZWQuDQoN CldoZW4gaW5kZXhlcyBhcmUgY29ycnVwdCAoYW5kIGRhdGEgYXJlIGZpbmUpLCB0aGUgYmVz dCBzb2x1dGlvbiBpcyB0byANCnJlaW5kZXggYWxsIGluZGV4ZXMgb3ZlciB2YXJjaGFyIGFu ZCB0ZXh0IGNvbHVtbnMuDQoNCktpbmQgUmVnYXJkcywNCg0KSG9sZ2VyDQoNCg0KLS0gDQpI b2xnZXIgSmFrb2JzLCBCZXJnaXNjaCBHbGFkYmFjaCwgVGVsLiArNDktMTc4LTk3NTkwMTIN Cg0K --------------paMZRYPkdDxKDUjQBcbchYP4 Content-Type: multipart/related; boundary="------------WRuzOCE5jvh1pI3iPU1GDPU7" --------------WRuzOCE5jvh1pI3iPU1GDPU7 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable


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
3D"logo"

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

mortensi@unimaticaspa.it

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


--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
--------------WRuzOCE5jvh1pI3iPU1GDPU7 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== --------------WRuzOCE5jvh1pI3iPU1GDPU7-- --------------paMZRYPkdDxKDUjQBcbchYP4-- --------------xFlCaxJ9eiAj7PJ6OAcRN00H-- --------------oJGXU2ttmOOXyac0lcr7RuXG Content-Type: application/pgp-signature; name="OpenPGP_signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="OpenPGP_signature" -----BEGIN PGP SIGNATURE----- wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCY9J1MAUDAAAAAAAKCRCKV5yTsxlhuu2p AJ47LcfiMHAGqzKPkLZtEPSZub+wRwCfYHEsNUzrqeBUlbUFr5E5Eo5ZyNM= =dZWh -----END PGP SIGNATURE----- --------------oJGXU2ttmOOXyac0lcr7RuXG--