Forum OpenACS Q&A: possible problem with iscachable functions on 7.3

I was reading the postgres docs and it says:
isCachable is an obsolete equivalent of IMMUTABLE; it's still accepted for backwards-compatibility reasons.
However, it describes the attributes as:
IMMUTABLE indicates that the function always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its parameter list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value.

STABLE indicates that within a single table scan the function will consistently return the same result for the same argument values, but that its result could change across SQL statements. This is the appropriate selection for functions whose results depend on database lookups, parameter variables (such as the current time zone), etc. Also note that the CURRENT_TIMESTAMP family of functions qualify as stable, since their values do not change within a transaction.

VOLATILE indicates that the function value can change even within a single table scan, so no optimizations can be made. Relatively few database functions are volatile in this sense; some examples are random(), currval(), timeofday(). Note that any function that has side-effects must be classified volatile, even if its result is quite predictable, to prevent calls from being optimized away; an example is setval().

and it sounds like we should really be using stable for most things. It's not really clear to me if we will get in trouble on 7.3 with iscachable or not but it's something to look out for.
Collapse
Posted by Don Baccus on
I just inspected the file acs-kernel/sql/postgresql/postgres.sql and it looks like the "iscachable" functions defined there will work fine with the new definition.  If you think I've missed some that won't, post here and I'll take another look.

The function in file-storage which gets the root folder for a given package will FAIL under the new definition.  It should be defined "stable".  In practice since it is rarely, if ever, called more than once per query we should probably not cache it in PG at all.  If caching it makes sense - and it's a cheap function so it may not really matter - let's do it at the Tcl level.

Similar things can be said about the proc that returns the package_id associated with a particular root folder.

Hmmm...simplest would be to just remove the "iscachable" altogether from HEAD and 4.6.1 (if that will include the PG 7.3 patches) ... Jeff, what do you think?

These are the only uses I found in the openacs-4 tree.  All other "iscachable" references are to the similar concept that's implemented by the Service Contract package.