Thread: Pausing at recovery target is off by one

pause_at_recovery_target doesn't do what I would expect when
recovery_target_inclusive='true' (which is the default). It pauses
before applying the target record, but if you then call
pg_xlog_replay_resume() to bring the system up, it will apply that one
more record, before exiting recovery. For example, if you do this in the
master:

insert into foo values (5);

Then crawl the WAL with pg_xlogdump to find the XID of that transaction,
and set recovery target to that:

recovery_target_xid='213890'
recovery_target_inclusive='true'
pause_at_recovery_target='true'

Now you connect to the paused system and verify that the state looks OK,
and call pg_xlog_replay(). Oops, the state it recovers to is not what
you expected:

postgres=# select ctid,* from foo;
  ctid  | i
-------+---
  (0,1) | 1
  (0,2) | 2
  (0,3) | 3
  (0,4) | 4
(4 rows)

postgres=# select pg_xlog_replay_resume();
  pg_xlog_replay_resume
-----------------------

(1 row)

postgres=# select ctid,* from foo;
  ctid  | i
-------+---
  (0,1) | 1
  (0,2) | 2
  (0,3) | 3
  (0,4) | 4
  (0,5) | 5
(5 rows)

I think this is a bug, and needs to be fixed.

- Heikki