Regex select ~ with ^ not using index on char field or text; Locale bug?
Just migrated a database from a RH6.1 + PG 7.0.3 system to a RH7.1 + PG
7.1 (rpms from ftp.postgresql.org (postgresql-server-7.1-1)).
On performing queries such as select * from table where name ~ '^NAME.*';
the database fails to use the available Btree index where available, even
when the database is told not to use the SEQ_SCAN optimization.
Figured it was a locale related bug..
PGsql was initlized and is started with the scripts in the RPM, so
LC_ALL=C is set. PSQL is also run with LC_ALL=C.
Am I going to need to hack the .spec to remove --enable-locale and
recompile?
--
The comments and opinions expressed herein are those of the author of this
message and may not reflect the policies of the Martin County Board of
County Commissioners.
Greg Maxwell <gmaxwell@martin.fl.us> writes:
On performing queries such as select * from table where name ~ '^NAME.*';
the database fails to use the available Btree index where available, even
when the database is told not to use the SEQ_SCAN optimization.
PGsql was initlized and is started with the scripts in the RPM, so
LC_ALL=C is set. PSQL is also run with LC_ALL=C.
Are you *certain* the DB was initialized in locale C? Try running
contrib/pg_controldata to be sure.
regards, tom lane
On Mon, 30 Apr 2001, Tom Lane wrote:
Greg Maxwell <gmaxwell@martin.fl.us> writes:
On performing queries such as select * from table where name ~ '^NAME.*';
the database fails to use the available Btree index where available, even
when the database is told not to use the SEQ_SCAN optimization.PGsql was initlized and is started with the scripts in the RPM, so
LC_ALL=C is set. PSQL is also run with LC_ALL=C.Are you *certain* the DB was initialized in locale C? Try running
contrib/pg_controldata to be sure.
If it wasn't, the start scripts in the RPM are broken.
Furthermore, I have reinited twice, very carefully and manually set all
the LC enviroment vars and still had the same result.
I wasn't aware of the pg_controldata program, I saw in other posts that
there was something in contrib to check this, but I couldn't find it.
Because of this, I changed the specfile to remove locale support, and
can't do further testing.. Sorry I didn't get your mail earlier.
Later this week, I'll be taking another system to RH7.1 + PG7.1, so I'll
let you know if I encounter the problem again.