pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedCREATE COLLATION without LOCALE throws error in v15
8+ messages / 4 participants
[nested] [flat]
* CREATE COLLATION without LOCALE throws error in v15
@ 2022-12-02 19:14 Kyle Spearrin <kspearrin@bitwarden.com>
0 siblings, 1 reply; 8+ messages in thread
From: Kyle Spearrin @ 2022-12-02 19:14 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: Justin Baur <jbaur@bitwarden.com>; Vince Grassia <vgrassia@bitwarden.com>
Hi,
I wanted to report a bug with the CREATE COLLATION query in Postgres 15.
The following query works in Postgres 12-14:
CREATE COLLATION "postgresIndetermanisticCollation" (LC_COLLATE =
'en-u-ks-primary',
LC_CTYPE = 'en-u-ks-primary',
PROVIDER = icu,
DETERMINISTIC = False
);
However, in Postgres 15 we see the following error:
ERROR: parameter "locale" must be specified
Changing the query to following resolves the issue:
CREATE COLLATION "postgresIndetermanisticCollation" (LOCALE =
'en-u-ks-primary',
PROVIDER = icu,
DETERMINISTIC = False
);
According to the docs here
<https://www.postgresql.org/docs/current/sql-createcollation.html;, we
should be able to execute this query either way (as is evident when testing
in versions 11-14).
This causes problems with queries generated by our Entity Framework Core
ORM tool that uses npgsql. We don't have control to change the way
the COLLATION query is created, therefore preventing us from supporting
Postgres 15.
--
Kyle Spearrin
Follow Bitwarden on social media:
Twitter <https://twitter.com/bitwarden; | Facebook
<https://www.facebook.com/bitwarden; | GitHub <https://github.com/bitwarden;
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: CREATE COLLATION without LOCALE throws error in v15
@ 2022-12-06 23:57 Jeff Davis <pgsql@j-davis.com>
parent: Kyle Spearrin <kspearrin@bitwarden.com>
0 siblings, 1 reply; 8+ messages in thread
From: Jeff Davis @ 2022-12-06 23:57 UTC (permalink / raw)
To: Kyle Spearrin <kspearrin@bitwarden.com>; pgsql-bugs@lists.postgresql.org; +Cc: Justin Baur <jbaur@bitwarden.com>; Vince Grassia <vgrassia@bitwarden.com>; Peter Eisentraut <peter@eisentraut.org>
On Fri, 2022-12-02 at 14:14 -0500, Kyle Spearrin wrote:
> However, in Postgres 15 we see the following error:
>
> ERROR: parameter "locale" must be specified
I agree that this is some kind of bug. The docs seem pretty clear[1]:
"locale
This is a shortcut for setting LC_COLLATE and LC_CTYPE
at once. If you specify this, you cannot specify either
of those parameters."
The error appears to come from commit f2553d4306 ("Add option to use
ICU as global locale provider"). My guess is that it was seen as
clearer to write LOCALE, and that the error wasn't expected to cause
anyone a problem. But, given that it's causing a problem for ORMs, we
should fix it.
Simple patch attached. I intend to commit and backpatch to 15 in the
next day or so.
Looking at the docs for CREATE COLLATION[1] and CREATE DATABASE[2], we
could probably do more to make this area consistent. But that's for
another discussion.
[1] https://www.postgresql.org/docs/devel/sql-createcollation.html
[2] https://www.postgresql.org/docs/devel/sql-createdatabase.html
--
Jeff Davis
PostgreSQL Contributor Team - AWS
Attachments:
v1-0001-Re-allow-creating-ICU-collation-if-LC_COLLATE-mat.patch (text/x-patch, 1.2K) download
From db8851a4b6f38d3223a7229abc4d7a08737258b7 Mon Sep 17 00:00:00 2001
From: Jeff Davis <jeff@j-davis.com>
Date: Tue, 6 Dec 2022 15:36:54 -0800
Subject: [PATCH v1] Re-allow creating ICU collation if LC_COLLATE matches
LC_CTYPE.
Commit f2553d4306 disallowed creating an ICU collation if LOCALE was
not specified. Previously, this was allowed as long as LC_COLLATE and
LC_CTYPE matched.
Discussion: https://postgr.es/m/CALDQics_oBEYfOnu_zH6yw9WR1waPCmcrqxQ8+39hK3Op=z2UQ@mail.gmail.com
Reported-by: Kyle Spearrin <kspearrin@bitwarden.com>
Backpatch-through: 15
---
src/backend/commands/collationcmds.c | 4 ++++
1 file changed, 4 insertions(+)
diff --git a/src/backend/commands/collationcmds.c b/src/backend/commands/collationcmds.c
index 81e54e0ce6..4b2f1d2c15 100644
--- a/src/backend/commands/collationcmds.c
+++ b/src/backend/commands/collationcmds.c
@@ -240,6 +240,10 @@ DefineCollation(ParseState *pstate, List *names, List *parameters, bool if_not_e
}
else if (collprovider == COLLPROVIDER_ICU)
{
+ if (!colliculocale && collcollate && collctype &&
+ strcmp(collcollate, collctype) == 0)
+ colliculocale = collcollate;
+
if (!colliculocale)
ereport(ERROR,
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
--
2.34.1
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: CREATE COLLATION without LOCALE throws error in v15
@ 2022-12-07 12:04 Peter Eisentraut <peter.eisentraut@enterprisedb.com>
parent: Jeff Davis <pgsql@j-davis.com>
0 siblings, 1 reply; 8+ messages in thread
From: Peter Eisentraut @ 2022-12-07 12:04 UTC (permalink / raw)
To: Jeff Davis <pgsql@j-davis.com>; Kyle Spearrin <kspearrin@bitwarden.com>; pgsql-bugs@lists.postgresql.org; +Cc: Justin Baur <jbaur@bitwarden.com>; Vince Grassia <vgrassia@bitwarden.com>
On 07.12.22 00:57, Jeff Davis wrote:
> On Fri, 2022-12-02 at 14:14 -0500, Kyle Spearrin wrote:
>> However, in Postgres 15 we see the following error:
>>
>> ERROR: parameter "locale" must be specified
>
> I agree that this is some kind of bug. The docs seem pretty clear[1]:
>
> "locale
>
> This is a shortcut for setting LC_COLLATE and LC_CTYPE
> at once. If you specify this, you cannot specify either
> of those parameters."
>
> The error appears to come from commit f2553d4306 ("Add option to use
> ICU as global locale provider"). My guess is that it was seen as
> clearer to write LOCALE, and that the error wasn't expected to cause
> anyone a problem.
This was an intentional change because of underlying conceptual and
catalog changes. lc_collate, lc_ctype, and iculocale are now tracked
separately in the catalogs. This is also important because for a
database they actually are used separately. So this also keeps the
locale/collation settings for databases and collations consistent.
The "locale" parameter is now basically "default for whatever settings
are appropriate for the locale provider". We should change the
documentation in that direction.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: CREATE COLLATION without LOCALE throws error in v15
@ 2022-12-07 17:44 Jeff Davis <pgsql@j-davis.com>
parent: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
0 siblings, 1 reply; 8+ messages in thread
From: Jeff Davis @ 2022-12-07 17:44 UTC (permalink / raw)
To: Peter Eisentraut <peter.eisentraut@enterprisedb.com>; Kyle Spearrin <kspearrin@bitwarden.com>; pgsql-bugs@lists.postgresql.org; +Cc: Justin Baur <jbaur@bitwarden.com>; Vince Grassia <vgrassia@bitwarden.com>
On Wed, 2022-12-07 at 13:04 +0100, Peter Eisentraut wrote:
> This was an intentional change because of underlying conceptual and
> catalog changes. lc_collate, lc_ctype, and iculocale are now tracked
> separately in the catalogs. This is also important because for a
> database they actually are used separately. So this also keeps the
> locale/collation settings for databases and collations consistent.
That makes sense, but there also doesn't seem to be much harm in
supporting the old behavior where it works as long as ctype==collate.
As in my patch, it accepts either locale or ctype==collate as the icu
locale.
We can discourage relying on that by updating the documentation as you
suggest.
--
Jeff Davis
PostgreSQL Contributor Team - AWS
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: CREATE COLLATION without LOCALE throws error in v15
@ 2022-12-08 18:29 Peter Eisentraut <peter.eisentraut@enterprisedb.com>
parent: Jeff Davis <pgsql@j-davis.com>
0 siblings, 1 reply; 8+ messages in thread
From: Peter Eisentraut @ 2022-12-08 18:29 UTC (permalink / raw)
To: Jeff Davis <pgsql@j-davis.com>; Kyle Spearrin <kspearrin@bitwarden.com>; pgsql-bugs@lists.postgresql.org; +Cc: Justin Baur <jbaur@bitwarden.com>; Vince Grassia <vgrassia@bitwarden.com>
On 07.12.22 18:44, Jeff Davis wrote:
> On Wed, 2022-12-07 at 13:04 +0100, Peter Eisentraut wrote:
>> This was an intentional change because of underlying conceptual and
>> catalog changes. lc_collate, lc_ctype, and iculocale are now tracked
>> separately in the catalogs. This is also important because for a
>> database they actually are used separately. So this also keeps the
>> locale/collation settings for databases and collations consistent.
>
> That makes sense, but there also doesn't seem to be much harm in
> supporting the old behavior where it works as long as ctype==collate.
> As in my patch, it accepts either locale or ctype==collate as the icu
> locale.
The harm is that in the context of database-level collations, the
analogous syntax means something different. That's why in the context
of adding ICU support on the database level, this syntax for collation
objects was disabled.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: CREATE COLLATION without LOCALE throws error in v15
@ 2022-12-08 19:59 Jeff Davis <pgsql@j-davis.com>
parent: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
0 siblings, 2 replies; 8+ messages in thread
From: Jeff Davis @ 2022-12-08 19:59 UTC (permalink / raw)
To: Peter Eisentraut <peter.eisentraut@enterprisedb.com>; Kyle Spearrin <kspearrin@bitwarden.com>; pgsql-bugs@lists.postgresql.org; +Cc: Justin Baur <jbaur@bitwarden.com>; Vince Grassia <vgrassia@bitwarden.com>
On Thu, 2022-12-08 at 19:29 +0100, Peter Eisentraut wrote:
> The harm is that in the context of database-level collations, the
> analogous syntax means something different.
I see. I certainly agree that we shouldn't document the
lc_ctype=lc_collate hack for CREATE COLLATION for the ICU provider,
because we don't want to encourage it.
Kyle, perhaps after the docs are updated, you can reach out to the
npgsql project to see if they can fix it on their side?
--
Jeff Davis
PostgreSQL Contributor Team - AWS
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: CREATE COLLATION without LOCALE throws error in v15
@ 2022-12-08 20:02 Kyle Spearrin <kspearrin@bitwarden.com>
parent: Jeff Davis <pgsql@j-davis.com>
1 sibling, 0 replies; 8+ messages in thread
From: Kyle Spearrin @ 2022-12-08 20:02 UTC (permalink / raw)
To: Jeff Davis <pgsql@j-davis.com>; +Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>; pgsql-bugs@lists.postgresql.org, Justin Baur <jbaur@bitwarden.com>; Vince Grassia <vgrassia@bitwarden.com>
Jeff,
It appears npgsql has already addressed it in an upcoming version, which we
are awaiting:
https://github.com/npgsql/efcore.pg/issues/2527
--
Kyle Spearrin
Follow Bitwarden on social media:
Twitter <https://twitter.com/bitwarden; | Facebook
<https://www.facebook.com/bitwarden; | GitHub <https://github.com/bitwarden;
On Thu, Dec 8, 2022 at 2:59 PM Jeff Davis <pgsql@j-davis.com> wrote:
> On Thu, 2022-12-08 at 19:29 +0100, Peter Eisentraut wrote:
> > The harm is that in the context of database-level collations, the
> > analogous syntax means something different.
>
> I see. I certainly agree that we shouldn't document the
> lc_ctype=lc_collate hack for CREATE COLLATION for the ICU provider,
> because we don't want to encourage it.
>
> Kyle, perhaps after the docs are updated, you can reach out to the
> npgsql project to see if they can fix it on their side?
>
>
> --
> Jeff Davis
> PostgreSQL Contributor Team - AWS
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: CREATE COLLATION without LOCALE throws error in v15
@ 2022-12-08 20:03 Justin Baur <jbaur@bitwarden.com>
parent: Jeff Davis <pgsql@j-davis.com>
1 sibling, 0 replies; 8+ messages in thread
From: Justin Baur @ 2022-12-08 20:03 UTC (permalink / raw)
To: Jeff Davis <pgsql@j-davis.com>; +Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>; Kyle Spearrin <kspearrin@bitwarden.com>; pgsql-bugs@lists.postgresql.org, Vince Grassia <vgrassia@bitwarden.com>
Hi All,
After sending in the report we did find an issue that was resolved by them
that fixes it on their side (https://github.com/npgsql/efcore.pg/issues/2527).
They haven't released a version with the fix yet but we can wait, A docs
update would be totally fine with us.
Thanks for all your help.
Justin Baur
On Thu, Dec 8, 2022 at 2:59 PM Jeff Davis <pgsql@j-davis.com> wrote:
> On Thu, 2022-12-08 at 19:29 +0100, Peter Eisentraut wrote:
> > The harm is that in the context of database-level collations, the
> > analogous syntax means something different.
>
> I see. I certainly agree that we shouldn't document the
> lc_ctype=lc_collate hack for CREATE COLLATION for the ICU provider,
> because we don't want to encourage it.
>
> Kyle, perhaps after the docs are updated, you can reach out to the
> npgsql project to see if they can fix it on their side?
>
>
> --
> Jeff Davis
> PostgreSQL Contributor Team - AWS
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2022-12-08 20:03 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-12-02 19:14 CREATE COLLATION without LOCALE throws error in v15 Kyle Spearrin <kspearrin@bitwarden.com>
2022-12-06 23:57 ` Jeff Davis <pgsql@j-davis.com>
2022-12-07 12:04 ` Peter Eisentraut <peter.eisentraut@enterprisedb.com>
2022-12-07 17:44 ` Jeff Davis <pgsql@j-davis.com>
2022-12-08 18:29 ` Peter Eisentraut <peter.eisentraut@enterprisedb.com>
2022-12-08 19:59 ` Jeff Davis <pgsql@j-davis.com>
2022-12-08 20:02 ` Kyle Spearrin <kspearrin@bitwarden.com>
2022-12-08 20:03 ` Justin Baur <jbaur@bitwarden.com>
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