Subject: Re: Point in Time Recovery
From: Simon Riggs <simon@2ndquadrant.com>
To: Richard Huxton <dev@archonet.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers@postgresql.org
In-Reply-To: <40EAF6E3.9090508@archonet.com>
References: <1089060299.17493.58.camel@stromboli>
	<4263.1089064016@sss.pgh.pa.us> <1089069104.17493.132.camel@stromboli>
	<40EAF6E3.9090508@archonet.com>
Content-Type: text/plain
Organization: 2nd Quadrant
Message-Id: <1089149984.17493.268.camel@stromboli>
Mime-Version: 1.0
Date: Tue, 06 Jul 2004 22:39:44 +0100
Content-Transfer-Encoding: 7bit

On Tue, 2004-07-06 at 20:00, Richard Huxton wrote:
> Simon Riggs wrote:
> > On Mon, 2004-07-05 at 22:46, Tom Lane wrote:
> > 
> >>Simon Riggs <simon@2ndquadrant.com> writes:
> >>
> >>>Should we use a different datatype than time_t for the commit timestamp,
> >>>one that offers more fine grained differentiation between checkpoints?
> >>
> >>Pretty much everybody supports gettimeofday() (time_t and separate
> >>integer microseconds); you might as well use that.  Note that the actual
> >>resolution is not necessarily microseconds, and it'd still not be
> >>certain that successive commits have distinct timestamps --- so maybe
> >>this refinement would be pointless.  You'll still have to design a user
> >>interface that allows selection without the assumption of distinct
> >>timestamps.
> > 
> > 
> > Well, I agree, though without the desired-for UI now, I think some finer
> > grained mechanism would be good. This means extending the xlog commit
> > record by a couple of bytes...OK, lets live a little.
> 
> At the risk of irritating people, I'll repeat what I suggested a few 
> weeks ago...
> 

All feedback is good. Thanks.

> Add a table: pg_pitr_checkpt (pitr_id SERIAL, pitr_ts timestamptz, 
> pitr_comment text)
> Let the user insert rows in transactions as desired. Let them stop the 
> restore when a specific (pitr_ts,pitr_comment) gets inserted (or on 
> pitr_id if they record it).
> 

It's a good plan, but the recovery is currently offline recovery and no
SQL is possible. So no way to insert, no way to access tables until
recovery completes. I like that plan and probably would have used it if
it was viable.

> IMHO time is seldom relevant, event boundaries are.
> 

Agreed, but time is the universally agreed way of describing two events
as being simultaneous. No other way to say "recover to the point when
the message queue went wild".

As of last post to Andreas, I've said I'll not bother changing the
granularity of the timestamp.

> If you want to add special syntax for this, fine. If not, an INSERT 
> statement is a convenient way to do this anyway.

The special syntax isn't hugely important - I did suggest a kind of
SQL-like syntax previously, but thats gone now. Invoking recovery via a
command file IS, so we are able to tell the system its not in crash
recovery AND that when you've finished I want you to respond to crashes
without re-entering archive recovery.

Thanks for your comments. I'm not making this more complex than needs
be; in fact much of the code is very simple - its just the planning
that's complex.

Best regards, Simon Riggs