Re: Composite Indexes with a function and a column

On Fri, Dec 21, 2012 at 10:45 AM, Christian Hammers <ch(at)lathspell(dot)de> wrote:

> 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...)
>

Good, ingenious way to make crazy a DBA ;)

Thanks again!

> 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.
>

--
Alex
acamargo(at)gmail(dot)com
"Por que, no mundo, os maus, tão frequentemente, sobrepujam os bons em
influência?
-Pela fraqueza dos bons; Os maus são intrigantes e audaciosos, os bons são
tímidos. Quando estes o quiserem, dominarão." -- Livro dos Espíritos, Q932.
http://livrodosespiritos.wordpress.com/