pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedERROR: DefineIndex: index function must be marked iscachable
6+ messages / 3 participants
[nested] [flat]
* ERROR: DefineIndex: index function must be marked iscachable
@ 2003-07-25 19:09 Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>
2003-07-25 19:21 ` Re: [SQL] ERROR: DefineIndex: index function must be marked iscachable Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-07-25 19:22 ` Re: ERROR: DefineIndex: index function must be marked iscachable Tomasz Myrta <jasiek@klaster.net>
0 siblings, 2 replies; 6+ messages in thread
From: Elielson Fontanezi @ 2003-07-25 19:09 UTC (permalink / raw)
To: pgsql-general <pgsql-general@postgresql.org>; pgsql-sql
Hi all!
What can I do in this case?
I could not found anything about iscachable.
postgres$ cat in.sql
create index bt_proposta_f01 on proposta
using btree (func_cod_secretaria(nr_proponente));
postgres$ psql -d escola -f in.sql
psql:in.sql:2: ERROR: DefineIndex: index function must be marked iscachable
postgres$
>\\\!/< 55 11 5080 9283
!_"""_! Elielson Fontanezi
(O) (o) PRODAM - Technical Support
Analyst
-------------------------------oOOO--(_)--OOOo------------------------------
-----
Success usually comes to those who are too busy to be looking for it.
oooo0 0oooo
---------------------------( )----------(
)--------------------------------
\ ( ) /
\_/ \_/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] ERROR: DefineIndex: index function must be marked iscachable
2003-07-25 19:09 ERROR: DefineIndex: index function must be marked iscachable Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>
@ 2003-07-25 19:21 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-07-25 19:24 ` Re: [SQL] ERROR: DefineIndex: index function must be Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 1 reply; 6+ messages in thread
From: Stephan Szabo @ 2003-07-25 19:21 UTC (permalink / raw)
To: Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>; +Cc: pgsql-general <pgsql-general@postgresql.org>; pgsql-sql
On Fri, 25 Jul 2003, Elielson Fontanezi wrote:
> What can I do in this case?
> I could not found anything about iscachable.
>
> postgres$ cat in.sql
> create index bt_proposta_f01 on proposta
> using btree (func_cod_secretaria(nr_proponente));
>
> postgres$ psql -d escola -f in.sql
> psql:in.sql:2: ERROR: DefineIndex: index function must be marked iscachable
Check the create function reference page, I believe you'd need to add WITH
(iscachable) to the end of the function creation.
iscachable means that the function always returnes the same result when
given the same arguments. It's a prereq to using the function in an
index. If your function doesn't meet those requirements (for example if it
does a query on a table) making an index on that function is a bad idea.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] ERROR: DefineIndex: index function must be
2003-07-25 19:09 ERROR: DefineIndex: index function must be marked iscachable Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>
2003-07-25 19:21 ` Re: [SQL] ERROR: DefineIndex: index function must be marked iscachable Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2003-07-25 19:24 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2003-07-25 19:24 UTC (permalink / raw)
To: Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>; +Cc: pgsql-general <pgsql-general@postgresql.org>; pgsql-sql
On Fri, 25 Jul 2003, Stephan Szabo wrote:
> On Fri, 25 Jul 2003, Elielson Fontanezi wrote:
>
> > What can I do in this case?
> > I could not found anything about iscachable.
>
>
> >
> > postgres$ cat in.sql
> > create index bt_proposta_f01 on proposta
> > using btree (func_cod_secretaria(nr_proponente));
> >
> > postgres$ psql -d escola -f in.sql
> > psql:in.sql:2: ERROR: DefineIndex: index function must be marked iscachable
>
> Check the create function reference page, I believe you'd need to add WITH
> (iscachable) to the end of the function creation.
>
> iscachable means that the function always returnes the same result when
> given the same arguments. It's a prereq to using the function in an
> index. If your function doesn't meet those requirements (for example if it
> does a query on a table) making an index on that function is a bad idea.
Almost forgot. In recent versions you'd specify that the function was
IMMUTABLE and I believe the error message would use such as well. What
version are you using (it might be worth considering an upgrade to 7.4
when it comes out)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ERROR: DefineIndex: index function must be marked iscachable
2003-07-25 19:09 ERROR: DefineIndex: index function must be marked iscachable Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>
@ 2003-07-25 19:22 ` Tomasz Myrta <jasiek@klaster.net>
1 sibling, 0 replies; 6+ messages in thread
From: Tomasz Myrta @ 2003-07-25 19:22 UTC (permalink / raw)
To: Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>; +Cc: pgsql-sql
Dnia 2003-07-25 21:09, Użytkownik Elielson Fontanezi napisał:
> Hi all!
>
> What can I do in this case?
> I could not found anything about iscachable.
>
> postgres$ cat in.sql
> create index bt_proposta_f01 on proposta
> using btree (func_cod_secretaria(nr_proponente));
>
> postgres$ psql -d escola -f in.sql
> psql:in.sql:2: ERROR: DefineIndex: index function must be marked iscachable
> postgres$
You should follow the error. Your function func_cod_secretaria has to be
declared as cacheable. (Look into documentation - sql commands / create
function). IMMUTABLE function should help.
This function has also for some nr_proponente returns always the same
value (look at IMMUTABLE description)
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 6+ messages in thread
* ERROR: DefineIndex: index function must be marked iscachable
@ 2003-07-25 19:43 Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>
2003-07-25 19:47 ` Re: ERROR: DefineIndex: index function must be marked Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 6+ messages in thread
From: Elielson Fontanezi @ 2003-07-25 19:43 UTC (permalink / raw)
To: pgsql-general <pgsql-general@postgresql.org>; pgsql-sql
Hi!
Who can help me on that?
First of all, my envoronment is:
Linux netlab142.prodam 2.4.8-26mdk #1 Sun Sep 23 17:06:39 CEST 2001
i686 unknown
pg_ctl (PostgreSQL) 7.2.
Problem: ERROR: DefineIndex: index function must be marked iscachable
by executing:
create index bt_proposta_f01 on proposta
using btree (func_cod_secretaria(nr_proponente));
Where nr_proponte is a integer type column.
The function is:
CREATE OR REPLACE FUNCTION func_cod_secretaria(INTEGER) RETURNS INTEGER AS '
DECLARE
v_nr_proponente ALIAS FOR $1;
BEGIN
return TRUNC(v_nr_proponente/100000,0)*100000
END;
' LANGUAGE 'plpgsql';
>\\\!/< 55 11 5080 9283
!_"""_! Elielson Fontanezi
(O) (o) PRODAM - Technical Support
Analyst
-------------------------------oOOO--(_)--OOOo------------------------------
-----
Success usually comes to those who are too busy to be looking for it.
oooo0 0oooo
---------------------------( )----------(
)--------------------------------
\ ( ) /
\_/ \_/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ERROR: DefineIndex: index function must be marked
2003-07-25 19:43 ERROR: DefineIndex: index function must be marked iscachable Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>
@ 2003-07-25 19:47 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2003-07-25 19:47 UTC (permalink / raw)
To: Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>; +Cc: pgsql-general <pgsql-general@postgresql.org>; pgsql-sql
On Fri, 25 Jul 2003, Elielson Fontanezi wrote:
> Who can help me on that?
>
> First of all, my envoronment is:
> Linux netlab142.prodam 2.4.8-26mdk #1 Sun Sep 23 17:06:39 CEST 2001
> i686 unknown
> pg_ctl (PostgreSQL) 7.2.
You should definately move to the highest 7.2 release (7.2.4 I think)
which shouldn't require a restore (although you should back up first in
any case). I think there were some reasonably important fixes between
7.2.1 and 7.2.4.
> CREATE OR REPLACE FUNCTION func_cod_secretaria(INTEGER) RETURNS INTEGER AS '
> DECLARE
> v_nr_proponente ALIAS FOR $1;
> BEGIN
> return TRUNC(v_nr_proponente/100000,0)*100000
> END;
> ' LANGUAGE 'plpgsql'
Add WITH (iscachable) after LANGUAGE 'plpgsql' to make the function usable
in the functional index unless I'm misremembering the old syntax.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-07-25 19:47 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-25 19:09 ERROR: DefineIndex: index function must be marked iscachable Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>
2003-07-25 19:21 ` Re: [SQL] ERROR: DefineIndex: index function must be marked iscachable Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-07-25 19:24 ` Re: [SQL] ERROR: DefineIndex: index function must be Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-07-25 19:22 ` Tomasz Myrta <jasiek@klaster.net>
2003-07-25 19:43 ERROR: DefineIndex: index function must be marked iscachable Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>
2003-07-25 19:47 ` Re: ERROR: DefineIndex: index function must be marked Stephan Szabo <sszabo@megazone.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox