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

Re: Partial index on varchar fields with IN

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Guillaume Smet" <guillaume(dot)smet(at)gmail(dot)com>
Cc: Bugs <pgsql-bugs(at)postgresql(dot)org>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Partial index on varchar fields with IN
Date: 2007-03-27 14:33:00
Message-ID: 19819.1175005980@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
"Guillaume Smet" <guillaume(dot)smet(at)gmail(dot)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

In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2007-03-27 14:59:29
Subject: Re: sorted results on pgbuildfarm
Previous:From: Bruce MomjianDate: 2007-03-27 14:32:41
Subject: Re: Concurrent connections in psql

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2007-03-27 19:08:12
Subject: Re: BUG #2977: dow doesn't conform to ISO-8601
Previous:From: Bruce MomjianDate: 2007-03-27 14:10:22
Subject: Re: BUG #3182: Cannot make libpq with BCC 5.5

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