Re: can't figure string compare result (using also custom C function)

Sam Mason ha scritto:
> On Fri, Feb 13, 2009 at 10:31:49PM +0100, Edoardo Panfili wrote:
>> SELECT idSpecie,nome  FROM specienomi
>> WHERE idspecie=37026 and nome='X Agropogon littoralis (Sm.) C.E. Hubb.';
>>  idspecie |                  nome
>> ----------+-----------------------------------------
>>     37026 | X Agropogon littoralis (Sm.) C.E. Hubb.
>>
>> The same query but without one condition: no results.
>>
>> SELECT idSpecie,nome  FROM specienomi
>> WHERE nome='X Agropogon littoralis (Sm.) C.E. Hubb.';
>>  idspecie | nome
>> ----------+------
>> (0 rows)
>>
>> I can't figure why, can someone tell me how investigate?
>
> I've just looked back in the archives and noticed that you were asking
> about functional indexes; you do know that if you change the definition
> of a function that PG doesn't know to rebuild the index don't you?
>
> That would exhibit the symptoms you're seeing; i.e. the first case is
> using an index on "idspecie" and the second is using the (out-of-date)
> functional index.
Tank you!

After you answer it is obvious (I have an IMMUTABLE at the and of function).

I was working with the same function using plpgsql but it is 2.5 times
slower, and plperl is slower.

REINDER TABLE specie;

and all works fine.

Is it reported on the documentation? (this is not a critic regarding
postgres very well done documentation!) I must read it with more attention.

Tank you again
Edoardo