From fhevia@ip-tel.com.ar Mon Sep 10 00:24:02 2007 Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 3C8BD9F978C for ; Sun, 9 Sep 2007 21:24:02 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 54085-08 for ; Sun, 9 Sep 2007 21:23:53 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from pyxis.powervt.com.ar (host3.powervt.com.ar [200.43.63.66]) by postgresql.org (Postfix) with ESMTP id 4EC669F9787 for ; Sun, 9 Sep 2007 21:23:52 -0300 (ADT) Received: from pcfhevia ([201.216.249.98]) by pyxis.powervt.com.ar (IPGroup Mail Server) with ESMTP id 1OT48953 for ; Sun, 09 Sep 2007 21:23:50 -0300 From: "Fernando Hevia" To: "'SQL Postgresql List'" Subject: Function Volatility Date: Sun, 9 Sep 2007 21:24:30 -0300 Organization: IP-TEL Message-ID: <017901c7f340$f497e400$8f01010a@iptel.com.ar> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_017A_01C7F327.CF4AAC00" X-Mailer: Microsoft Office Outlook 11 Thread-Index: AcfzQPRctMPcoZ+RSmmSiPXZX+9XBg== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/69 X-Sequence-Number: 29282 This is a multi-part message in MIME format. ------=_NextPart_000_017A_01C7F327.CF4AAC00 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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. ------=_NextPart_000_017A_01C7F327.CF4AAC00 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Function Volatility

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.

------=_NextPart_000_017A_01C7F327.CF4AAC00-- From tgl@sss.pgh.pa.us Mon Sep 10 02:15:36 2007 Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 3936D9F9AC3 for ; Sun, 9 Sep 2007 23:15:36 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 64762-10 for ; Sun, 9 Sep 2007 23:15:32 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 1FF4C9F9A68 for ; Sun, 9 Sep 2007 23:15:33 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l8A2FOMq015491; Sun, 9 Sep 2007 22:15:24 -0400 (EDT) To: "Fernando Hevia" cc: "'SQL Postgresql List'" Subject: Re: Function Volatility In-reply-to: <017901c7f340$f497e400$8f01010a@iptel.com.ar> References: <017901c7f340$f497e400$8f01010a@iptel.com.ar> Comments: In-reply-to "Fernando Hevia" message dated "Sun, 09 Sep 2007 21:24:30 -0300" Date: Sun, 09 Sep 2007 22:15:24 -0400 Message-ID: <15490.1189390524@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/74 X-Sequence-Number: 29287 "Fernando Hevia" 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 From fhevia@ip-tel.com.ar Mon Sep 10 13:47:21 2007 Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 398F29F9C09 for ; Mon, 10 Sep 2007 10:47:21 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 67376-01 for ; Mon, 10 Sep 2007 10:47:17 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from pyxis.powervt.com.ar (host3.powervt.com.ar [200.43.63.66]) by postgresql.org (Postfix) with ESMTP id C787A9F9C46 for ; Mon, 10 Sep 2007 10:47:00 -0300 (ADT) Received: from pcfhevia ([201.216.249.98]) by pyxis.powervt.com.ar (IPGroup Mail Server) with ESMTP id 1OT48953; Mon, 10 Sep 2007 10:46:58 -0300 From: "Fernando Hevia" To: "'Tom Lane'" Cc: "'SQL Postgresql List'" Subject: Re: Function Volatility Date: Mon, 10 Sep 2007 10:47:34 -0300 Organization: IP-TEL Message-ID: <019801c7f3b1$240cfb60$8f01010a@iptel.com.ar> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 11 Thread-Index: AcfzUIeGxqx+xIqWTwCwGgLXvUWxdAAA1zvQ In-Reply-To: <15490.1189390524@sss.pgh.pa.us> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/76 X-Sequence-Number: 29289 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.