Forum OpenACS Development: Re: Audit of postgres plpgsql procs...

Collapse
Posted by Don Baccus on
In 7.2 iscachable was only used to determine if a function could be pre-executed in the context of a single SQL statement.
Iscachable indicates that the function always returns the same result when given the same argument values (i.e., it does not do database lookups or otherwise use information not directly present in its parameter list). The optimizer uses iscachable to know whether it is safe to pre-evaluate a call of the function.
So in practice it was the same as stable, because the optimizer only examines one SQL statement at a time and the function was always executed and then cached by the executor for each SQL statement. They added immutable and cross-statement function result caching for 7.3.

I'm not familiar with the actual implementation in 7.3 but it's clear from the documentation that relying on the (documented, I might add) implementation of "iscachable" in 7.2 might result in errors in PG 7.3. We'd better look into fixing these ... I wish they hadn't done that, we probably aren't the only people with potential bugs here.

Collapse
Posted by Jeff Davis on
I think these should be changed to stable:
 trigger_type
 acs_objects_get_tree_sortkey
 forums_message__root_message_id
 file_storage__get_root_folder
 file_storage__get_package_id
I don't see any others that should be a problem.

Strictly speaking acs__magic_object_id maybe should be but I think it's safe to leave as immutable...

Collapse
Posted by Jeff Davis on
I committed the changes for the core plpgsql procs and provided an upgrade script for the procs which changed internally (bug fixes), but no upgrade script to change the strict/volatile flags. I will try to get one together in the next day or two.