Thread: cacheable stored functions?
I'm converting a SQL application to PostgreSQL. The majority of the logic
in this application is in the stored functions in the database.
Somewhere, I saw a reference to "WITH (iscachable)" for stored functions,
looking again, I'm unable to find any reference to this directive. I have
a single function that is _obviously_ safe to cache using this, and it
generates no errors or problems that I can see.
Now I'm looking at a lot of other functions that, if cached, would speed
up performance considerably. Yet I'm reluctant to use this directive
since I can't find documentation on it anywhere.
Can anyone say whether this is a supported feature in plpgsql, and is
safe to use? Is it simply undocumented, or am I just looking in the
wrong place?
(to reduce ambiguity, the manner in which I'm using this is:
CREATE FUNCTION getconstant(VARCHAR)
RETURNS int
AS '
DECLARE
BEGIN
IF $1 = ''phrase'' THEN
RETURN 1;
END IF;
...
END;
' LANGUAGE 'plpgsql' WITH (iscacheable);
--
Bill Moran
Potential Technologies
http://www.potentialtech.com
On Fri, 20 Feb 2004, Bill Moran wrote: > I'm converting a SQL application to PostgreSQL. The majority of the logic > in this application is in the stored functions in the database. > > Somewhere, I saw a reference to "WITH (iscachable)" for stored functions, > looking again, I'm unable to find any reference to this directive. I have > a single function that is _obviously_ safe to cache using this, and it > generates no errors or problems that I can see. It's been basically superceded by IMMUTABLE, and I believe they're described in the create function reference page. Note that it doesn't involve caching as much as the fact that it can be evaluated once and treated as a constant.
Dnia 2004-02-20 16:35, Użytkownik Bill Moran napisał: > Can anyone say whether this is a supported feature in plpgsql, and is > safe to use? Is it simply undocumented, or am I just looking in the > wrong place? "iscachable" is only for backward compatibility - it's changed now to "IMMUTABLE" You can read more about immutable, stable and volatile functions in Postgresql documentation - chapter SQL Commands/CREATE FUNCTION. Regards, Tomasz Myrta
On Friday 20 February 2004 15:35, Bill Moran wrote: > I'm converting a SQL application to PostgreSQL. The majority of the logic > in this application is in the stored functions in the database. > > Somewhere, I saw a reference to "WITH (iscachable)" for stored functions, > looking again, I'm unable to find any reference to this directive. I have > a single function that is _obviously_ safe to cache using this, and it > generates no errors or problems that I can see. > > Now I'm looking at a lot of other functions that, if cached, would speed > up performance considerably. Yet I'm reluctant to use this directive > since I can't find documentation on it anywhere. From memory, "iscachable" was replaced in version 7.3 by the three finer-grained settings IMMUTABLE, STABLE, VOLATILE. I'm guessing the old behaviour is still there for backwards compatibility, but it's probably best to use the new versions. -- Richard Huxton Archonet Ltd
Richard Huxton wrote: > On Friday 20 February 2004 15:35, Bill Moran wrote: > >>I'm converting a SQL application to PostgreSQL. The majority of the logic >>in this application is in the stored functions in the database. >> >>Somewhere, I saw a reference to "WITH (iscachable)" for stored functions, >>looking again, I'm unable to find any reference to this directive. I have >>a single function that is _obviously_ safe to cache using this, and it >>generates no errors or problems that I can see. >> >>Now I'm looking at a lot of other functions that, if cached, would speed >>up performance considerably. Yet I'm reluctant to use this directive >>since I can't find documentation on it anywhere. > >From memory, "iscachable" was replaced in version 7.3 by the three > finer-grained settings IMMUTABLE, STABLE, VOLATILE. > > I'm guessing the old behaviour is still there for backwards compatibility, but > it's probably best to use the new versions. Thanks to everyone who replied (with more or less the same answer ;) This has explained away my confusion, and I now have a reference to read. -- Bill Moran Potential Technologies http://www.potentialtech.com