Skip site navigation (1) Skip section navigation (2)

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 (view raw or whole 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


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



Yosef Haas

Lead Developer




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 '


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

            for i IN 0..50000000  loop

            end loop;

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

            return true;



LANGUAGE 'plpgsql';


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

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




(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




Yosef Haas



In response to

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group