Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v14Mp-004Fa4-FJ for pgsql-hackers@arkaria.postgresql.org; Tue, 23 Sep 2025 14:51:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1v14Mn-006Ljo-U3 for pgsql-hackers@arkaria.postgresql.org; Tue, 23 Sep 2025 14:51:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v14Mn-006LjY-GF for pgsql-hackers@lists.postgresql.org; Tue, 23 Sep 2025 14:51:41 +0000 Received: from mail-canadacentralazlp170120003.outbound.protection.outlook.com ([2a01:111:f403:c103::3] helo=YT6PR01CU002.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1v14Mj-002OPW-0v for pgsql-hackers@lists.postgresql.org; Tue, 23 Sep 2025 14:51:41 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=I0mlM0ITXQv0NT1AX5cdQ+t3SWv5cHjX6mcrOIPZIRrrcNmm8oYZelo0cs06MpPxCAIHyV6/n7kaRMrLez+K5U9gdvHhIkI3U9iWjPZ6e6UoCZ/DPp9vcCkl14JqUIcFIgGf8/yk64NZYF+UI7pRT3UIkVSGqN8u+uee0LNjPG7zv47j5iVOZjj3gA4qID+l9353mVpUelANN7BaUupXW7IJmzHpXwrt+6/cSbeVAwx04wBcy2fQHh3nPCMDJQBz1chvZZPecbIzrphFHlHwPMMKZZMCQkcNjETD66C3DeqdLwDUMeT4huTLQDicIm7p61lj0Cm6JmCuKvnZRgb9bA== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=m2X7jU/oKn21bFn2TERVWyNgNwZb5ApX2p68Y6+hNgo=; b=LpCMPhCM113+q49I9164cuo0Eb8bNwSNPttirdtJUcWVl+jB1Mzxt3/SNKC1jFQjXECfLFQwTKWXAz+xgQYHGgnn8a4Fnk2x6EfdtuXgzU6g92WHezj7fR+U1pgl+bgyj9MRlwlWUO/D4U6yugYhV8sEMKOiAyvEQ5g+PL7DYCVK+NvjebFBKOVEyHtBFMeZzAo+26+xnDot6Nxnu4tkXdtOR3XfD6+TfL9uyuilh2OaFmCNP7Ty6c9h/Bbcn+DKKS2/TJzg75TNFBBTCi0JRIeyhSzsKYYZRF/+3GGOjKL6nhccUcC95M6lEqRHWs4JE0EmdQFscfm4h8Q80q+Otg== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=d2l.com; dmarc=pass action=none header.from=d2l.com; dkim=pass header.d=d2l.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=d2l.com; s=selector2; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=m2X7jU/oKn21bFn2TERVWyNgNwZb5ApX2p68Y6+hNgo=; b=a+1TIyLfUg6s/rTjcMRuVNgCwTVYu5eCUZS+EYIKNgWn1JnGU4IJAp/mD7Kat+G/ql3p6nwrqU9NUi41LBptZKBH8/5MAA3x/z3qqpDHZ8KZzhhXRgUs9JEYoiycyNeEXwRVcwdZbERVk7L/pK7taka3urts3fsi1dzc7Ncw9zS9ylAuTyTlh+gzDgnACkNoFgaDZeY2xIgaYVnsJW7/ARM1e+WYSUSmjNrL5I0WMcadXerhJ/++sg9gOUx2GOaErrbD9hgdaINnCYLqYAIKyYYk4NwRWe59flvWO7MPwNNv+cnCu7C0Gr1X2KMPfrlXsKEkPTpVvFGj6akm8P2z6Q== Received: from YT2PPF959236618.CANPRD01.PROD.OUTLOOK.COM (2603:10b6:b08::466) by TO1PPF9D69E3105.CANPRD01.PROD.OUTLOOK.COM (2603:10b6:b08::66e) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.9137.13; Tue, 23 Sep 2025 14:51:28 +0000 Received: from YT2PPF959236618.CANPRD01.PROD.OUTLOOK.COM ([fe80::cb1c:77e3:3148:cbd]) by YT2PPF959236618.CANPRD01.PROD.OUTLOOK.COM ([fe80::cb1c:77e3:3148:cbd%7]) with mapi id 15.20.9115.020; Tue, 23 Sep 2025 14:51:28 +0000 From: Todd Lang To: "pgsql-hackers@lists.postgresql.org" Subject: Supporting non-deterministic collations with tailoring rules. Thread-Topic: Supporting non-deterministic collations with tailoring rules. Thread-Index: AdwsmLEULSqpl1UDQyeUPnc6i6g45AAAMY4g Date: Tue, 23 Sep 2025 14:51:28 +0000 Message-ID: References: In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: MSIP_Label_2bf23dec-417a-4f4e-854c-c4792a92f800_ActionId=d7f50b70-f672-4e44-a078-29ddbb652c83;MSIP_Label_2bf23dec-417a-4f4e-854c-c4792a92f800_ContentBits=0;MSIP_Label_2bf23dec-417a-4f4e-854c-c4792a92f800_Enabled=true;MSIP_Label_2bf23dec-417a-4f4e-854c-c4792a92f800_Method=Standard;MSIP_Label_2bf23dec-417a-4f4e-854c-c4792a92f800_Name=Confidential;MSIP_Label_2bf23dec-417a-4f4e-854c-c4792a92f800_SetDate=2025-09-23T14:44:38Z;MSIP_Label_2bf23dec-417a-4f4e-854c-c4792a92f800_SiteId=74bbca6d-410b-45b3-9b51-2a6aa6477079;MSIP_Label_2bf23dec-417a-4f4e-854c-c4792a92f800_Tag=10, 3, 0, 1; authentication-results: dkim=none (message not signed) header.d=none;dmarc=none action=none header.from=D2L.com; x-ms-publictraffictype: Email x-ms-traffictypediagnostic: YT2PPF959236618:EE_|TO1PPF9D69E3105:EE_ x-ms-office365-filtering-correlation-id: 94118ef6-2b8d-4b5f-0ba8-08ddfab0ad7a x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|1800799024|376014|366016|19092799006|13003099007|8096899003|38070700021; x-microsoft-antispam-message-info: =?us-ascii?Q?J0smPIn0uMLSP+vgWVxpY0YJuZUBPsHGsZ4lzkQu5RBEaGR5obPK/5wHb7RI?= =?us-ascii?Q?6WHjDgrA8yTcg+eoTwsijEOb8sq0gt6Wpj7tL9AEr1zmJnr5H1tAp/xDMIb+?= =?us-ascii?Q?afox5wFrB9nY0awlq/ydpwGZeQGxTrMLZfkJ+i9g3OizHHvuRxD+HCL9hELm?= =?us-ascii?Q?A1asrAkeTkp7Pbp0QH4DUip9o9kod3JVm9caHklYpYA7gNcvmw48ocIFzUNq?= =?us-ascii?Q?asYRImgEW5btb7H9FJmVcLWp3NeqBsqIr4uHN0DPWWR7uqxlXNVe7Md1IXu9?= =?us-ascii?Q?pb7qK6HTt+I4/aerCVrHs0SIxi8QWJkvuT5nHdfbolFA/fKHPZvgLWnrcylT?= =?us-ascii?Q?2p6Yq05DKBEmS+jTyXh28gC5HqosMTMRcf7k8Ogo9mmyMYTd6Lr/YZey5OZr?= =?us-ascii?Q?vafSSDXPC3MnOUAMHrsXDdhqFTxzshLnby8NRkQ+/NW8pImhRxACEJBlC9Uc?= =?us-ascii?Q?MP6i5DvODIAUgENg+yoHzWp6m7lAtn7aKaMfBtsBHlvIY6Tfd4W9U4qAJyBC?= =?us-ascii?Q?s3qNLwr4+yBIw/jrqJO11JP74/HboL4ZYDvvI1JBvGxyjB7qUnnQufe7lhgG?= =?us-ascii?Q?MosUpiMkqh8sk80j2GpGlTAmFNatS3tKBPCE/9dcJUx5pPR0S6oJnmbgMU/W?= =?us-ascii?Q?FHecT8Cjg7K7gBuQ51+M2GYCMf1EsOIAfiK+UxIT3yDCLqH5C5nvuqRBEBx9?= =?us-ascii?Q?vg/0q5weqbyQctHFEem1xSlxMcNdjX/TDiSFO8faKGCx61DP/2VvoVZGMzxe?= =?us-ascii?Q?k/8D2Rd9J3hmLcy54GF6+jZbxQe749odZzqp41HJbCcacZ7ESERB4YlbChaa?= =?us-ascii?Q?iKSN30L2mQ5baD+VagfVjRMtLNnojHjbWbKxLryen41bLxzgPzTn5iwbO5R1?= =?us-ascii?Q?h6/h/duUU8J6eHjbAOGo6vk6VwJ2K40QacBdpjDIPPe2qZgaVMNCFoncX4va?= =?us-ascii?Q?i/1ztf7vEsyLu3cCJyYQomcx/q5/mf1xwKwRc+E6Cp1nQaGMe7dSyMAgfyoJ?= =?us-ascii?Q?fyjqGTiHnyjGaBInS4dx5W8QGIN9bSak6sjSVzsBM3HKcmZODAlbG/s2pKEW?= =?us-ascii?Q?FKKfJfsXMi5UfjLe0IRFo555pSXWrc7NXbw4c8cfk0mgQ23Wsae2uNDpcGj4?= =?us-ascii?Q?AE1r0ygKtAj20E82nXCvq3JaN5yqToYmmdEQr0j33kzN8covfcFm5xi7QtBX?= =?us-ascii?Q?JfO+oSdXMG6/p9sfCDs6u4PmVyzJYhNMr+AdxyooslCgxv2Dy19fKLuCvuNX?= =?us-ascii?Q?M0hsoCyIoSI7fkUsnw20ruQeAJytLHYagh5JUR2PIX2SEfBf9cVeELIiMe4A?= =?us-ascii?Q?HuzNWlWWntkkFQt2w2VmNj1jqqekiobV5F90QbuW70I2FAxx9tSFrNc40tbq?= =?us-ascii?Q?G5773E/sYux+DLXju5O2et593bsmuB181tQ3CmC2WR1saeG88nvzi/TbVyYZ?= =?us-ascii?Q?8Db6mRzQh2Zs2mLoMt7/yTudzHSpR/iwNqGaGW7cRBsUdgxWIIBTGNczYOdE?= =?us-ascii?Q?h+dtIRDNYAasj6o=3D?= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:YT2PPF959236618.CANPRD01.PROD.OUTLOOK.COM;PTR:;CAT:NONE;SFS:(13230040)(1800799024)(376014)(366016)(19092799006)(13003099007)(8096899003)(38070700021);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?us-ascii?Q?mALkZul1rwZJuWVk5O2kHyBI6DOSNtUHUuaK0FlNVD1FlazX86WIhFAIDSop?= =?us-ascii?Q?+s/rlvjYutyn81k9oYCf82F9Jm2N0E9eQ+bGNd2KViPFj6SNvOBfV1LmpiYC?= =?us-ascii?Q?2nMbS72F5DWvK7UbG9AEwdyu0TaWAHGAFWaniBoG62KkbvXFY8TbUzxBHAsK?= =?us-ascii?Q?qDzZ1CzrgtAX4uwK7w6EjzSyvyPQ6Po4uYfuM5HmrIpEtwBf7yDid3FX1mun?= =?us-ascii?Q?EnFYqcxSk0rTNV6RbKnJgtetZMeGOjTyrfIl08fI8QvHM3zTW8jzGyIG0akp?= =?us-ascii?Q?IG3JxYOylBPcqzVCYyn+M4s4KqqWodfFvM//YHqkAVGnF09a+QeowX1nueTH?= =?us-ascii?Q?2FnUZqCogOYcdz7YcGw4uvQlNrBQm5tCRJiLU5smOND+ukoZgh6ZxZx8EmHY?= =?us-ascii?Q?PZhQe0/rT3eY37HNHG0ORAi9n3HmQwhkBoCRhisZ95qSxOTn6MAe3qCCIVQS?= =?us-ascii?Q?al9Q2sMsiVTwo5MVfEkYx+HjduRDHx3qgHVnq2B7HzxtX7RplZzdlxmUmLOV?= =?us-ascii?Q?rOYA20ocC3pDI7r5L9uTMgWcOcaJ4XJ70M+SGz4CE5Red64ujZvhbi939zWG?= =?us-ascii?Q?AlZAmaXrlf2X5jvlre9jbKdsLd6WjmfXWE8ilzFrXrCn2TybCAlqubnyaUy/?= =?us-ascii?Q?I8lggieDoTVRJlP/vYseUaRCQ5to3Qw+et0oZmz82vNrhrLG8io59UL3WRdc?= =?us-ascii?Q?zD6i1XFMrtekqiKBSGo/8vr/mKPSu6hO4Y9nZpTp7KEX1o0ANcY9QIzZQOE+?= =?us-ascii?Q?NnpGm+JYwj8l+LSlgbE+4UVMcbJ5ZCPC8AoL/7DsZZ1dtyNIR+edcgGK0bhX?= =?us-ascii?Q?PNp8f8Zkyddxmrle5n1n1VaXpiBa1GT4Nsupd7o4FTP9jHy48iqG5hdL9Skl?= =?us-ascii?Q?jukRbql5F3PuVDJ5Bn+X3H3mRMnUl3PSzWxBV4yLwdbvojT0IhYG0Wbt1Aci?= =?us-ascii?Q?F2Ruv/qj9XlfAG3/wy/f0lYnD2bYv9MgTmg+A2dPsLwv3Y46ki63x4X5daar?= =?us-ascii?Q?2OBtm8M8GmEuGeQczF3s/hwLOA1VX3JTHWjFskgE+Xuylg1b3dxm93HwYrSq?= =?us-ascii?Q?ZWsl1ahFJW4VIQgOXM+SFRnLHD5u9UrA1kZ9n1cyRxDOKhl58/GKNbcC4Gld?= =?us-ascii?Q?Z96hNEh+WRr04rLgYpNmYAeS2UDH7B5OCPCqHcJP+ClOc+yq4fZ8FqStkFZG?= =?us-ascii?Q?Xw63ZIrCfRcxyOPn4tA55g/nFIEeCoTzBtS+qLxJhO6Wc//QgyWqyasFCHjR?= =?us-ascii?Q?wPfVXSXUSDj6VwxIJawuBAyxR07Fq4YO6F4HpdiKWdgmthhp5KDrqUlBrEiT?= =?us-ascii?Q?DTh2W8ANb4kgEwVJkedPQHSeD7rMA9z+eE0Uf1DLWjG1erqbp1LXaFGxj0UI?= =?us-ascii?Q?R/pFc0j4jNm5/8gRmrr/snUXFokzBWYyZqIhpONd7VzKmCTe5Xdfgl4dkfr3?= =?us-ascii?Q?nJ4zXDIbhsk9ZitNPRdxr9nOtvjf+ePJwG4LVEtPacKs8FiRcjIoCSf9tAqV?= =?us-ascii?Q?rmzA1NDzc5m7fsZzIbrVjPVp1qXz83O1rWbgxwVdBO+Py5AsZ4Bsb07ZU90O?= =?us-ascii?Q?HkBokddYZvnUpvAAgPEWXyjgLmr3d9/Gb5YS2w5y?= Content-Type: multipart/alternative; boundary="_000_YT2PPF959236618377A072745A280E278F4BE1DAYT2PPF959236618_" MIME-Version: 1.0 X-OriginatorOrg: d2l.com X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: YT2PPF959236618.CANPRD01.PROD.OUTLOOK.COM X-MS-Exchange-CrossTenant-Network-Message-Id: 94118ef6-2b8d-4b5f-0ba8-08ddfab0ad7a X-MS-Exchange-CrossTenant-originalarrivaltime: 23 Sep 2025 14:51:28.7721 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 74bbca6d-410b-45b3-9b51-2a6aa6477079 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: 6WESQ12ZV1ggmXh+SuzGOpNBeNTUsuiGmH+HyK3yfJGdyl+Y8MxbhSBkD090lC8A8c4he0h5RD41r9/MSZO9Lw== X-MS-Exchange-Transport-CrossTenantHeadersStamped: TO1PPF9D69E3105 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_YT2PPF959236618377A072745A280E278F4BE1DAYT2PPF959236618_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Reposting this here from the Discord server as requested: When creating a collation, in https://github.com/postgres/postgres/blob/mas= ter/src/backend/utils/adt/pg_locale_icu.c#L461 it is opening the collator w= ith the tailoring rules supplied. However, it has hardcoded the strength le= vel UCOL_DEFAULT_STRENGTH. This has the effect of ignoring the "determinist= ic=3Dfalse" you may have specified in your CREATE COLLATION call. If, inste= ad of UCOL_DEFAULT_STRENGTH, the code understood the deterministic paramete= r and passed either UCOL_PRIMARY for "deterministic=3Dtrue", and UCOL_SECON= DARY for "deterministic=3Dfalse", this would preserve the attempt to obtain= case-insensitivity in the locale while simultaneously allowing tailoring a= s expected. I have made the modification to the pg_locale_icu.c and tested it locally (= simply hardcoding UCOL_SECONDARY - not checking the deterministic parameter= ) and it behaves as expected, though I freely admit my knowledge of ICU int= ersecting with Postgres is rather limited. --_000_YT2PPF959236618377A072745A280E278F4BE1DAYT2PPF959236618_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

 

 

Repo= sting this here from the Discord server as requested:

 

When creating a col= lation, in https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/pg_l= ocale_icu.c#L461 it is opening the collator with the tailoring rules su= pplied. However, it has hardcoded the strength level UCOL_DEFAULT_STRENGTH.= This has the effect of ignoring the "deterministic=3Dfalse" you may have specified in your CREAT= E COLLATION call. If, instead of UCOL_DEFAULT_STRENGTH, the code understood= the deterministic parameter and passed either UCOL_PRIMARY for "deter= ministic=3Dtrue", and UCOL_SECONDARY for "deterministic=3Dfalse&q= uot;, this would preserve the attempt to obtain case-insensitivity in the locale= while simultaneously allowing tailoring as expected.

 

I ha= ve made the modification to the pg_locale_icu.c and tested it locally (simp= ly hardcoding UCOL_SECONDARY – not checking the deterministic paramet= er) and it behaves as expected, though I freely admit my knowledge of ICU intersecting with Postgres is rather limited.

--_000_YT2PPF959236618377A072745A280E278F4BE1DAYT2PPF959236618_--