From ElielsonF@prodam.sp.gov.br Fri Jul 25 19:08:07 2003 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 998E2D1C4CC; Fri, 25 Jul 2003 19:08:07 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 65701-02; Fri, 25 Jul 2003 16:07:17 -0300 (ADT) Received: from sampa7.prodam.sp.gov.br (unknown [200.230.190.107]) by svr1.postgresql.org (Postfix) with ESMTP id 11CD5D1C4B7; Fri, 25 Jul 2003 16:07:53 -0300 (ADT) Received: from cprodamibs249.PRODAM (cprodamibs249.prodam.sp.gov.br [10.10.64.249]) by sampa7.prodam.sp.gov.br with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id PTQ3N9MC; Fri, 25 Jul 2003 16:07:49 -0300 Received: by cprodamibs249.prodam with Internet Mail Service (5.5.2653.19) id ; Fri, 25 Jul 2003 16:09:28 -0300 Message-ID: From: Elielson Fontanezi To: pgsql-general , pgsql-sql Subject: ERROR: DefineIndex: index function must be marked iscachable Date: Fri, 25 Jul 2003 16:09:27 -0300 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C352E0.1AEC46B0" X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.4 tagged_above=0.0 required=5.0 tests=HTML_50_60, HTML_FONT_FACE_ODD X-Spam-Level: X-Archive-Number: 200307/1246 X-Sequence-Number: 46099 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C352E0.1AEC46B0 Content-Type: text/plain; charset="iso-8859-1" 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 ---------------------------( )----------( )-------------------------------- \ ( ) / \_/ \_/ ------_=_NextPart_001_01C352E0.1AEC46B0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

= Hi=20 all!

What can I do in this=20 case?

I could not found anything ab= out=20 iscachable.

postgre= s$ cat=20 in.sql
create index bt_proposta_f01 on proposta
using btree=20 (func_cod_secretaria(nr_proponente));

pos= tgres$ psql=20 -d escola -f in.sql
psql:in.sql:2: ERROR: DefineIndex: index funct= ion=20 must be marked iscachable
postgres$

   &nb= sp;            =             &nb= sp;     =20 >\\\!/<     55=20 11 5080=20 9283
           &= nbsp;           &nbs= p;          =20 !_"""_!     Eli= elson=20 Fontanezi
          &n= bsp;            = ;           =20 (O) (o)     PR= ODAM -=20 Technical Support=20 Analyst
-------------------------------oOOO--(_)--OOOo------------------= -----------------
    =20 Success=20 usually comes to those who are too busy to be looking for=20 it.
           &n= bsp;            = ;   =20 oooo0  &nbs= p;      =20 0oooo
---------------------------(   =20 )----------(   =20 )--------------------------------
      &n= bsp;            = ;        =20 \ (           = =20 ) =20 /
           &nbs= p;            &= nbsp;   =20 \_/           =20 \_/

------_=_NextPart_001_01C352E0.1AEC46B0-- From sszabo@megazone.bigpanda.com Fri Jul 25 19:21:17 2003 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 896F6D1C4BC; Fri, 25 Jul 2003 19:21:17 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 60091-10; Fri, 25 Jul 2003 16:20:30 -0300 (ADT) Received: from megazone.bigpanda.com (unknown [66.180.233.26]) by svr1.postgresql.org (Postfix) with ESMTP id D64A9D1C4B7; Fri, 25 Jul 2003 16:21:15 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 43B22D62D; Fri, 25 Jul 2003 12:21:15 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 314F45C0E; Fri, 25 Jul 2003 12:21:15 -0700 (PDT) Date: Fri, 25 Jul 2003 12:21:15 -0700 (PDT) From: Stephan Szabo To: Elielson Fontanezi Cc: pgsql-general , pgsql-sql Subject: Re: [SQL] ERROR: DefineIndex: index function must be marked iscachable In-Reply-To: Message-ID: <20030725121618.G37941-100000@megazone.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/1247 X-Sequence-Number: 46100 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. From jasiek@klaster.net Fri Jul 25 19:22:36 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 7BF3BD1C4CC for ; Fri, 25 Jul 2003 19:22:36 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 65557-04 for ; Fri, 25 Jul 2003 16:21:49 -0300 (ADT) Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66]) by svr1.postgresql.org (Postfix) with ESMTP id 745D5D1C4BC for ; Fri, 25 Jul 2003 16:22:34 -0300 (ADT) Received: from klaster.net (core-1.citynet.pl [80.48.135.69]) by serwer.skawsoft.com.pl (Postfix) with ESMTP id 1B0112B3CF; Fri, 25 Jul 2003 21:22:26 +0200 (CEST) Message-ID: <3F218374.9000204@klaster.net> Date: Fri, 25 Jul 2003 21:22:28 +0200 From: Tomasz Myrta User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; en-US; rv:1.4) Gecko/20030624 X-Accept-Language: pl, en-us, en MIME-Version: 1.0 To: Elielson Fontanezi Cc: pgsql-sql Subject: Re: ERROR: DefineIndex: index function must be marked iscachable References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-2; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/383 X-Sequence-Number: 14497 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 From sszabo@megazone.bigpanda.com Fri Jul 25 19:24:57 2003 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 6C29AD1C510; Fri, 25 Jul 2003 19:24:57 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 61927-09; Fri, 25 Jul 2003 16:24:11 -0300 (ADT) Received: from megazone.bigpanda.com (unknown [66.180.233.26]) by svr1.postgresql.org (Postfix) with ESMTP id 62BF9D1C50D; Fri, 25 Jul 2003 16:24:56 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 4F216D62D; Fri, 25 Jul 2003 12:24:56 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 3CC015C0E; Fri, 25 Jul 2003 12:24:56 -0700 (PDT) Date: Fri, 25 Jul 2003 12:24:56 -0700 (PDT) From: Stephan Szabo To: Elielson Fontanezi Cc: pgsql-general , pgsql-sql Subject: Re: [SQL] ERROR: DefineIndex: index function must be In-Reply-To: <20030725121618.G37941-100000@megazone.bigpanda.com> Message-ID: <20030725122348.L37941-100000@megazone.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/1248 X-Sequence-Number: 46101 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) From ElielsonF@prodam.sp.gov.br Fri Jul 25 19:42:08 2003 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id C3D9ED1C4E1; Fri, 25 Jul 2003 19:42:08 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 68323-04; Fri, 25 Jul 2003 16:41:21 -0300 (ADT) Received: from sampa7.prodam.sp.gov.br (unknown [200.230.190.107]) by svr1.postgresql.org (Postfix) with ESMTP id E0A14D1C4DA; Fri, 25 Jul 2003 16:42:03 -0300 (ADT) Received: from cprodamibs249.PRODAM (cprodamibs249.prodam.sp.gov.br [10.10.64.249]) by sampa7.prodam.sp.gov.br with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id PTQ33BW2; Fri, 25 Jul 2003 16:42:02 -0300 Received: by cprodamibs249.prodam with Internet Mail Service (5.5.2653.19) id ; Fri, 25 Jul 2003 16:43:42 -0300 Message-ID: From: Elielson Fontanezi To: pgsql-general , pgsql-sql Subject: ERROR: DefineIndex: index function must be marked iscachable Date: Fri, 25 Jul 2003 16:43:34 -0300 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C352E4.A4DB9642" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/1251 X-Sequence-Number: 46104 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C352E4.A4DB9642 Content-Type: text/plain; charset="iso-8859-1" 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 ---------------------------( )----------( )-------------------------------- \ ( ) / \_/ \_/ ------_=_NextPart_001_01C352E4.A4DB9642 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi!

Who can help me on that?

First of all, my envoronment= =20 is:

Linux= =20 netlab142.prodam 2.4.8-26mdk #1 Sun Sep 23 17:06:39 CEST 2001 i686=20 unknown
pg_ctl (PostgreSQL)= =20 7.2.

= Problem: ERROR: DefineI= ndex:=20 index function must be marked iscachable by=20 executing:

create index bt_proposta_f01 on proposta
usin= g btree=20 (func_cod_secretaria(nr_proponente));

= Where nr_proponte is a intege= r type=20 column.

= The function=20 is:

CREATE OR REPLACE FUNCTION func_cod_secretaria(I= NTEGER)=20 RETURNS INTEGER AS '
   DECLARE
    &nb= sp;=20 v_nr_proponente ALIAS FOR $1;
  =20 BEGIN
      return=20 TRUNC(v_nr_proponente/100000,0)*100000
   END;
' LANGUAGE= =20 'plpgsql';

------_=_NextPart_001_01C352E4.A4DB9642-- From sszabo@megazone.bigpanda.com Fri Jul 25 19:47:24 2003 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id B7853D1C4B7; Fri, 25 Jul 2003 19:47:24 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 68816-04; Fri, 25 Jul 2003 16:46:37 -0300 (ADT) Received: from megazone.bigpanda.com (unknown [66.180.233.26]) by svr1.postgresql.org (Postfix) with ESMTP id 0F9D3D1C4C8; Fri, 25 Jul 2003 16:47:23 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 6BD0ED641; Fri, 25 Jul 2003 12:47:24 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 617885C0E; Fri, 25 Jul 2003 12:47:24 -0700 (PDT) Date: Fri, 25 Jul 2003 12:47:24 -0700 (PDT) From: Stephan Szabo To: Elielson Fontanezi Cc: pgsql-general , pgsql-sql Subject: Re: ERROR: DefineIndex: index function must be marked In-Reply-To: Message-ID: <20030725124326.K38567-100000@megazone.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/1252 X-Sequence-Number: 46105 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.