pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
cacheable 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