Re: transaction_timestamp()

Hi Tom and Kevin,

I stand corrected.  It was awhile ago that I last played around with this.  I must have had the DELETE operation
outsidethe transaction block back then possibly. 

Thanks for setting me straight.  Sorry about the waste of time.

Sam

-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: Wednesday, 26 May 2010 3:13 AM
To: Samuel Stearns
Cc: pgsql-admin@postgresql.org; Tom Lane
Subject: Re: [ADMIN] transaction_timestamp()

Samuel Stearns <SStearns@internode.com.au> wrote:
> Tom Lane <tgl@sss.pgh.pa.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