Re: Possible savepoint bug

Tom Lane wrote:
> I wrote:
>> Michael Paesold <mpaesold@gmx.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'
LANGUAGEc;
 
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