Re: [BUGS] Partial index on varchar fields with IN - Mailing list pgsql-hackers

From Tom Lane
Subject Re: [BUGS] Partial index on varchar fields with IN
Date
Msg-id 19819.1175005980@sss.pgh.pa.us
Whole thread Raw
List pgsql-hackers
"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


pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: notification payloads
Next
From: Bruce Momjian
Date:
Subject: Re: Concurrent connections in psql