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 flat)
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

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-2014 The PostgreSQL Global Development Group