Partial index on varchar fields with IN

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

Responses

Browse pgsql-bugs by date

  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

Browse pgsql-hackers by date

  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