Обсуждение: 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.
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.
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