Thread: 8.4.0 vs. locales vs. pl/perl?

Having been helping someone out on IRC with what looked initially like
an index corruption problem, it turns out to be (almost certainly) a
locale initialization error.

environment: debian packaged 8.4.0

scenario: restoring a dump results in incorrect indexes for some
specific tables (indexscan order fails to match < comparison or
order resulting from explicit sorts). The dump contains plperl
and plperlu language creation and function definitions; excising
these from the dump removes the problem.

The db locale is en_US.UTF-8 (which appears to be case-insensitive on
this platform: 'C' > 'd' > 'X'), but the incorrect index ordering that
results when the full restore is done is consistent with C locale
(i.e. 'C' > 'X' > 'd').

Looking at the code, the obvious thing that glares out is that the
locale setup in CheckMyDatabase is calling setlocale rather than
pg_perm_setlocale... am I missing something, or is this an obvious
bug?

-- 
Andrew (irc:RhodiumToad)

Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> Looking at the code, the obvious thing that glares out is that the
> locale setup in CheckMyDatabase is calling setlocale rather than
> pg_perm_setlocale... am I missing something, or is this an obvious
> bug?

Sigh, it's an obvious bug.  Whoever copied xlog.c's "pg_perm_setlocale"
calls as "setlocale" needs to get out the sackcloth and ashes.
        regards, tom lane

Andrew Gierth wrote:
> environment: debian packaged 8.4.0
> 
> scenario: restoring a dump results in incorrect indexes for some
> specific tables (indexscan order fails to match < comparison or
> order resulting from explicit sorts). The dump contains plperl
> and plperlu language creation and function definitions; excising
> these from the dump removes the problem.
> 
> The db locale is en_US.UTF-8 (which appears to be case-insensitive on
> this platform: 'C' > 'd' > 'X'), but the incorrect index ordering that
> results when the full restore is done is consistent with C locale
> (i.e. 'C' > 'X' > 'd').
> 
> Looking at the code, the obvious thing that glares out is that the
> locale setup in CheckMyDatabase is calling setlocale rather than
> pg_perm_setlocale... am I missing something, or is this an obvious
> bug?

Looks like an obvious bug. Looking at the archives, it was present in
the collation / per-database locale patches from the beginning, and I
missed it during review. I'll go fix it, thanks for the analysis!

--  Heikki Linnakangas EnterpriseDB   http://www.enterprisedb.com