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