Postgres 12: backend crashes when creating non-deterministic collation

Thomas Kellerer wrote:

I was trying to learn how the new non-deterministic collations in v12
work, but the following makes the backend crash:

CREATE COLLATION de_ci (provider = icu, locale = 'de-x-icu',
deterministic = false);

Which leads to:

2019-10-04 11:54:23 CEST LOG: server process (PID 7540) was
terminated by exception 0xC0000005

I might have misunderstood how to use deterministic to create a case-
insensitive collation, but I don't think the backend should crash if
I do something wrong ;)

Yes, there is a bug somewhere. FWIW, it works on my Linux system.

To get a case insensitive collation you'd have to use something like

LOCALE = 'de-DE-u-ks-level2'

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Laurenz Albe schrieb am 04.10.2019 um 16:04:

I was trying to learn how the new non-deterministic collations in v12
work, but the following makes the backend crash:

CREATE COLLATION de_ci (provider = icu, locale = 'de-x-icu',
deterministic = false);

Which leads to:

2019-10-04 11:54:23 CEST LOG: server process (PID 7540) was
terminated by exception 0xC0000005

I might have misunderstood how to use deterministic to create a case-
insensitive collation, but I don't think the backend should crash if
I do something wrong ;)

Yes, there is a bug somewhere. FWIW, it works on my Linux system.

It also works on Windows when I specify "correct" locale names - the above seems to be an edge case.
Is it worth the effort to report that through the bug reporting form?

To get a case insensitive collation you'd have to use something like

LOCALE = 'de-DE-u-ks-level2'

Creating works, but apparently on Windows ICU does not support this.

The following works fine on Linux (returns both rows), but not on Windows (returns nothing)

create collation de_ci (provider = icu, locale = 'de-DE-u-ks-level2', deterministic = false);
create table test (name text);
insert into test values ('FOO'), ('Foo');

select *
from test
where name = 'foo' collate de_ci;

Not a big deal, but might surprise some people.

Thomas

Thomas Kellerer wrote:

It also works on Windows when I specify "correct" locale names - the above
seems to be an edge case.
Is it worth the effort to report that through the bug reporting form?

Sure. Both the crash with 'de-x-icu' and the difference in behavior
between Linux and Windows on locale = 'de-DE-u-ks-level2' look like
"must-fix" bugs to me. Both are valid ICU locales and should work the
same in all operating systems.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Am 04.10.19 um 12:13 schrieb Thomas Kellerer:

I was trying to learn how the new non-deterministic collations in v12
work, but the following makes the backend crash:

CREATE COLLATION de_ci (provider = icu, locale = 'de-x-icu',
deterministic = false);

Which leads to:

2019-10-04 11:54:23 CEST   LOG:  server process (PID 7540) was
terminated by exception 0xC0000005
2019-10-04 11:54:23 CEST   DETAIL:  Failed process was running:
    CREATE COLLATION de_ci (provider = icu, locale = 'de-x-icu',
deterministic = false)
2019-10-04 11:54:23 CEST   HINT:  See C include file "ntstatus.h" for
a description of the hexadecimal value.
2019-10-04 11:54:23 CEST   LOG:  terminating any other active server
processes
2019-10-04 11:54:23 CEST   WARNING:  terminating connection because of
crash of another server process
2019-10-04 11:54:23 CEST   DETAIL:  The postmaster has commanded this
server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.

This is on Windows 10 with the Postgres 12 binaries from EDB.
Exact Postgres version is: PostgreSQL 12.0, compiled by Visual C++
build 1914, 64-bit
The database was pg_upgraded if that makes any difference

works for me, with:

psql (12rc1 (Ubuntu 12~rc1-1.pgdg18.04+1))
Type "help" for help.

test=# CREATE COLLATION de_ci (provider = icu, locale = 'de-x-icu',
deterministic = false);
CREATE COLLATION
test=*# commit;
COMMIT
test=#

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Thomas Kellerer <spam_eater@gmx.net> writes:

It also works on Windows when I specify "correct" locale names - the above seems to be an edge case.
Is it worth the effort to report that through the bug reporting form?

No, this thread is a sufficient report. What *would* be a good use
of time is to get a stack trace from the crash, if you can.

regards, tom lane

On 2019-10-04 10:52:38 -0400, Tom Lane wrote:

Thomas Kellerer <spam_eater@gmx.net> writes:

It also works on Windows when I specify "correct" locale names - the above seems to be an edge case.
Is it worth the effort to report that through the bug reporting form?

No, this thread is a sufficient report. What *would* be a good use
of time is to get a stack trace from the crash, if you can.

FWIW, https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Windows
might be helpful.

Thomas Kellerer wrote:

To get a case insensitive collation you'd have to use something like

LOCALE = 'de-DE-u-ks-level2'

Creating works, but apparently on Windows ICU does not support this.

After installing v12 on windows with the EDB installer, I notice
that it ships with ICU 53, a relatively old version (2014).

Concerning the problem just above (not the crash), ICU 53 is too old
to support BCP47 tags as collation attributes, as mentioned
at https://www.postgresql.org/docs/12/collation.html :

"The first example selects the ICU locale using a “language tag” per
BCP 47. The second example uses the traditional ICU-specific locale
syntax. The first style is preferred going forward, but it is not
supported by older ICU versions.

With ICU 53 or older, instead of the locale above, we must use the
old-style syntax:

locale = 'de-DE@colStrength=secondary'

If you use that in your example, the case insensitive lookups should
work.

But it's unfortunate that the EDB build did not switch to a recent ICU
version for PostgreSQL 12.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Daniel Verite schrieb am 04.10.2019 um 18:49:

Creating works, but apparently on Windows ICU does not support this.

After installing v12 on windows with the EDB installer, I notice
that it ships with ICU 53, a relatively old version (2014).

Concerning the problem just above (not the crash), ICU 53 is too old
to support BCP47 tags as collation attributes, as mentioned
at https://www.postgresql.org/docs/12/collation.html :

"The first example selects the ICU locale using a “language tag” per
BCP 47. The second example uses the traditional ICU-specific locale
syntax. The first style is preferred going forward, but it is not
supported by older ICU versions.

With ICU 53 or older, instead of the locale above, we must use the
old-style syntax:

locale = 'de-DE@colStrength=secondary'

If you use that in your example, the case insensitive lookups should
work.

That indeed works, thanks a lot.

Tom Lane schrieb am 04.10.2019 um 16:52:

Is it worth the effort to report that through the bug reporting form?

No, this thread is a sufficient report. What *would* be a good use
of time is to get a stack trace from the crash, if you can.

I don't know if I did everything correctly, but here it is. I hope it helps

icuuc53.dll!0000000064964a80() Unbekannt

icuuc53.dll!0000000064964c2d() Unbekannt
icuuc53.dll!0000000064966328() Unbekannt
icuuc53.dll!0000000064965469() Unbekannt
icuuc53.dll!000000006495ef28() Unbekannt
icuuc53.dll!0000000064961501() Unbekannt
icuuc53.dll!000000006495b330() Unbekannt
icuuc53.dll!0000000064959b9e() Unbekannt
icuin53.dll!0000000064a8bd92() Unbekannt
postgres.exe!get_collation_actual_version(char collprovider, const char * collcollate) Zeile 1533 C
postgres.exe!DefineCollation(ParseState * pstate, List * names, List * parameters, bool if_not_exists) Zeile 218 C
postgres.exe!ProcessUtilitySlow(ParseState * pstate, PlannedStmt * pstmt, const char * queryString, ProcessUtilityContext context, ParamListInfoData * params, QueryEnvironment * queryEnv, _DestReceiver * dest, char * completionTag) Zeile 1292 C
postgres.exe!standard_ProcessUtility(PlannedStmt * pstmt, const char * queryString, ProcessUtilityContext context, ParamListInfoData * params, QueryEnvironment * queryEnv, _DestReceiver * dest, char * completionTag) Zeile 933 C
postgres.exe!ProcessUtility(PlannedStmt * pstmt, const char * queryString, ProcessUtilityContext context, ParamListInfoData * params, QueryEnvironment * queryEnv, _DestReceiver * dest, char * completionTag) Zeile 363 C
postgres.exe!PortalRunUtility(PortalData * portal, PlannedStmt * pstmt, bool isTopLevel, bool setHoldSnapshot, _DestReceiver * dest, char * completionTag) Zeile 1184 C
postgres.exe!PortalRunMulti(PortalData * portal, bool isTopLevel, bool setHoldSnapshot, _DestReceiver * dest, _DestReceiver * altdest, char * completionTag) Zeile 1323 C
postgres.exe!PortalRun(PortalData * portal, long count, bool isTopLevel, bool run_once, _DestReceiver * dest, _DestReceiver * altdest, char * completionTag) Zeile 800 C
postgres.exe!exec_execute_message(const char * portal_name, long max_rows) Zeile 2098 C
postgres.exe!PostgresMain(int argc, char * * argv, const char * dbname, const char * username) Zeile 4299 C
[Inlineframe] postgres.exe!BackendRun(Port *) Zeile 4431 C
postgres.exe!SubPostmasterMain(int argc, char * * argv) Zeile 4953 C
postgres.exe!main(int argc, char * * argv) Zeile 216 C

So it happens somewhere inside the ICU DLL - but I don't have the symbols for that

Thomas Kellerer <spam_eater@gmx.net> writes:

Tom Lane schrieb am 04.10.2019 um 16:52:

No, this thread is a sufficient report. What *would* be a good use
of time is to get a stack trace from the crash, if you can.

I don't know if I did everything correctly, but here it is. I hope it helps

icuuc53.dll!0000000064964a80() Unbekannt

icuuc53.dll!0000000064964c2d() Unbekannt
icuuc53.dll!0000000064966328() Unbekannt
icuuc53.dll!0000000064965469() Unbekannt
icuuc53.dll!000000006495ef28() Unbekannt
icuuc53.dll!0000000064961501() Unbekannt
icuuc53.dll!000000006495b330() Unbekannt
icuuc53.dll!0000000064959b9e() Unbekannt
icuin53.dll!0000000064a8bd92() Unbekannt
postgres.exe!get_collation_actual_version(char collprovider, const char * collcollate) Zeile 1533 C
postgres.exe!DefineCollation(ParseState * pstate, List * names, List * parameters, bool if_not_exists) Zeile 218 C
postgres.exe!ProcessUtilitySlow(ParseState * pstate, PlannedStmt * pstmt, const char * queryString, ProcessUtilityContext context, ParamListInfoData * params, QueryEnvironment * queryEnv, _DestReceiver * dest, char * completionTag) Zeile 1292 C
postgres.exe!standard_ProcessUtility(PlannedStmt * pstmt, const char * queryString, ProcessUtilityContext context, ParamListInfoData * params, QueryEnvironment * queryEnv, _DestReceiver * dest, char * completionTag) Zeile 933 C

Hm. This trace says that the crash happened somewhere down inside ICU
itself, during the ucol_open() call in get_collation_actual_version().
There isn't much we could have done to mess up the arguments to that
function. That would seem to mean that it's ICU's bug not ours.
Maybe another reason not to be using such an old ICU version :-(.

regards, tom lane

Tom Lane schrieb am 04.10.2019 um 19:36:

Hm. This trace says that the crash happened somewhere down inside ICU
itself, during the ucol_open() call in get_collation_actual_version().
There isn't much we could have done to mess up the arguments to that
function. That would seem to mean that it's ICU's bug not ours.
Maybe another reason not to be using such an old ICU version :-(.

I would like to test this with a newer ICU version.

So I managed to setup the build environment with Visual Studio, but I can't figure out how to enable ICU for the build.

I created a config.pl to specify the location of the downloaded ICU libraries with the following content

# Configuration arguments for vcbuild.
use strict;
use warnings;

our $config = {
icu => "d:\Projects\postgres\libs\icu\lib64\" # --with-icu=<path>
};

The build is successful, but when I run "install targetfolder" no ICU libraries are included and pg_config only shows:

CONFIGURE = --enable-thread-safety --with-ldap --without-zlib

I have no idea what I am missing.

I also tried building with Msys2 but even when I run configure with the --with-icu option, no ICU DLLs are copied

Regards
Thomas

Thomas Kellerer schrieb am 05.10.2019 um 13:39:

Hm.  This trace says that the crash happened somewhere down inside ICU
itself, during the ucol_open() call in get_collation_actual_version().
There isn't much we could have done to mess up the arguments to that
function.  That would seem to mean that it's ICU's bug not ours.
Maybe another reason not to be using such an old ICU version :-(.

I would like to test this with a newer ICU version.

So I managed to setup the build environment with Visual Studio, but I can't figure out how to enable ICU for the build.

Ah, figured it out.

config.pl has a different format compared to config_default.pl

$config->{icu}='d:\Projects\postgres\libs\icu'

did the trick, and postgres was built with ICU support.

I can confirm that with ICU 65 the crash does not occur and the case insensitive comparison works fine as well.

Regards
Thomas

On 2019-10-04 19:36, Tom Lane wrote:

Hm. This trace says that the crash happened somewhere down inside ICU
itself, during the ucol_open() call in get_collation_actual_version().
There isn't much we could have done to mess up the arguments to that
function. That would seem to mean that it's ICU's bug not ours.

Some build farm coverage of Windows+ICU would be nice. We have test
cases in place that might have caught this.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services