Tom Lane wrote:
> I wrote:
>> Michael Paesold <mpaesold(at)gmx(dot)at> writes:
>>> I am seeing a similar unique index bug here...
>>> This is PostgreSQL 8.1.1 on RHEL 3, Intel Xeon (i686).
>> It looks like the problem is that index entries are being inserted out
>> of order.
> After further investigation, it seems that the original sort order of
> the index was not C-locale, but something else --- I can reproduce the
> current index ordering except for a small number of new-ish tuples if
> I sort the data in en_US.
> We go out of our way to prevent the backend's locale from changing after
> initdb. Did you do something to override that?
No, I am sure I did not do anything to change the locale itentionally. The
cluster was initialized with "initdb --no-locale"... (and this is what it
> Another theory is that this is a manifestation of the known problem with
> plperl sometimes changing the backend's locale setting. Is it possible
> that the index was created in a session that had previously run some
> plperl functions?
This is a theory. The whole database was loaded using pg_restore, I still
have the original dump so I will have a look at the dump now. The database
actually contains some plperl functions.
Restoring to a file I find some perhaps interesting facts perhaps relevant:
*) SET check_function_bodies = false;
So at least the syntax checking function should not be called.
*) Old plperl call handler:
The dump from 7.4.x created the public.plperl_call_handler() function,
which I only dropped after the full dump was loaded.
CREATE FUNCTION plperl_call_handler() RETURNS language_handler
AS '$libdir/plperl', 'plperl_call_handler'
ALTER FUNCTION public.plperl_call_handler() OWNER TO postgres;
CREATE TRUSTED PROCEDURAL LANGUAGE plperl HANDLER plperl_call_handler;
*) There is a single plperl function that is only used in a view. (Btw.
this view is totally unrelated to the given table and should never be used
in the same backend session.)
From the points above, I don't think the plperl function should have been
called during load. Perhaps I am mistaken and plperl did really override
the locale setting.
Looking at the environment set for the "postgres" unix user, which is used
to run Postgres, I see that LANG is set to the default value of
en_US.UTF-8. So it seems possible that setting LANG to C here, could fix
This still doesn't explain why the initial sort order is wrong here.
The creation order in the dump is:
CREATE TABLE... (without indexes)
ALTER TABLE ONLY properties ADD CONSTRAINT pk_properties...
Please tell me if you need further information.
In response to
pgsql-hackers by date
|Next:||From: Dmitry Panov||Date: 2005-12-28 12:17:40|
|Subject: WAL logs multiplexing?|
|Previous:||From: Martijn van Oosterhout||Date: 2005-12-28 08:50:26|
|Subject: Re: [Bizgres-general] WAL bypass for INSERT, UPDATE and|