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:t...@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 <sstea...@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

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Reply via email to