Re: now() in PL/pgSQL Functions

From: "Yosef Haas" <yosef(at)karatedepot(dot)com>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: now() in PL/pgSQL Functions
Date: 2007-01-30 15:13:56
Message-ID: 00a801c74481$42bfbb40$0100a8c0@cqg2y51
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2007-01-30 15:20:22 Re: BUG #2945: possibly forgotten SPI_push()/SPI_pop()
Previous Message Adriaan van Os 2007-01-30 15:10:07 Re: [HACKERS] [BUGS] BUG #2907: pg_get_serial_sequence quoting