Partial index on varchar fields with IN

Поиск
Список
Период
Сортировка
От Guillaume Smet
Тема Partial index on varchar fields with IN
Дата
Msg-id 1d4e0c10703270643s20d012fcud44f4ec6d117cf7c@mail.gmail.com
обсуждение исходный текст
Ответы Re: Partial index on varchar fields with IN  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
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

В списке pgsql-bugs по дате отправления:

Предыдущее
От: "msmbarabino@virgilio.it"
Дата:
Сообщение: Re: Very slow bytea extraction
Следующее
От: Bruce Momjian
Дата:
Сообщение: Re: BUG #3182: Cannot make libpq with BCC 5.5