Обсуждение: Partial index on varchar fields with IN

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

"Guillaume Smet" <guillaume.smet@gmail.com> writes:
> 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

Hmm.  This is generating a coercion from varchar[] to text[], and the
problem is that array_type_coerce() is marked "stable".  That seemed
like a good idea at the time (I think the idea was that it looks at
system catalog entries that might change), but on reflection it's
obviously broken --- it might be either too liberal or too conservative
depending on what the volatility of the underlying per-element coercion
function is.

I think that to make this sort of thing work correctly, we need array
coercions to expose the underlying coercion function's OID in the
expression tree, where it could be seen by tree traversals that look
for things like volatile functions.  I don't see any good way to do
that using the current representation that array_type_coerce() is a
type coercion function just like any other.  I'm thinking we have to
get rid of it as a function and instead have an expression node type
ArrayCoerce, or maybe ArrayMap would be a better name --- the semantics
would basically be "apply the indicated function to every element of
the input array".  One nice thing is that that'd eliminate the runtime
lookup that currently has to be done by array_type_coerce().

Comments?  Does anyone see a way to fix it that would be back-patchable?
(A new node type definitely isn't...)
        regards, tom lane