Thread: 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