 
> - by time - but the time stamp on each xlog record only specifies to the
> second, which could easily be 10 or more commits (we hope....)
> 
> Should we use a different datatype than time_t for the commit timestamp,
> one that offers more fine grained differentiation between checkpoints?

Imho seconds is really sufficient. If you know a more precise position
you will probably know it from backend log or an xlog sniffer. With those
you can easily use the TransactionId way.

> - when we stop, keep reading records until EOF, just don't apply them.
> When we write a checkpoint at end of recovery, the unapplied
> transactions are buried alive, never to return.
> - stop where we stop, then force zeros to EOF, so that no possible
> record remains of previous transactions.
> I'm tempted by the first plan, because it is more straightforward and
> stands much less chance of me introducing 50 wierd bugs just before
> close.

But what if you restore because after that PIT everything went haywire
including the log ? Did you mean to apply the remaining changes but not 
commit those xids ? I think it is essential to only leave xlogs around 
that allow a subsequent rollforward from the same old full backup. 
Or is an instant new full backup required after restore ?

Andreas

Tue, 6 Jul 2004 09:38:47 +0200
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: Point in Time Recovery
On Tue, 6 Jul 2004, Zeugswetter Andreas SB SD wrote:

> > Should we use a different datatype than time_t for the commit timestamp,
> > one that offers more fine grained differentiation between checkpoints?
>
> Imho seconds is really sufficient. If you know a more precise position
> you will probably know it from backend log or an xlog sniffer. With those
> you can easily use the TransactionId way.

I'd also think that seconds are absolutely sufficient. From my daily
experience I can say that you're normally lucky to know the time
on one minute basis.
If you need to get closer, a log sniffer is unavoidable ...

Greetings, Klaus

-- 
Full Name   : Klaus Naumann     | (http://www.mgnet.de/) (Germany)
Phone / FAX : ++49/177/7862964  | E-Mail: (kn@mgnet.de)

Tue, 6 Jul 2004 12:54:55 +0200 (CEST)
spock@mgnet.de
Re: Point in Time Recovery
On Tue, 2004-07-06 at 08:38, Zeugswetter Andreas SB SD wrote:
>  > - by time - but the time stamp on each xlog record only specifies to the
> > second, which could easily be 10 or more commits (we hope....)
> > 
> > Should we use a different datatype than time_t for the commit timestamp,
> > one that offers more fine grained differentiation between checkpoints?
> 
> Imho seconds is really sufficient. If you know a more precise position
> you will probably know it from backend log or an xlog sniffer. With those
> you can easily use the TransactionId way.
> 

OK, thanks. I'll just leave the time_t datatype just the way it is.

Best Regards, Simon Riggs


Tue, 06 Jul 2004 20:58:48 +0100
Simon Riggs <simon@2ndquadrant.com>
Re: Point in Time Recovery
On 7/6/2004 3:58 PM, Simon Riggs wrote:

> On Tue, 2004-07-06 at 08:38, Zeugswetter Andreas SB SD wrote:
>>  > - by time - but the time stamp on each xlog record only specifies to the
>> > second, which could easily be 10 or more commits (we hope....)
>> > 
>> > Should we use a different datatype than time_t for the commit timestamp,
>> > one that offers more fine grained differentiation between checkpoints?
>> 
>> Imho seconds is really sufficient. If you know a more precise position
>> you will probably know it from backend log or an xlog sniffer. With those
>> you can easily use the TransactionId way.

TransactionID and timestamp is only sufficient if the transactions are 
selected by their commit order. Especially in read committed mode, 
consider this execution:

     xid-1: start
     xid-2: start
     xid-2: update field x
     xid-2: commit
     xid-1: update field y
     xid-1: commit

In this case, the update done by xid-1 depends on the row created by 
xid-2. So logically xid-2 precedes xid-1, because it made its changes 
earlier.

So you have to apply the log until you find the commit record of the 
transaction you want apply last, and then stamp all transactions that 
where in progress at that time as aborted.


Jan

>> 
> 
> OK, thanks. I'll just leave the time_t datatype just the way it is.
> 
> Best Regards, Simon Riggs
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Sat, 10 Jul 2004 10:17:40 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: Point in Time Recovery
On Sat, 2004-07-10 at 15:17, Jan Wieck wrote:
> On 7/6/2004 3:58 PM, Simon Riggs wrote:
> 
> > On Tue, 2004-07-06 at 08:38, Zeugswetter Andreas SB SD wrote:
> >>  > - by time - but the time stamp on each xlog record only specifies to the
> >> > second, which could easily be 10 or more commits (we hope....)
> >> > 
> >> > Should we use a different datatype than time_t for the commit timestamp,
> >> > one that offers more fine grained differentiation between checkpoints?
> >> 
> >> Imho seconds is really sufficient. If you know a more precise position
> >> you will probably know it from backend log or an xlog sniffer. With those
> >> you can easily use the TransactionId way.
> 
> TransactionID and timestamp is only sufficient if the transactions are 
> selected by their commit order. Especially in read committed mode, 
> consider this execution:
> 
>      xid-1: start
>      xid-2: start
>      xid-2: update field x
>      xid-2: commit
>      xid-1: update field y
>      xid-1: commit
> 
> In this case, the update done by xid-1 depends on the row created by 
> xid-2. So logically xid-2 precedes xid-1, because it made its changes 
> earlier.
> 
> So you have to apply the log until you find the commit record of the 
> transaction you want apply last, and then stamp all transactions that 
> where in progress at that time as aborted.
> 

Agreed.

I've implemented this exactly as you say....

This turns out to be very easy because:
- when looking where to stop we only ever stop at commit or aborts -
these are the only records that have timestamps anyway...
- any record that isn't specifically committed is not updated in the
clog and therefore not visible. The clog starts in indeterminate state,
0 and is then updated to either committed or aborted. Aborted and
indeterminate are handled similarly in the current code, to allow for
crash recovery - PITR doesn't change anything there.
So, PITR doesn't do anything that crash recovery doen't already do.
Crash recovery makes no attempt to keep track of in-progress
transactions and doesn't make a special journey to the clog to
specifically mark them as aborted - they just are by default.

So, what we mean by "stop at a transactionId" is "stop applying redo at
the commit/abort record for that transactionId." It has to be an exact
match, not a "greater than", for exactly the reason you mention. That
means that although we stop at the commit record of transactionId X, we
may also have applied records for transactions with later transactionIds
e.g. X+1, X+2...etc (without limit or restriction).

(I'll even admit that as first, I did think we could get away with the
"less than" test that you are warning me about. Overall, I've spent more
time on theory/analysis than on coding, on the idea that you can improve
poor code, but wrong code just needs to be thrown away).

Timestamps are more vague...When time is used, there might easily be 10+
transactions whose commit/abort records have identical timestamp values.
So we either stop at the first or last record depending upon whether we
specified inclusive or exclusive on the recovery target value.

The hard bit, IMHO, is what we do with the part of the log that we have
chosen not to apply....which has been discussed on list in detail also.

Thanks for keeping an eye out for possible errors - this one is
something I'd thought through and catered for (there are comments in my
current latest published code to that effect, although I have not yet
finished coding the clean-up-after-stopping part). 

This implies nothing with regard to other possible errors or oversights
and so I very much welcome any questioning of this nature - I am as
prone to error as the next man. It's important we get this right.

Best regards, Simon Riggs


Sat, 10 Jul 2004 22:06:51 +0100
Simon Riggs <simon@2ndquadrant.com>
Re: Point in Time Recovery