Tom Lane wrote:
I wrote:
Michael Paesold <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
