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(at)megazone(dot)bigpanda(dot)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.