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 1oudJK-0001OT-Cy for pgsql-general@arkaria.postgresql.org; Mon, 14 Nov 2022 17:31:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oudJJ-00037Q-8V for pgsql-general@arkaria.postgresql.org; Mon, 14 Nov 2022 17:31:53 +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 1oudJI-00037H-Tz for pgsql-general@lists.postgresql.org; Mon, 14 Nov 2022 17:31:52 +0000 Received: from mout.gmx.net ([212.227.17.21]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oudJF-0005NP-IJ for pgsql-general@lists.postgresql.org; Mon, 14 Nov 2022 17:31:51 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=s31663417; t=1668447104; bh=MD0Yxcr6UsTKhwjCe/RhMFmOv5NiAcZWa1eei9cPOZs=; h=X-UI-Sender-Class:Date:From:To:Cc:Subject:References:In-Reply-To; b=Fhu5hrAaHCAt8UVs9Ragm0A5WSU89j55Bi4d2OmMBpHgOdZXYmQqM1do6cKP3qw/t m+4JZX2OeG3FgiA6qIOQZlWKzR6ktZNaCbMe8kskKWoPBM1O/eTLd8XemU53S0pKG7 euS1sgX+iSbmpvIqwNiHUx0V4T/wXyLD28dSOkM+uAyGgbliCjrzdWPZArFqX8D574 gseyk+olAQAJnuqBC9h1ARU2VsLr2zlBR8xIXZDJUX+pnbx73jpu+bBR86Rf8mR/WV jIZ3aV/CNaQH57f2DthqXq7cwR8TYybc7lPCi2ok3Hm3WtqU8bIdUO/f6u5pyTuWjH bLoUCt9/gsAeQ== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from hermes ([91.9.17.9]) by mail.gmx.net (mrgmx104 [212.227.17.168]) with ESMTPSA (Nemesis) id 1MMXUN-1odn9D2X3P-00JcMp; Mon, 14 Nov 2022 18:31:44 +0100 Received: from ncq by hermes with local (Exim 4.94.2) (envelope-from ) id 1oudJ9-0000Rf-H0; Mon, 14 Nov 2022 18:31:43 +0100 Date: Mon, 14 Nov 2022 18:31:43 +0100 From: Karsten Hilbert To: Daniel Verite Cc: Christophe Pettus , pgsql-general@lists.postgresql.org Subject: Re: Q: fixing collation version mismatches Message-ID: References: <206a0d6b-d91d-4fc9-a467-2bcf426fed06@manitou-mail.org> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <206a0d6b-d91d-4fc9-a467-2bcf426fed06@manitou-mail.org> Ma_X_il-Followup-to: d Re_X_turn-receipt-to: Karsten.Hilbert@gmx.net Di_X_sposition-Notification-To: Karsten.Hilbert@gmx.net X-Confi_X_rm-Reading-To: Karsten.Hilbert@gmx.net X-Pri_X_ority: 2 (High) Sender: X-Provags-ID: V03:K1:mdjwU8DMDJHFkb2GDEqVaRYUBV1qo87pmAGWm4OEUyLUAUolLbJ ECr5/wCV4yDZQ+LJOeqd1NvzUYBUs+5qNPpGajaTOzZQTnRni/NiSFRzM2DPu3DnB/Cy+AV nEGcTEcAdVsU+PozLsxY+lWa2Hn3EQx+NQ/8lXaK0lWQOJrkrEja8HGKPovHoakn/wo95gb A8YYW7885y8L3Qk/YlBKA== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:BYJnO4jN6ZI=;CnwmJT+6VM+Z3UtitiGByiBCMo9 jjGDaGIov95LzGe6yd/Q04528Klf1yNstZ8TrlCvUkdj4hGp1cvbooMXzkbHnAIryu2aWluAP 1vMnybcIojbc9ibHqyeC5i3YjsUzxc10laSMyvyeZyHmR1AXN8tZIXofy2l0LQ5EqO1yjpW1T RR1tQNA8AwHo+C1JGc+wY8bhzPzvyJ1be957x6pEBZm0jsVGYWfOw0C1I6afkuiB6ePmYhBxy rjIVn4Xjx5GtCnQ0xwgJfh8i+vO4XB4yGccKNvVgrfOvzuYiT23+2iB1NC1p1whr1qb/e9VaZ eHmBiZYBMv5+JGEPc+ans+28w2pLFO7Qf/0/r3YJ1IoKsMuag2mLM7SWgDpLrnl2coRkiN2Mm SDgLEhWqTZanl3RM7NMpWk8R2Hu7GWZpnCdbw1QZIpIHBc7YNM3ZWnfD899TSyp0/Un/s4DWR X2bywJTfqWP31o/BZXxG6qfDLoejyipbJOAwAF+8OO60kw31BNReEXH6dZtCElE5nzkYo9U2A Fy7B7qwW/e+7zdykcGk2JYiFuyrvB9+iDE80gi+XFqVcJU7owUds1d8WybrWU35Kpv0GaZA0Q rONs9e1Q6j2aDksKJ9JInsogauLquJzYYRvqAOzr+JHNvzoTjiEFWIAuI9OyzYecPXu/xOpq2 uf2cq4rc88hkQAhJChDQKF+wtwPQ6Q6C1fljmwZosaEqEBbmQDFP80y+Odhe6VXF8KHBLfoPP bZqB9TmsJE7XEBm/FyA9G5XNMmmsU56Ja3AnTJJ9uRv+fISS7a41k+8phwm5faWzuo1AfaNP8 yndYIMlOzJuZIekmPqPzTTGNFRb4LaVEyPJtbQ7opge2c6ZLzks8yuB5wYyzQxHSr5MUoHtEC cN2Dv9ZYNogL5OuPUezlnnA5xm1UhINnphgztW00EfjqrfjgShxyvbC2osmBH3YUbLouBKx4h PePgSw== Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Am Mon, Nov 14, 2022 at 05:42:16PM +0100 schrieb Daniel Verite: > > Which is why my question still stands: does the above > > three-strikes operation safely take care of any collation > > issues that may currently exist in a database ? > > For the indexes, yes, but theorically, all constraints involving collata= ble > types need a recheck. > > For foreign key constraints with non-deterministic collations, there > might be equality tests that pass with an older Unicode version and fail > with a newer Unicode version. Which gives weight to the argument that using real-world data (instead of surrogate keys) may lead to trouble. > For check constraints as well, checks applied to strings with recent > Unicode characters can give different results after an upgrade. Thanks for pointing this out more clearly. My thinking already evolved towards also including VALIDATE CONSTRAINT. I shall, for the record, update the sequence in question: -- indices REINDEX DATABASE db_in_question; -- constraints (check, foreign key) UPDATE pg_constraint SET convalidated =3D false WHERE all_check_and_FK_co= nstraints; ALTER TABLE table_with_constraint VALIDATE CONSTRAINT constraint_on_that_= table; -- other things, see below -- ... -- refresh collation versions if no errors above ALTER DATABASE db_in_question REFRESH COLLATION VERSION; ALTER COLLATION every_collation_from_pg_collation REFRESH VERSION; What else needs to be taken care of, and how ? partitions Need to re-sort rows into the proper partition as needed. Can this be achievd by UPDATE each_partitioned_table SET each_partitioned_key =3D each_partitio= ned_key; ? Courtesy of (ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION)= : Updating the partition key of a row will cause it to be moved into a different partition if it no longer satisfies the partition bounds of its original partition. range types Will this UPDATE table_with_range_type_column SET ranged_column =3D ranged_column find all relevant issues ? domains Will this UPDATE table_with_domain_type_column SET domained_column =3D domained_co= lumn find all relevant issues ? custom types ?? function immutability ?? It can be argued that functions marked IMMUTABLE really are not in case they involve sorting of a collatable data type, and are thus wrongly marked as IMMUTABLE. IOW pre-existing user error. If all this has been discussed in detail, I'd be glad for a pointer into the archive. Thanks, Karsten =2D- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B