From: | Samuel Stearns <SStearns(at)internode(dot)com(dot)au> |
---|---|
To: | Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> |
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 23:09:41 |
Message-ID: | 68B59BEDCD36854AADBDF17E91B2937A01A57CCDFF@EXCHMAIL.staff.internode.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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 outside the transaction block back then possibly.
Thanks for setting me straight. Sorry about the waste of time.
Sam
-----Original Message-----
From: Kevin Grittner [mailto:Kevin(dot)Grittner(at)wicourts(dot)gov]
Sent: Wednesday, 26 May 2010 3:13 AM
To: Samuel Stearns
Cc: pgsql-admin(at)postgresql(dot)org; Tom Lane
Subject: Re: [ADMIN] transaction_timestamp()
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mikko Partio | 2010-05-26 06:01:41 | Re: could not truncate directory "pg_subtrans": apparent wraparound |
Previous Message | Samuel Stearns | 2010-05-25 22:45:05 | Re: Quickest command to dump and restore the database? : pg_dump and pg_restore? using PostgreSQL 8.3 running on RedHat 5.3 |