From: | "Guillaume Smet" <guillaume(dot)smet(at)gmail(dot)com> |
---|---|
To: | Bugs <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Partial index on varchar fields with IN |
Date: | 2007-03-27 13:43:36 |
Message-ID: | 1d4e0c10703270643s20d012fcud44f4ec6d117cf7c@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-hackers |
Hi all,
I recently came accross a problem with the use of IN clause in a
partial index with a varchar(3) field. Int, char and text seems to be
OK (test case is provided below).
Version is 8.2.3 running on a Fedora Core 3 (RPM rebuilt from the PGDG ones).
test=# SELECT version();
version
-------------------------------------------------------------------------------------------------------
PostgreSQL 8.2.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC)
3.4.4 20050721 (Red Hat 3.4.4-2)
(1 ligne)
Here is the test case:
test=# CREATE TABLE test_in (field int);
CREATE TABLE
test=# CREATE INDEX idx_test_in ON test_in(field) WHERE field IN(1, 2);
CREATE INDEX
test=# DROP INDEX idx_test_in;
DROP INDEX
test=# DROP TABLE test_in;
DROP TABLE
-> OK with int
test=# CREATE TABLE test_in (field text);
CREATE TABLE
test=# CREATE INDEX idx_test_in ON test_in(field) WHERE field IN('1', '2');
CREATE INDEX
test=# DROP INDEX idx_test_in;
DROP INDEX
test=# DROP TABLE test_in;
DROP TABLE
-> OK with text
test=# CREATE TABLE test_in (field char(3));
CREATE TABLE
test=# CREATE INDEX idx_test_in ON test_in(field) WHERE field IN('1', '2');
CREATE INDEX
test=# DROP INDEX idx_test_in;
DROP INDEX
test=# DROP TABLE test_in;
DROP TABLE
-> OK with char(3)
test=# CREATE TABLE test_in (field varchar(3));
CREATE TABLE
test=# CREATE INDEX idx_test_in ON test_in(field) WHERE field IN('1', '2');
ERROR: functions in index predicate must be marked IMMUTABLE
-> failed with varchar(3)
This behaviour seems inconsistent and it seems to me it can be a bug.
Any comment?
--
Guillaume
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2007-03-27 14:10:22 | Re: BUG #3182: Cannot make libpq with BCC 5.5 |
Previous Message | Ivan Panchenko | 2007-03-27 10:37:25 | Old bug concerning regular expressions and UTF-8 |
From | Date | Subject | |
---|---|---|---|
Next Message | Hannu Krosing | 2007-03-27 13:44:57 | Re: Guarenteeing complex referencial integrity through custom triggers |
Previous Message | Gregory Stark | 2007-03-27 13:39:20 | Re: Concurrent connections in psql |