Composite Indexes with a function and a column

Hello

On Fri, 21 Dec 2012 08:46:14 -0200
Alex Pires de Camargo <acamargo@gmail.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-

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@lathspell.de> wrote:

Hello

On Fri, 21 Dec 2012 08:46:14 -0200
Alex Pires de Camargo <acamargo@gmail.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-

--
Alex
acamargo@gmail.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/

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@gmail.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@lathspell.de> wrote:

Hello

On Fri, 21 Dec 2012 08:46:14 -0200
Alex Pires de Camargo <acamargo@gmail.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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Dec 21, 2012 at 10:45 AM, Christian Hammers <ch@lathspell.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@gmail.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@lathspell.de>

wrote:

Hello

On Fri, 21 Dec 2012 08:46:14 -0200
Alex Pires de Camargo <acamargo@gmail.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@gmail.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/