Re: [PERFORM] cacheable stored functions?
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. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] cacheable stored functions?
Dnia 2004-02-20 16:35, Uytkownik 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] cacheable stored functions?
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] cacheable stored functions?
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
