pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Are cached IMMUTABLE values persistent between sessions
5+ messages / 4 participants
[nested] [flat]

* Are cached IMMUTABLE values persistent between sessions
@ 2005-06-23 13:26  Donald Fraser <postgres@kiwi-fraser.net>
  0 siblings, 3 replies; 5+ messages in thread

From: Donald Fraser @ 2005-06-23 13:26 UTC (permalink / raw)
  To: pgsql-admin

Is the returned value of a function defined as IMMUTABLE cached globally? In other words could postgresql potentially return a cached value obtained from one client session to a different client session?

Thanks in advance
Donald Fraser 

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Are cached IMMUTABLE values persistent between sessions
@ 2005-06-23 14:33  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Donald Fraser <postgres@kiwi-fraser.net>
  2 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2005-06-23 14:33 UTC (permalink / raw)
  To: Donald Fraser <postgres@kiwi-fraser.net>; +Cc: pgsql-admin

"Donald Fraser" <postgres@kiwi-fraser.net> writes:
> Is the returned value of a function defined as IMMUTABLE cached =
> globally?

No, in fact it isn't cached at all.  IMMUTABLE tells the planner that
it's OK to fold a function call with constant inputs to a constant
result value at plan time.  Nothing more.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Are cached IMMUTABLE values persistent between sessions
@ 2005-06-23 14:40  Alvaro Herrera <alvherre@surnet.cl>
  parent: Donald Fraser <postgres@kiwi-fraser.net>
  2 siblings, 0 replies; 5+ messages in thread

From: Alvaro Herrera @ 2005-06-23 14:40 UTC (permalink / raw)
  To: Donald Fraser <postgres@kiwi-fraser.net>; +Cc: pgsql-admin

On Thu, Jun 23, 2005 at 02:26:23PM +0100, Donald Fraser wrote:
> Is the returned value of a function defined as IMMUTABLE cached
> globally? In other words could postgresql potentially return a cached
> value obtained from one client session to a different client session?

Return values from functions are never cached at all, not even for
immutable functions.  The mutability flag (currently) is only used for
deciding the "optimizability" of the function within the query's
execution.

-- 
Alvaro Herrera (<alvherre[a]surnet.cl>)
"El que vive para el futuro es un iluso, y el que vive para el pasado,
un imbécil" (Luis Adler, "Los tripulantes de la noche")



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Are cached IMMUTABLE values persistent between sessions
@ 2005-06-23 15:00  Michael Fuhr <mike@fuhr.org>
  parent: Donald Fraser <postgres@kiwi-fraser.net>
  2 siblings, 0 replies; 5+ messages in thread

From: Michael Fuhr @ 2005-06-23 15:00 UTC (permalink / raw)
  To: Donald Fraser <postgres@kiwi-fraser.net>; +Cc: pgsql-admin

On Thu, Jun 23, 2005 at 02:26:23PM +0100, Donald Fraser wrote:
>
> Is the returned value of a function defined as IMMUTABLE cached
> globally?  In other words could postgresql potentially return a
> cached value obtained from one client session to a different client
> session?

You could experiment with this by adding a debugging RAISE statement
to an IMMUTABLE PL/pgSQL function to see when it's called.  I suspect
there's no global caching; if there is then I haven't been able to
make it happen in tests.  There doesn't even appear to be any caching
between disparate queries in the same session unless a prepared
plan is involved (e.g., a plan made with PREPARE or a cached plan
from a PL/pgSQL function) and the result of the IMMUTABLE function
could be determined at planning time.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Are cached IMMUTABLE values persistent between sessions
@ 2005-06-24 09:53  Donald Fraser <postgres@kiwi-fraser.net>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Donald Fraser @ 2005-06-24 09:53 UTC (permalink / raw)
  To: pgsql-admin

> "Donald Fraser" writes:
>> Is the returned value of a function defined as IMMUTABLE cached =
>> globally?

"Tom Lane" writes:
> No, in fact it isn't cached at all.  IMMUTABLE tells the planner that
> it's OK to fold a function call with constant inputs to a constant
> result value at plan time.  Nothing more.

Ok I'm sort of clutching at straws here...
What about this scenario: I'm aware that sql statements within PL/pgSQL 
functions get cached. Therefore if the query planner can fold a function with a 
constant result, could that query plan get cached by PL/pgSQL with a folded 
value?

Regards
Donald Fraser 




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2005-06-24 09:53 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-23 13:26 Are cached IMMUTABLE values persistent between sessions Donald Fraser <postgres@kiwi-fraser.net>
2005-06-23 14:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-24 09:53   ` Donald Fraser <postgres@kiwi-fraser.net>
2005-06-23 14:40 ` Alvaro Herrera <alvherre@surnet.cl>
2005-06-23 15:00 ` Michael Fuhr <mike@fuhr.org>

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