Re: Possible savepoint bug

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
still is).

> 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'
LANGUAGE c;
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
the problem.

This still doesn't explain why the initial sort order is wrong here.

The creation order in the dump is:

CREATE TABLE... (without indexes)
COPY ...
ALTER TABLE ONLY properties ADD CONSTRAINT pk_properties...

Please tell me if you need further information.

Best Regards,
Michael Paesold