Thread: [GENERAL] Not able to create collation on Windows

Murtuza Zabuawala <murtuza.zabuawala@enterprisedb.com> writes:
> I am trying to create collation on windows using default POSIX collation
> with pgAdmin3 but I am getting error as shown in screenshot, Can someone
> suggest how to fix this?

> *Syntax:*
> CREATE COLLATION public.test from pg_catalog."POSIX";

> *Error:*
> ERROR: could not create locale "POSIX". No error

Hmm.  Evidently Windows' _create_locale() doesn't accept "POSIX".
You might find that "C" works instead, don't know for sure.

I think this is actually a bug, because the collations code clearly
means to allow clones of the C/POSIX locales --- see eg lc_collate_is_c,
which could be noticeably simpler if that case weren't contemplated.
However, DefineCollation checks validity of the new collation by
unconditionally calling pg_newlocale_from_collation().  That violates
the advice in pg_newlocale_from_collation's header comment:

 * Also, callers should avoid calling this before going down a C/POSIX
 * fastpath, because such a fastpath should work even on platforms without
 * locale_t support in the C library.

Every other call site honors that.

So I think what we ought to do is change DefineCollation more or
less like this:

-    (void) pg_newlocale_from_collation(newoid);
+    if (!lc_collate_is_c(newoid) || !lc_ctype_is_c(newoid))
+        (void) pg_newlocale_from_collation(newoid);

Another issue exposed by this report is that we aren't reporting
_create_locale() failures in a useful way.  It's possible this
could be improved by inserting

#ifdef WIN32
    _dosmaperr(GetLastError());
#endif

into report_newlocale_failure in pg_locale.c, but I'm not really
sure.  Microsoft's man page for _create_locale() fails to say much
of anything about its error-case behavior, and definitely does not
say that it sets the GetLastError indicator.  Still, there's certainly
no chance that printing errno without doing this will be useful.
I would suggest that we do that for starters, and if we hear that
we're still getting silly errors, just hot-wire the code to assume
ENOENT on Windows.

            regards, tom lane

On 8/1/17 10:53, Tom Lane wrote:
> Murtuza Zabuawala <murtuza.zabuawala@enterprisedb.com> writes:
>> I am trying to create collation on windows using default POSIX collation
>> with pgAdmin3 but I am getting error as shown in screenshot, Can someone
>> suggest how to fix this?
>
>> *Syntax:*
>> CREATE COLLATION public.test from pg_catalog."POSIX";
>
>> *Error:*
>> ERROR: could not create locale "POSIX". No error
>
> Hmm.  Evidently Windows' _create_locale() doesn't accept "POSIX".
> You might find that "C" works instead, don't know for sure.
>
> I think this is actually a bug, because the collations code clearly
> means to allow clones of the C/POSIX locales --- see eg lc_collate_is_c,

You seem to say that we should support a "POSIX" locale even on systems
where the C library does not support that.  I'm not convinced about that.

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

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> On 8/1/17 10:53, Tom Lane wrote:
>> I think this is actually a bug, because the collations code clearly
>> means to allow clones of the C/POSIX locales --- see eg lc_collate_is_c,

> You seem to say that we should support a "POSIX" locale even on systems
> where the C library does not support that.  I'm not convinced about that.

Uh, we already do.  Note all the regression tests that unconditionally
assume that the POSIX collation works.  Also, I am confused by your
apparent belief that there might somewhere be a version of libc that
fails to provide C-locale-compliant behavior.  Surely nobody would
tolerate a version of strcmp() that fails to act per C spec.

            regards, tom lane

Murtuza Zabuawala <murtuza.zabuawala@enterprisedb.com> writes:
> Yes, I was able to create collation using "C" instead of "POSIX" on windows,
> CREATE COLLATION public.test from pg_catalog."C";

Yeah, I thought that might happen.  So the point basically is that in
almost all of the collations code, the "C" and "POSIX" names are handled
by dedicated code paths that don't care what the system's locale support
thinks.  But we missed that for CREATE COLLATION.  Aside from the case
you ran into, this means you can't do CREATE COLLATION ... FROM "C"
at all on platforms that lack HAVE_LOCALE_T.  There's no good reason
for that IMO; not if we're one line of code away from allowing it.

            regards, tom lane