Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-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

pgsql-hackers by date

Next:From: Hannu KrosingDate: 2007-03-27 13:44:57
Subject: Re: Guarenteeing complex referencial integrity throughcustom triggers
Previous:From: Gregory StarkDate: 2007-03-27 13:39:20
Subject: Re: Concurrent connections in psql

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2007-03-27 14:10:22
Subject: Re: BUG #3182: Cannot make libpq with BCC 5.5
Previous:From: Ivan PanchenkoDate: 2007-03-27 10:37:25
Subject: Old bug concerning regular expressions and UTF-8

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group