Skip site navigation (1) Skip section navigation (2)

Re: Possible savepoint bug

From: Michael Paesold <mpaesold(at)gmx(dot)at>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Possible savepoint bug
Date: 2005-12-28 10:38:35
Message-ID: 43B26B2B.6000209@gmx.at (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Dmitry PanovDate: 2005-12-28 12:17:40
Subject: WAL logs multiplexing?
Previous:From: Martijn van OosterhoutDate: 2005-12-28 08:50:26
Subject: Re: [Bizgres-general] WAL bypass for INSERT, UPDATE and

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group