pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedcacheable stored functions?
5+ messages / 4 participants
[nested] [flat]
* cacheable stored functions?
@ 2004-02-20 15:35 Bill Moran <wmoran@potentialtech.com>
2004-02-20 15:47 ` Re: cacheable stored functions? Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-20 15:59 ` Re: cacheable stored functions? Tomasz Myrta <jasiek@klaster.net>
2004-02-20 16:01 ` Re: cacheable stored functions? Richard Huxton <dev@archonet.com>
0 siblings, 3 replies; 5+ messages in thread
From: Bill Moran @ 2004-02-20 15:35 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: cacheable stored functions?
2004-02-20 15:35 cacheable stored functions? Bill Moran <wmoran@potentialtech.com>
@ 2004-02-20 15:47 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2004-02-20 15:47 UTC (permalink / raw)
To: Bill Moran <wmoran@potentialtech.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: cacheable stored functions?
2004-02-20 15:35 cacheable stored functions? Bill Moran <wmoran@potentialtech.com>
@ 2004-02-20 15:59 ` Tomasz Myrta <jasiek@klaster.net>
2 siblings, 0 replies; 5+ messages in thread
From: Tomasz Myrta @ 2004-02-20 15:59 UTC (permalink / raw)
To: Bill Moran <wmoran@potentialtech.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: cacheable stored functions?
2004-02-20 15:35 cacheable stored functions? Bill Moran <wmoran@potentialtech.com>
@ 2004-02-20 16:01 ` Richard Huxton <dev@archonet.com>
2004-02-20 16:48 ` Re: cacheable stored functions? Bill Moran <wmoran@potentialtech.com>
2 siblings, 1 reply; 5+ messages in thread
From: Richard Huxton @ 2004-02-20 16:01 UTC (permalink / raw)
To: Bill Moran <wmoran@potentialtech.com>; pgsql-performance
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: cacheable stored functions?
2004-02-20 15:35 cacheable stored functions? Bill Moran <wmoran@potentialtech.com>
2004-02-20 16:01 ` Re: cacheable stored functions? Richard Huxton <dev@archonet.com>
@ 2004-02-20 16:48 ` Bill Moran <wmoran@potentialtech.com>
0 siblings, 0 replies; 5+ messages in thread
From: Bill Moran @ 2004-02-20 16:48 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-02-20 16:48 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-20 15:35 cacheable stored functions? Bill Moran <wmoran@potentialtech.com>
2004-02-20 15:47 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-20 15:59 ` Tomasz Myrta <jasiek@klaster.net>
2004-02-20 16:01 ` Richard Huxton <dev@archonet.com>
2004-02-20 16:48 ` Bill Moran <wmoran@potentialtech.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox