Thread: BUG #3401: PITR does not work in the case of recovery_target_xid = 'SELECT_only_transaction_ID'

The following bug has been logged online:

Bug reference:      3401
Logged by:          Katsuhiko Okano
Email address:      okano.katsuhiko@oss.ntt.co.jp
PostgreSQL version: Head(20070607)
Operating system:   Fedora Core release 5 (Bordeaux)
Description:        PITR does not work in the case of recovery_target_xid =
'SELECT_only_transaction_ID'
Details:

Transaction ID is wasted even if it is a transaction containing only
SELECT.
Transaction ID can be specified when recovering using a PITR feature.
For example, in recovery.conf,
--------
recovery_target_xid = '1100842'
--------

A PITR feature does not work in the case which specified transaction ID in
recovery_target_xid
corresponding to the transaction which contains only SELECT
It expects to stop in foward of target ID or in backward of target ID.
However, it does not stop while recovery done.
Recovery is done to the last and it becomes the newest.(target ID is
disregarded)

This operation is unexpected.I think that it should describe on a document.

"Katsuhiko Okano" <okano.katsuhiko@oss.ntt.co.jp> writes:
> Transaction ID can be specified when recovering using a PITR feature.
> For example, in recovery.conf,
> --------
> recovery_target_xid = '1100842'
> --------

The documentation already points out that this feature is currently
useless due to the lack of any tools for determining a suitable target
XID.  Presumably such a tool would help you dig through the WAL log
to see which transactions did what.  I'm not very concerned about the
set of possible target XIDs not being complete.

            regards, tom lane

On Thu, 2007-06-21 at 11:17 +0000, Katsuhiko Okano wrote:

> Description:        PITR does not work in the case of recovery_target_xid =
> 'SELECT_only_transaction_ID'
> Details:

If a transaction is purely read only then there is no COMMIT or ABORT
message written to the transaction log. As a result there is no way to
know at what point that transaction occurred, nor any way to use the
recovery_target_xid on those kinds of transactions. This is not a bug in
PITR, it is a specific optimisation of WAL to improve the throughput of
read-only queries.

I'll add this to the docs in my next round of docs changes.

How did you come to choose an xid of this nature?

--
  Simon Riggs
  EnterpriseDB   http://www.enterprisedb.com

Simon Riggs wrote:
> On Thu, 2007-06-21 at 11:17 +0000, Katsuhiko Okano wrote:
>
>> Description:        PITR does not work in the case of recovery_target_xid =
>> 'SELECT_only_transaction_ID'
>> Details:
(snip)
> How did you come to choose an xid of this nature?

specify log_statement = 'all'  and log_line_prefix = '[%x]' in postgresql.conf
(I know this approach is not useful and hardly used on actual system management.)

output server log like below:
[621]LOG:  statement: CREATE TABLE xxx(col1 integer);
[622]LOG:  statement: SELECT * FROM xxx;
[623]LOG:  statement: SELECT * FROM xxx;
[624]LOG:  statement: SELECT * FROM xxx;
[625]LOG:  statement: INSERT INTO xxx VALUES (1);
[626]LOG:  statement: INSERT INTO xxx VALUES (2);
[627]LOG:  statement: INSERT INTO xxx VALUES (3);
[628]LOG:  statement: SELECT * FROM xxx;
[629]LOG:  statement: SELECT * FROM xxx;
[630]LOG:  statement: SELECT * FROM xxx;


Regards,
--
--------
Katsuhiko Okano
okano katsuhiko _at_ oss ntt co jp

Katsuhiko Okano <okano.katsuhiko@oss.ntt.co.jp> writes:
> Simon Riggs wrote:
>> How did you come to choose an xid of this nature?

> specify log_statement = 'all'  and log_line_prefix = '[%x]' in postgresql.conf
> (I know this approach is not useful and hardly used on actual system management.)

No, it's not very useful because you can't be sure that the order of
commit records in the WAL file will match what you see in the postmaster
log.  If the transactions are sufficiently well spread apart in time
that you *can* be sure of that, you might as well use timestamps anyway.
The reason for having the XID option in recovery.conf at all is to allow
an exact stop point specification when a timestamp is too inaccurate
--- but in a situation like that, you'd really have to have grovelled
through the WAL file with some kind of dump tool to determine which XID
you want to specify.

BTW, as of 8.3 commit timestamps have full gettimeofday() precision,
they're not just time_t values; so the use-case for stopping by XID
is even narrower than it used to be.

            regards, tom lane