pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunction Volatility
3+ messages / 2 participants
[nested] [flat]
* Function Volatility
@ 2007-09-10 00:24 Fernando Hevia <fhevia@ip-tel.com.ar>
2007-09-10 02:15 ` Re: Function Volatility Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Fernando Hevia @ 2007-09-10 00:24 UTC (permalink / raw)
To: pgsql-sql
Hi guys,
I am not sure if I am understanding volatility.
My issue is better explained with a quick example. The function below
expresses call durations in minutes and it is immutable.
CREATE OR REPLACE FUNCTION dur2min(secs INTEGER) RETURNS INTEGER
AS $$
BEGIN
RAISE NOTICE 'BEEN HERE!';
RETURN CEIL(secs/60.0);
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
# SELECT dur2min(30) as c1, dur2min(30) as c2, dur2min(30) as c3;
NOTICE: BEEN HERE!
NOTICE: BEEN HERE!
NOTICE: BEEN HERE!
c1 | c2 | c3
----+----+----
1 | 1 | 1
(1 row)
What bother me are the 3 "been here" messages. As the function is immutable
and the parameter remains unchanged needs the planner actually execute the
function 3 times?
I was under the impression that under these conditions it could *reuse* the
result of the first call. The manual states the planner should avoid
reevaluate the function but I'm not sure what that means as it *is*
executing it every time.
My goal of course is that the function gets executed only once per row.
I'm using 8.2.4
Thanks for your hindsight.
Regards,
Fernando.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Function Volatility
2007-09-10 00:24 Function Volatility Fernando Hevia <fhevia@ip-tel.com.ar>
@ 2007-09-10 02:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-09-10 13:47 ` Re: Function Volatility Fernando Hevia <fhevia@ip-tel.com.ar>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2007-09-10 02:15 UTC (permalink / raw)
To: Fernando Hevia <fhevia@ip-tel.com.ar>; +Cc: pgsql-sql
"Fernando Hevia" <fhevia@ip-tel.com.ar> writes:
> I am not sure if I am understanding volatility.
You're not.
> What bother me are the 3 "been here" messages. As the function is immutable
> and the parameter remains unchanged needs the planner actually execute the
> function 3 times?
The IMMUTABLE marker is a promise from you to the system that it is safe
to optimize away multiple calls to the function. It is not a promise
from the system to you that the system will expend unlimited amounts of
energy to detect duplicate calls. The majority of the immutable
functions in Postgres are things like int4pl(), where it would obviously
be silly to expend any cycles at all on looking for duplicate calls such
as you show here.
In practice what will happen is that each textual call will be folded to
a constant separately. The advantage comes from not having to repeat
the call for each row processed by a query, not from saving work within
a row.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Function Volatility
2007-09-10 00:24 Function Volatility Fernando Hevia <fhevia@ip-tel.com.ar>
2007-09-10 02:15 ` Re: Function Volatility Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-09-10 13:47 ` Fernando Hevia <fhevia@ip-tel.com.ar>
0 siblings, 0 replies; 3+ messages in thread
From: Fernando Hevia @ 2007-09-10 13:47 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Tom Lane writes:
> The IMMUTABLE marker is a promise from you to the system that it is safe
> to optimize away multiple calls to the function. It is not a promise
> from the system to you that the system will expend unlimited amounts of
> energy to detect duplicate calls.
Nicely put. Thanks!
BTW, this explanation should go into the manual.
Regards,
Fernando.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-09-10 13:47 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-09-10 00:24 Function Volatility Fernando Hevia <fhevia@ip-tel.com.ar>
2007-09-10 02:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-09-10 13:47 ` Fernando Hevia <fhevia@ip-tel.com.ar>
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