Re: Composite Indexes with a function and a column

Hallo

A function that is used as part of an index has at least to be declared
immutable:

devel_np=# CREATE OR REPLACE FUNCTION f() RETURNS int AS $$ BEGIN return (random()*100)::int; END; $$ VOLATILE LANGUAGE plpgsql;
CREATE FUNCTION

devel_np=# CREATE INDEX ON t (f(), i);
ERROR: functions in index expression must be marked IMMUTABLE

Of couse, you can just declare your function as "IMMUTABLE" and still call
random() or access other tables in it if you think you know what you're doing.

(I wonder if it's somehow possible to get PostgreSQL into an endless loop or
crash by doing an ORDER BY which uses an index that returns random values...)

bye,

-christian-

On Fri, 21 Dec 2012 10:31:43 -0200
Alex Pires de Camargo <acamargo(at)gmail(dot)com> wrote:

> Thanks a lot!
>
> From documentation:
>
> "
> IMMUTABLE indicates that the function cannot modify the database and always
> returns the same result when given the same argument values; that is, it
> does not do database lookups or otherwise use information not directly
> present in its argument list. If this option is given, any call of the
> function with all-constant arguments can be immediately replaced with the
> function value.
> "
>
> I understand that to be immutable a function should not access mutable data.
>
> If my function access another table that I have guarantee that it will not
> be changed, It's safe to turn that function immutable and use in an index?
> I know that i'll be punished if my guarantee fails...
>
> Regards,
>
>
> On Fri, Dec 21, 2012 at 10:12 AM, Christian Hammers <ch(at)lathspell(dot)de> wrote:
>
> > Hello
> >
> > On Fri, 21 Dec 2012 08:46:14 -0200
> > Alex Pires de Camargo <acamargo(at)gmail(dot)com> wrote:
> >
> > > Is it possible?
> > >
> > > Thanks!
> >
> > Why not?
> >
> > devel_np=# CREATE TABLE t (i int);
> > CREATE TABLE
> >
> > devel_np=# CREATE INDEX ON t (length(i::text), i);
> > CREATE INDEX
> >
> > bye,
> >
> > -christian-
> >
>
>
>

--
Network Engineering & Design; Content Delivery Platform & IP

NETCOLOGNE Gesellschaft für Telekommunikation mbH
Am Coloneum 9 | 50829 Köln
Tel: 0221 2222-8711 | Fax: 0221 2222-78711
www.netcologne.de

Geschäftsführer:
Dr. Hans Konle (Sprecher)
Dipl.-Ing. Karl-Heinz Zankel
HRB 25580, AG Köln

Diese Nachricht (inklusive aller Anhänge) ist vertraulich. Sollten Sie diese
Nachricht versehentlich erhalten haben, bitten wir, den Absender (durch
Antwort-E-Mail) hiervon unverzüglich zu informieren und die Nachricht zu
löschen. Die E-Mail darf in diesem Fall weder vervielfältigt noch in anderer
Weise verwendet werden.