pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Samuel Stearns <SStearns@internode.com.au>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: transaction_timestamp()
Date: Tue, 25 May 2010 14:45:05 +0930
Message-ID: <68B59BEDCD36854AADBDF17E91B2937A01A57CCD01@EXCHMAIL.staff.internode.com.au> (raw)
In-Reply-To: <17418.1274761902@sss.pgh.pa.us>
References: <68B59BEDCD36854AADBDF17E91B2937A01A57CCCB9@EXCHMAIL.staff.internode.com.au>
	<17418.1274761902@sss.pgh.pa.us>

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



view thread (6+ messages)  latest in thread

Message-ID: <68B59BEDCD36854AADBDF17E91B2937A01A57CCD01@EXCHMAIL.staff.internode.com.au>
Permalink:  ../68B59BEDCD36854AADBDF17E91B2937A01A57CCD01@EXCHMAIL.staff.internode.com.au/
Also on:    postgresql.org/message-id/68B59BEDCD36854AADBDF17E91B2937A01A57CCD01@EXCHMAIL.staff.internode.com.au

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: SStearns@internode.com.au, tgl@sss.pgh.pa.us
  Subject: Re: transaction_timestamp()
  In-Reply-To: <68B59BEDCD36854AADBDF17E91B2937A01A57CCD01@EXCHMAIL.staff.internode.com.au>
  References: <68B59BEDCD36854AADBDF17E91B2937A01A57CCCB9@EXCHMAIL.staff.internode.com.au> <17418.1274761902@sss.pgh.pa.us> <68B59BEDCD36854AADBDF17E91B2937A01A57CCD01@EXCHMAIL.staff.internode.com.au>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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