Обсуждение: BUG #3401: PITR does not work in the case of recovery_target_xid = 'SELECT_only_transaction_ID'
BUG #3401: PITR does not work in the case of recovery_target_xid = 'SELECT_only_transaction_ID'
От
"Katsuhiko Okano"
Дата:
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
Re: BUG #3401: PITR does not work in the case ofrecovery_target_xid = 'SELECT_only_transaction_ID'
От
Katsuhiko Okano
Дата:
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