The PITR docs that have just been put up say:

        But if you want to recover to some previous point in time (say,
        right before the junior DBA dropped your main transaction
        table), just specify the required stopping point in
        recovery.conf. You can specify the stop point either by
        date/time or by transaction ID. As of this writing only the
        date/time option is very usable, since there are no tools to
        help you identify which transaction ID to use.

How about adding a logging option to put the transaction id on the log
for every statement that modifies the database?  Would that be a small
enough change to be allowed into 8.0?
-- 
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
     "And not only so, but we glory in tribulations also; 
      knowing that tribulation worketh patience; And  
      patience, experience; and experience, hope."          
                                        Romans 5:3,4 


Wed, 04 Aug 2004 17:42:14 +0100
Oliver Elphick <olly@lfix.co.uk>
PITR - recovery to a particular transaction
Oliver Elphick <olly@lfix.co.uk> writes:
> How about adding a logging option to put the transaction id on the log
> for every statement that modifies the database?  Would that be a small
> enough change to be allowed into 8.0?

I think we could get away with adding transaction ID as one of the
available %-items in log_line_prefix.  I'm not sure how useful this
really is though --- timestamps are probably more useful overall to
have in your log.  The direction I was expecting we'd head in is to
provide WAL logfile examination tools.

			regards, tom lane

Wed, 04 Aug 2004 14:16:04 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: PITR - recovery to a particular transaction 
On Wed, 2004-08-04 at 19:16, Tom Lane wrote:
> Oliver Elphick <olly@lfix.co.uk> writes:
> > How about adding a logging option to put the transaction id on the log
> > for every statement that modifies the database?  Would that be a small
> > enough change to be allowed into 8.0?
> 
> I think we could get away with adding transaction ID as one of the
> available %-items in log_line_prefix.  I'm not sure how useful this
> really is though --- timestamps are probably more useful overall to
> have in your log.

Why not both?

You seem to be suggesting that using the id is less useful than the
time, but surely it's going to be easier to say "this disaster happened
in transaction 123 so lets do a PITR up to 122" than to say "this
happened at time x so do PITR up to x - 1 second"; the latter might miss
several tranactions.  Have I got the concepts wrong here?

>   The direction I was expecting we'd head in is to
> provide WAL logfile examination tools.

But that's not going to happen for 8.0, so any means of getting the
transaction id is better than none.

-- 
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
     "And not only so, but we glory in tribulations also; 
      knowing that tribulation worketh patience; And  
      patience, experience; and experience, hope."          
                                        Romans 5:3,4 


Wed, 04 Aug 2004 20:05:05 +0100
Oliver Elphick <olly@lfix.co.uk>
Re: PITR - recovery to a particular transaction
> You seem to be suggesting that using the id is less useful than the
> time, but surely it's going to be easier to say "this disaster happened
> in transaction 123 so lets do a PITR up to 122" than to say "this

Transaction IDs are assigned at transaction start but what you really
want is some indicator of when the commit occurred.

Transaction 123 may have committed while 122 was still running.



Wed, 04 Aug 2004 15:15:29 -0400
Rod Taylor <pg@rbt.ca>
Re: PITR - recovery to a particular transaction
Rod Taylor wrote:
> > You seem to be suggesting that using the id is less useful than the
> > time, but surely it's going to be easier to say "this disaster happened
> > in transaction 123 so lets do a PITR up to 122" than to say "this
> 
> Transaction IDs are assigned at transaction start but what you really
> want is some indicator of when the commit occurred.
> 
> Transaction 123 may have committed while 122 was still running.

True.  In fact this brings up a problem of using the xid for recovery
stop.  The interesting point is that you might recover to just before
xact 123, but that doesn't mean you get xact 122.

Still I think we need to add xid to the log_line_prefix for PITR and
make it clear that specifying a recovery xid doesn't always include
earlier xids.  I have added this to the open items list.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 4 Aug 2004 17:13:51 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: PITR - recovery to a particular transaction

When we do a PITR recovery based on xid, does it stop recovery based on
the start of the xid or the commit of the xid?  And if you say
recovery_target_inclusive =true, does it recover that xid while not
recoverying other xids that are higher but committed sooner than the
target xid?

---------------------------------------------------------------------------

Oliver Elphick wrote:
> On Wed, 2004-08-04 at 19:16, Tom Lane wrote:
> > Oliver Elphick <olly@lfix.co.uk> writes:
> > > How about adding a logging option to put the transaction id on the log
> > > for every statement that modifies the database?  Would that be a small
> > > enough change to be allowed into 8.0?
> > 
> > I think we could get away with adding transaction ID as one of the
> > available %-items in log_line_prefix.  I'm not sure how useful this
> > really is though --- timestamps are probably more useful overall to
> > have in your log.
> 
> Why not both?
> 
> You seem to be suggesting that using the id is less useful than the
> time, but surely it's going to be easier to say "this disaster happened
> in transaction 123 so lets do a PITR up to 122" than to say "this
> happened at time x so do PITR up to x - 1 second"; the latter might miss
> several tranactions.  Have I got the concepts wrong here?
> 
> >   The direction I was expecting we'd head in is to
> > provide WAL logfile examination tools.
> 
> But that's not going to happen for 8.0, so any means of getting the
> transaction id is better than none.
> 
> -- 
> Oliver Elphick                                          olly@lfix.co.uk
> Isle of Wight                              http://www.lfix.co.uk/oliver
> GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
>                  ========================================
>      "And not only so, but we glory in tribulations also; 
>       knowing that tribulation worketh patience; And  
>       patience, experience; and experience, hope."          
>                                         Romans 5:3,4 
> 
> 
> ---------------------------(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
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 6 Aug 2004 23:22:50 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: PITR - recovery to a particular transaction
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> When we do a PITR recovery based on xid, does it stop recovery based on
> the start of the xid or the commit of the xid?

You can stop either "before" or "after" that commit.  See
recovery.conf.sample (I don't think it's documented anywhere else
yet :-(),

			regards, tom lane

Sat, 07 Aug 2004 00:07:31 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: PITR - recovery to a particular transaction 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > When we do a PITR recovery based on xid, does it stop recovery based on
> > the start of the xid or the commit of the xid?
> 
> You can stop either "before" or "after" that commit.  See
> recovery.conf.sample (I don't think it's documented anywhere else
> yet :-(),

Yea, my question is if you choose "after", do you get everything that
happens until the "after" transaction commits, or just when it begins. 
If I stop after xid 125, and xid 126 starts and stops before 125
commits, does 126 get restored?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sat, 7 Aug 2004 13:33:32 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: PITR - recovery to a particular transaction
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Yea, my question is if you choose "after", do you get everything that
> happens until the "after" transaction commits, or just when it begins. 
> If I stop after xid 125, and xid 126 starts and stops before 125
> commits, does 126 get restored?

Yes.  You don't get to be selective about what to keep: it's everything
up to a certain time instant, and nothing after that.  Stopping by XID
is just a different way of identifying what that time instant is.

BTW, stopping "before" an XID actually means stopping just before its
commit or abort record, so transactions that ended before it did will
be included in the recovery.

			regards, tom lane

Sat, 07 Aug 2004 14:00:50 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: PITR - recovery to a particular transaction 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Yea, my question is if you choose "after", do you get everything that
> > happens until the "after" transaction commits, or just when it begins. 
> > If I stop after xid 125, and xid 126 starts and stops before 125
> > commits, does 126 get restored?
> 
> Yes.  You don't get to be selective about what to keep: it's everything
> up to a certain time instant, and nothing after that.  Stopping by XID
> is just a different way of identifying what that time instant is.
> 
> BTW, stopping "before" an XID actually means stopping just before its
> commit or abort record, so transactions that ended before it did will
> be included in the recovery.

OK, I added a mention of this in the docs.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sat, 7 Aug 2004 14:07:39 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: PITR - recovery to a particular transaction