Thread: 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