pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedtransaction_timestamp()
6+ messages / 3 participants
[nested] [flat]
* transaction_timestamp()
@ 2010-05-25 04:25 Samuel Stearns <SStearns@internode.com.au>
2010-05-25 04:31 ` Re: transaction_timestamp() Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Samuel Stearns @ 2010-05-25 04:25 UTC (permalink / raw)
To: pgsql-admin
Howdy,
I have an archiving script running in an 8.3 environment using transaction_timestamp() for the INSERT and DELETE operations. I want to run the same thing in an 8.1 environment but transaction_timestamp() does not exist in 8.1. now() will not work because of the time that elapses between the INSERT and DELETE operations (I will end up losing some data). Does anyone have any suggestions, please?
Thanks,
Sam
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: transaction_timestamp()
2010-05-25 04:25 transaction_timestamp() Samuel Stearns <SStearns@internode.com.au>
@ 2010-05-25 04:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-05-25 05:15 ` Re: transaction_timestamp() Samuel Stearns <SStearns@internode.com.au>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2010-05-25 04:31 UTC (permalink / raw)
To: Samuel Stearns <SStearns@internode.com.au>; +Cc: pgsql-admin
Samuel Stearns <SStearns@internode.com.au> writes:
> I have an archiving script running in an 8.3 environment using transaction_timestamp() for the INSERT and DELETE operations. I want to run the same thing in an 8.1 environment but transaction_timestamp() does not exist in 8.1. now() will not work because of the time that elapses between the INSERT and DELETE operations (I will end up losing some data). Does anyone have any suggestions, please?
Um ... transaction_timestamp() is exactly the same thing as now().
Perhaps you need to go re-read
http://www.postgresql.org/docs/8.3/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: transaction_timestamp()
2010-05-25 04:25 transaction_timestamp() Samuel Stearns <SStearns@internode.com.au>
2010-05-25 04:31 ` Re: transaction_timestamp() Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-05-25 05:15 ` Samuel Stearns <SStearns@internode.com.au>
2010-05-25 17:42 ` Re: transaction_timestamp() Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 6+ messages in thread
From: Samuel Stearns @ 2010-05-25 05:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Thanks, Tom.
I read it again. I got into trouble using now() before. Transaction_timestamp() is really what I need, I think.
I have a table that is updated multiple times/second.
My archiving command operation:
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 resulting in more rows deleted than were inserted.
Whereas transaction_timestamp() takes care of the problem because both INSERT and DELETE operations work off of the one timestamp that the transaction started.
Sam
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, 25 May 2010 2:02 PM
To: Samuel Stearns
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] transaction_timestamp()
Samuel Stearns <SStearns@internode.com.au> writes:
> I have an archiving script running in an 8.3 environment using transaction_timestamp() for the INSERT and DELETE operations. I want to run the same thing in an 8.1 environment but transaction_timestamp() does not exist in 8.1. now() will not work because of the time that elapses between the INSERT and DELETE operations (I will end up losing some data). Does anyone have any suggestions, please?
Um ... transaction_timestamp() is exactly the same thing as now().
Perhaps you need to go re-read
http://www.postgresql.org/docs/8.3/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: transaction_timestamp()
2010-05-25 04:25 transaction_timestamp() Samuel Stearns <SStearns@internode.com.au>
2010-05-25 04:31 ` Re: transaction_timestamp() Tom Lane <tgl@sss.pgh.pa.us>
2010-05-25 05:15 ` Re: transaction_timestamp() Samuel Stearns <SStearns@internode.com.au>
@ 2010-05-25 17:42 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-05-25 23:09 ` Re: transaction_timestamp() Samuel Stearns <SStearns@internode.com.au>
0 siblings, 1 reply; 6+ messages in thread
From: Kevin Grittner @ 2010-05-25 17:42 UTC (permalink / raw)
To: Samuel Stearns <SStearns@internode.com.au>; +Cc: pgsql-admin; Tom Lane <tgl@sss.pgh.pa.us>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: transaction_timestamp()
2010-05-25 04:25 transaction_timestamp() Samuel Stearns <SStearns@internode.com.au>
2010-05-25 04:31 ` Re: transaction_timestamp() Tom Lane <tgl@sss.pgh.pa.us>
2010-05-25 05:15 ` Re: transaction_timestamp() Samuel Stearns <SStearns@internode.com.au>
2010-05-25 17:42 ` Re: transaction_timestamp() Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-05-25 23:09 ` Samuel Stearns <SStearns@internode.com.au>
2010-05-26 15:40 ` Re: transaction_timestamp() Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 6+ messages in thread
From: Samuel Stearns @ 2010-05-25 23:09 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-admin; Tom Lane <tgl@sss.pgh.pa.us>
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.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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: transaction_timestamp()
2010-05-25 04:25 transaction_timestamp() Samuel Stearns <SStearns@internode.com.au>
2010-05-25 04:31 ` Re: transaction_timestamp() Tom Lane <tgl@sss.pgh.pa.us>
2010-05-25 05:15 ` Re: transaction_timestamp() Samuel Stearns <SStearns@internode.com.au>
2010-05-25 17:42 ` Re: transaction_timestamp() Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-05-25 23:09 ` Re: transaction_timestamp() Samuel Stearns <SStearns@internode.com.au>
@ 2010-05-26 15:40 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 6+ messages in thread
From: Kevin Grittner @ 2010-05-26 15:40 UTC (permalink / raw)
To: Samuel Stearns <SStearns@internode.com.au>; +Cc: pgsql-admin; Tom Lane <tgl@sss.pgh.pa.us>
Samuel Stearns <SStearns@internode.com.au> wrote:
> Sorry about the waste of time.
It happens. One tip which might be useful with future issues is to
try to put together a simple case demonstrating the issue (similar
to what I posted on this thread). Sometimes the process of
preparing that will make the answer obvious; when it doesn't,
posting it will make it much easier for people to give useful
advice.
I'm glad you've got a solution that works for you now.
-Kevin
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-05-26 15:40 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-05-25 04:25 transaction_timestamp() Samuel Stearns <SStearns@internode.com.au>
2010-05-25 04:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-05-25 05:15 ` Samuel Stearns <SStearns@internode.com.au>
2010-05-25 17:42 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-05-25 23:09 ` Samuel Stearns <SStearns@internode.com.au>
2010-05-26 15:40 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox