Hi!
We're using postgres 6.5.3 for an application where we, among other
things, store information about users. On two separate occations on
two different servers we've experienced problems with indices on the
same table (out of over 100).
The problem is that postgres refuse to find a certain row in the table
when performing a simple select using the primary (unique) key like
this:
select * from elever where userid = 'walther';
When selecting all rows the row in question is shown. When doing the
following, I get the result I want:
select * from elever where userid like 'w%'; <-- OK
select * from elever where userid like 'wa%'; <-- OK
select * from elever where userid like 'wal%'; <-- OK
select * from elever where userid like 'walt%'; <-- OK
but not
select * from elever where userid like 'walth%'; <-- Not OK!
Droping and recreating the index solves the problem, but that's not
good enough for me since the problem has reoccured on a different
machine with a different database. vacuum and vacuum analyze does not
report any problem with the table.
Both times the problem occured with userid's starting with a
"w". Postgres is running with a Swedish locale, and on FreeBSD this
means that "w" and "v" (among a number of other letter) are treated
equally when collating/sorting. I suppose this could be part of the
problem.
Is this a known problem? If so, is it fixed in 7.0.2? I've not seen
this happen on any of our postgres 7.0.2 systems, but as I can't even
reproduce it on the 6.5.3, that's no guarantee it's fixed...
For the record... I know we shouldn't run 6.5.3 and we will upgrade to
7.x within soon...
psql -c 'select version()'
PostgreSQL 6.5.3 on i386-unknown-freebsd4.1, compiled by cc
uname -a
FreeBSD 4.1-RELEASE #0: Tue Aug 29 15:31:01 CEST 2000
Regards,
Patrik Kudo
ech`echo xiun|tr nu oc|sed 'sx\([sx]\)\([xoi]\)xo un\2\1 is xg'`ol
Känns det oklart? Fråga på!
