RES: ERROR: DefineIndex: index function must be marked iscachable

Thanks a lot!

The complete solution is here!

1st. The function wich substitute the trunc() function

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' WITH isCachable;

2nd. The index on that function

CREATE INDEX bt_proposta_f01
ON proposta USING BTREE (func_cod_secretaria(nr_proponente));

3rd. The anlysis of both queries: the old and new one.

    a) the old query:

---------------------------------------------------------------
DEBUG:  query: select
   pa.nr_projeto,
   pa.dc_denom_projeto,
   pa.nr_proponente,
   pa.dc_coordenador,
   op.dc_proponente
   from proposta pa
   inner join orgao_proponente op
   on (trunc(pa.nr_proponente/100000,0)*100000 = op.nr_proponente)
   where pa.in_situacao_proposta <> 'E' ORDER BY 1 DESC;

DEBUG:  QUERY STATISTICS
! system usage stats:
!       104.665005 elapsed 10.090000 user 0.420000 system sec
!       [10.100000 user 0.420000 sys total]
!       0/0 [0/0] filesystem blocks in/out
!       141/50 [352/180] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       0/0 [0/0] voluntary/involuntary context switches
! postgres usage stats:
!       Shared blocks:       7408 read,          0 written, buffer hit rate
= 13.23
%
!       Local  blocks:          0 read,          0 written, buffer hit rate
= 0.00%
!       Direct blocks:          0 read,          0 written
---------------------------------------------------------------


    b) the new query


---------------------------------------------------------------
DEBUG:  query: select
   pa.nr_projeto,
   pa.dc_denom_projeto,
   pa.nr_proponente,
   pa.dc_coordenador,
   op.dc_proponente
   from proposta pa
   inner join orgao_proponente op
   on (op.nr_proponente = func_cod_secretaria(pa.nr_proponente))
   where pa.in_situacao_proposta <> 'E';
DEBUG:  query: SELECT  TRUNC( $1 /100000,0)*100000
DEBUG:  QUERY STATISTICS
! system usage stats:
!       0.130885 elapsed 0.020000 user 0.010000 system sec
!       [0.020000 user 0.020000 sys total]
!       0/0 [0/0] filesystem blocks in/out
!       168/68 [369/172] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       0/0 [0/0] voluntary/involuntary context switches
! postgres usage stats:
!       Shared blocks:        142 read,          1 written, buffer hit rate
= 88.10
%
!       Local  blocks:          0 read,          0 written, buffer hit rate
= 0.00%
!       Direct blocks:          0 read,          0 written
---------------------------------------------------------------

-----Mensagem original-----
De: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
Enviada em: sexta-feira, 25 de julho de 2003 16:47
Para: Elielson Fontanezi
Cc: pgsql-general; pgsql-sql
Assunto: Re: [GENERAL] ERROR: DefineIndex: index function must be marked
iscachable



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.