Re: now() in PL/pgSQL Functions

["Yosef Haas" <yosef(at)karatedepot(dot)com>]

Ok. Right after I posted I noticed this:

 

“It is important to know that CURRENT_TIMESTAMP and related functions return the start time of the current transaction; their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the "current" time, so that multiple modifications within the same transaction bear the same time stamp.”

 

Using timeofday(), gives me the result I want (timeofday()::timestamp casts it nicely to a timestamp).

 

 

Yosef Haas

Lead Developer

KarateDepot.com

845-875-6423

yosef(at)karatedepot(dot)com

---

From: pgsql-bugs-owner(at)postgresql(dot)org [mailto:pgsql-bugs-owner(at)postgresql(dot)org] On Behalf Of Yosef Haas
Sent: Tuesday, January 30, 2007 9:44 AM
To: pgsql-bugs(at)postgresql(dot)org
Subject: [BUGS] now() in PL/pgSQL Functions

 

This is in version 8.1.4.

 

I’ve noticed what seems to be a strange behavior – it may be by design, but I figured I’d ask.

 

Run this simple test function:

 

create or replace function test() RETURNS bool AS '

begin

            raise notice ''%'',now();

            for i IN 0..50000000  loop

            end loop;

            raise notice ''%'',now();

            return true;

end;

'

LANGUAGE 'plpgsql';

 

It should print the current date, wait a few seconds (by counting to 50 million)

And then print the current date. Clearly, the two dates are not identical; however this is how it executes:

 

catalog=# select test();

NOTICE:  2007-01-30 09:33:19.323702-05

NOTICE:  2007-01-30 09:33:19.323702-05

 test

------

 t

(1 row)

 

For some reason it is using the same value for both “now()” calls. Is this a bug, or by design? If it’s by design what can I do to get the right time. I know that the function only returns when it’s finished executing, but shouldn’t now() return the actual time and not the time that the function begins?

 

Thanks,

 

Yosef Haas

yosef(at)karatedepot(dot)com