Thread: How do you control IMMUTABLE PG PROC results?

We have procs that would benefit from returning IMMUTABLE results. The procs
are dependent on external tables that rarely change, but when they DO
change, it would be great if we could expire the cache that the procs read
from so that the procs are forced to re-evaluate the results.

Is this possible?

How do I know how many possible results are cached?

On Friday 5. November 2010 01.24.14 Carlo Stonebanks wrote:
> We have procs that would benefit from returning IMMUTABLE results. The procs
> are dependent on external tables that rarely change, but when they DO
> change, it would be great if we could expire the cache that the procs read
> from so that the procs are forced to re-evaluate the results.

A function declared as IMMUTABLE can't, by definition, do database lookups.
Then it has to be declared as STABLE.

<http://www.postgresql.org/docs/8.4/static/xfunc-volatility.html>

«An IMMUTABLE function cannot modify the database and is guaranteed to return
the same results given the same arguments forever.»

regards,
Leif B. Kristensen

On Thu, Nov 04, 2010 at 08:24:14PM -0400, Carlo Stonebanks wrote:
> We have procs that would benefit from returning IMMUTABLE results.
> The procs are dependent on external tables that rarely change, but
> when they DO change, it would be great if we could expire the cache
> that the procs read from so that the procs are forced to re-evaluate
> the results.
>
> Is this possible?
>
> How do I know how many possible results are cached?

in addition to what Leif responded, please note that immutable functions
*do not* cache results.

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007