Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 75E78636A94 for ; Tue, 25 May 2010 20:09:54 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 57334-07 for ; Tue, 25 May 2010 23:09:46 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from imail.internode.com.au (exchht01-ext.internode.com.au [203.26.95.185]) by mail.postgresql.org (Postfix) with ESMTP id 89C026366F8 for ; Tue, 25 May 2010 20:09:46 -0300 (ADT) Received: from EXCHMAIL.staff.internode.com.au ([169.254.1.229]) by exchht01.staff.internode.com.au ([fe80::193f:3ff6:d38f:74dc%12]) with mapi; Wed, 26 May 2010 08:39:42 +0930 From: Samuel Stearns To: Kevin Grittner CC: "pgsql-admin@postgresql.org" , Tom Lane Date: Wed, 26 May 2010 08:39:41 +0930 Subject: Re: transaction_timestamp() Thread-Topic: [ADMIN] transaction_timestamp() Thread-Index: Acr8Ma51REScS82NSC2nGexekD3p+QALUb2w Message-ID: <68B59BEDCD36854AADBDF17E91B2937A01A57CCDFF@EXCHMAIL.staff.internode.com.au> References: <68B59BEDCD36854AADBDF17E91B2937A01A57CCCB9@EXCHMAIL.staff.internode.com.au> <17418.1274761902@sss.pgh.pa.us> <68B59BEDCD36854AADBDF17E91B2937A01A57CCD01@EXCHMAIL.staff.internode.com.au> <4BFBC5B90200002500031A5E@gw.wicourts.gov> In-Reply-To: <4BFBC5B90200002500031A5E@gw.wicourts.gov> Accept-Language: en-US, en-AU Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: en-US, en-AU Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201005/246 X-Sequence-Number: 34719 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 th= en possibly. Thanks for setting me straight. Sorry about the waste of time. Sam -----Original Message----- From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]=20 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 wrote: > Tom Lane wrote: =20 >> transaction_timestamp() is exactly the same thing as now(). =20 > I got into trouble using now() before. =20 Using what? What kind of trouble? =20 > Transaction_timestamp() is really what I need, I think. =20 test=3D# \x Expanded display is on. test=3D# begin; BEGIN test=3D# 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 =20 [wait ten seconds or so...] =20 test=3D# 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 =20 test=3D# commit; COMMIT test=3D# 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 =20 > 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; >=20 > The now() on the INSERT will be a different time than the now() on > the DELETE =20 What makes you think that? =20 -Kevin