localization problem (and solution)

Here is a test case for a previously reported bug (see
http://archives.postgresql.org/pgsql-general/2005-11/msg01235.php)

initdb using es_MX.ISO-8859-1, start postgres using es_MX.UTF-8 and
execute:

create procedural language plperl;
create or replace function foo() returns int as 'return 1' language 'plperl';
create table persona (nombre text check (nombre ~ '^[[:upper:]][[:lower:]]*([-''. [:alpha:]]+)?$'::text));
copy persona (nombre) from stdin;
José
\.

It will error out saying:

ERROR: new row for relation "persona" violates check constraint "persona_nombre_check"
CONTEXT: COPY persona, line 1: "José"

Commenting the creation of the plperl function (or moving it after the copy
command) this script runs without errors. Also applying this patch solves
the problem:

*** src/backend/access/transam/xlog.c~ 2005-11-22 12:23:05.000000000 -0600
--- src/backend/access/transam/xlog.c 2005-12-19 20:34:22.000000000 -0600
***************
*** 3626,3631 ****
--- 3626,3632 ----
" which is not recognized by setlocale().",
ControlFile->lc_collate),
errhint("It looks like you need to initdb or install locale support.")));
+ setenv("LC_COLLATE", ControlFile->lc_collate, 1);
if (setlocale(LC_CTYPE, ControlFile->lc_ctype) == NULL)
ereport(FATAL,
(errmsg("database files are incompatible with operating system"),
***************
*** 3633,3638 ****
--- 3634,3640 ----
" which is not recognized by setlocale().",
ControlFile->lc_ctype),
errhint("It looks like you need to initdb or install locale support.")));
+ setenv("LC_CTYPE", ControlFile->lc_ctype, 1);

/* Make the fixed locale settings visible as GUC variables, too */
SetConfigOption("lc_collate", ControlFile->lc_collate,

Some fprintf's around the regex code shows that someone is changing
the localization parameters by those found in the enviroment, at least
for the LC_CTYPE and LC_COLLATE categories, and plperl seems to be the
culprit. Needless to say that this bug might lead to index corruption
beside other problems. It also explains some very wired (and very
difficult to reproduce) anomalies I have seen.

Regards,
Manuel.