Thread: strange behaviour

strange behaviour

From
"Gaetano Mendola"
Date:
Hi to all,
I did a funciont for retrieve the actual time stamp
(not the time stamp of transaction):

CREATE FUNCTION sp_now (  )
RETURNS timestamp AS'
DECLARE
BEGIN
    RAISE NOTICE ''sp_now()'';
    RETURN timestamp(timeofday());
END;
' LANGUAGE 'plpgsql';


I notice that when I do some select like:

SELECT *
FROM user_traffic
WHERE start_date < sp_now();

the sp_now() is called for each row so I recreate
the function with the attribute iscachable:


CREATE FUNCTION sp_now (  )
RETURNS timestamp AS'
DECLARE

BEGIN
    RETURN timestamp(timeofday());
END;
' LANGUAGE 'plpgsql'
WITH (iscachable);

Now the select is working fine ( I mean only
one call for all records).

I create a function like this:

CREATE FUNCTION sp_foo (  )
RETURNS timestamp AS'
DECLARE
    my_time_stamp    timestamp;
BEGIN
    my_time_stamp := sp_now();

    RETURN my_time_stamp;
END;
' LANGUAGE 'plpgsql';


and with my surprise when I do a
series of this select :     select sp_now(), sp_foo();

I obtain:

empdb=# select sp_now(), sp_foo();
          sp_now           |          sp_foo
---------------------------+---------------------------
 2002-04-27 11:52:45.70+02 | 2002-04-27 11:52:45.70+02
(1 row)

empdb=# select sp_now(), sp_foo();
          sp_now           |          sp_foo
---------------------------+---------------------------
 2002-04-27 11:52:48.07+02 | 2002-04-27 11:52:45.70+02
(1 row)

empdb=# select sp_now(), sp_foo();
          sp_now           |          sp_foo
---------------------------+---------------------------
 2002-04-27 11:52:52.50+02 | 2002-04-27 11:52:45.70+02
(1 row)

empdb=# select sp_now(), sp_foo();
          sp_now           |          sp_foo
---------------------------+---------------------------
 2002-04-27 11:52:54.00+02 | 2002-04-27 11:52:45.70+02

It is normal that inside sp_foo() the sp_now() is not anymore called ?


Ciao
Gaetano

--
#exclude <windows>
#include <CSRSS>
printf("\t\t\b\b\b\b\b\b");.
printf("\t\t\b\b\b\b\b\b");


Re: strange behaviour

From
Tom Lane
Date:
"Gaetano Mendola" <mendola@bigfoot.com> writes:
> It is normal that inside sp_foo() the sp_now() is not anymore called ?

You *did* mark it cachable.  timeofday() is pretty much the classic
example of functions that shouldn't be marked cachable.

            regards, tom lane

Re: strange behaviour

From
"Gaetano Mendola"
Date:
"Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> "Gaetano Mendola" <mendola@bigfoot.com> writes:
> > It is normal that inside sp_foo() the sp_now() is not anymore called ?
>
> You *did* mark it cachable.  timeofday() is pretty much the classic
> example of functions that shouldn't be marked cachable.

Yes I know, but is not so clear why in a select like:

select sp_now(), sp_foo();

the first call to sp_now() is not cached and inside sp_foo() yes,
this behaviour was more clear for me if both sp_now() was
cached.


Ciao
Gaetano



Re: strange behaviour

From
"Gaetano Mendola"
Date:
"Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> "Gaetano Mendola" <mendola@bigfoot.com> writes:
> > It is normal that inside sp_foo() the sp_now() is not anymore called ?
>
> You *did* mark it cachable.  timeofday() is pretty much the classic
> example of functions that shouldn't be marked cachable.

Yes I know, but is not so clear why in a select like:

select sp_now(), sp_foo();

the first call to sp_now() is not cached and inside sp_foo() yes,
this behaviour was more clear for me if both sp_now() was
cached.


Ciao
Gaetano