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

Re: transaction_timestamp()

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Samuel Stearns" <SStearns(at)internode(dot)com(dot)au>
Cc: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: transaction_timestamp()
Date: 2010-05-25 17:42:33
Message-ID: 4BFBC5B90200002500031A5E@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-admin
Samuel Stearns <SStearns(at)internode(dot)com(dot)au> wrote:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
 
>> transaction_timestamp() is exactly the same thing as now().
 
> I got into trouble using now() before.
 
Using what?  What kind of trouble?
 
> Transaction_timestamp() is really what I need, I think.
 
test=# \x
Expanded display is on.
test=# begin;
BEGIN
test=# select now(), current_timestamp, transaction_timestamp();
-[ RECORD 1 ]---------+------------------------------
now                   | 2010-05-25 12:41:34.365224-05
now                   | 2010-05-25 12:41:34.365224-05
transaction_timestamp | 2010-05-25 12:41:34.365224-05
 
[wait ten seconds or so...]
 
test=# select now(), current_timestamp, transaction_timestamp();
-[ RECORD 1 ]---------+------------------------------
now                   | 2010-05-25 12:41:34.365224-05
now                   | 2010-05-25 12:41:34.365224-05
transaction_timestamp | 2010-05-25 12:41:34.365224-05
 
test=# commit;
COMMIT
test=# select now(), current_timestamp, transaction_timestamp();
-[ RECORD 1 ]---------+------------------------------
now                   | 2010-05-25 12:41:50.765224-05
now                   | 2010-05-25 12:41:50.765224-05
transaction_timestamp | 2010-05-25 12:41:50.765224-05
 
> BEGIN;
> INSERT INTO blah_archive (id, user) SELECT id, user FROM blah
> where date < (now() - '30 days'::interval);
> DELETE FROM blah where date < (now() - '30 days'::interval);
> END;
> 
> The now() on the INSERT will be a different time than the now() on
> the DELETE
 
What makes you think that?
 
-Kevin

In response to

Responses

pgsql-admin by date

Next:From: Balkrishna SharmaDate: 2010-05-25 17:48:34
Subject: How to find if a SELECT is reading from buffer or disk ?
Previous:From: Ian LeaDate: 2010-05-25 12:44:13
Subject: Re: Quickest command to dump and restore the database? : pg_dump and pg_restore? using PostgreSQL 8.3 running on RedHat 5.3

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