Обсуждение: Automated way to find actual COMMIT LSN of subxact LSN
I want to build automation to recover a database to a specific LSN *inclusive*, even if that LSN is from a subtransaction. The problem I am facing is that I know what specific LSN wrote a row on a remote system, but if I create a recovery.conf file with:
recovery_target_lsn = '95F/BBA36DF8'
and 95F/BBA36DF8 is actually a subtransaction, then even if I use default behavior of recovery_target_inclusive = true, that transaction will NOT be included in the restore point, because it is prior to the actual COMMIT LSN of which this lsn/subxact is a part.
My hack for now is to simply manually scan down until I find the COMMIT, which is the only way so far I can figure to find it out. I don't want to hack some kind of search script based on this if there is already a better way to get this information... anyone know of a way?
Thank you,
Jeremy
At Tue, 19 Mar 2019 12:16:34 -0500, Jeremy Finzel <finzelj@gmail.com> wrote in <CAMa1XUjZyq9sf1COSL-VPe9khpdu52WUoeWECUQDthGwtmb3vQ@mail.gmail.com> > I want to build automation to recover a database to a specific LSN > *inclusive*, even if that LSN is from a subtransaction. The problem I am > facing is that I know what specific LSN wrote a row on a remote system, but > if I create a recovery.conf file with: > > recovery_target_lsn = '95F/BBA36DF8' > > and 95F/BBA36DF8 is actually a subtransaction, then even if I use default > behavior of recovery_target_inclusive = true, that transaction will NOT be > included in the restore point, because it is prior to the actual COMMIT LSN > of which this lsn/subxact is a part. > > My hack for now is to simply manually scan down until I find the COMMIT, > which is the only way so far I can figure to find it out. I don't want to > hack some kind of search script based on this if there is already a better > way to get this information... anyone know of a way? FWIW it seems to be the only way starting from an LSN. If you can identify the XID or end timestamp of the transaction, it would be usable instead. If recovery_target_inclusive were able to take the third value "xact", is it exactly what you want? And is it acceptable? regards. -- Kyotaro Horiguchi NTT Open Source Software Center
If recovery_target_inclusive were able to take the third value
"xact", is it exactly what you want?
And is it acceptable?
Yes, that would be exactly what I would want. It would work to have a 3rd value for recovery_target_inclusive, although perhaps it's debatable that instead, it should actually be the default behavior to roll any LSN with recovery_target_inclusive = true until it is actually visible? If I say I want to "include" an LSN in my recovery target, it doesn't make much sense if that just won't be visible unless it's an actual commit LSN, so in fact the recovery point does not include the LSN.
A related problem kind of demonstrates the same odd behavior. If you put in recovery_target_xid to a subtransaction_id, it just skips it and continues recovering, which really seems to be undesirable behavior. It would be nice if that also could roll up to the next valid actual commit transaction.
Thanks!
Jeremy
Jeremy Finzel <finzelj@gmail.com> writes:
> A related problem kind of demonstrates the same odd behavior. If you put
> in recovery_target_xid to a subtransaction_id, it just skips it and
> continues recovering, which really seems to be undesirable behavior. It
> would be nice if that also could roll up to the next valid actual commit
> transaction.
It would seem like what you're asking for is to continue until the commit
of the parent transaction, not just the next commit after the subcommit.
Otherwise (if that's an unrelated xact) the subxact would still not be
committed, so that you might as well have stopped short of it.
I'd be in favor of that for recovery_target_xid, but I'm not at all
convinced about changing the behavior for a target LSN. The fact that
the target is a subcommit seems irrelevant when you specify by LSN.
I don't recall this for sure, but doesn't a parent xact's commit record
include all subxact XIDs? If so, the implementation would just require
searching the subxacts as well as the main XID for a match to
recovery_target_xid.
regards, tom lane
It would seem like what you're asking for is to continue until the commit
of the parent transaction, not just the next commit after the subcommit.
Otherwise (if that's an unrelated xact) the subxact would still not be
committed, so that you might as well have stopped short of it.
Right, the parent transaction is what I meant.
I'd be in favor of that for recovery_target_xid, but I'm not at all
convinced about changing the behavior for a target LSN. The fact that
the target is a subcommit seems irrelevant when you specify by LSN.
Perhaps some context will help. There have been 2 cases in which I have tried to do this, both of them based on logical decoding, and finding either a transaction id or an LSN to recover to. Actually, the only reason I have ever used transaction id instead of LSN is on <= 9.6 because the latter isn't supported until pg10.
For this use case, my goal is simply to be able to recover the the point immediately after a particular decoded log line is visible, without necessarily having to find out the final parent transaction id.
Given this, I am open to different implementations but I would like to either be able to specify an LSN or transaction ID, and have a feature that allows the recovery target to roll forward just until it is visible, even if the LSN or transaction ID is not the actual commit of the parent transaction.
I don't recall this for sure, but doesn't a parent xact's commit record
include all subxact XIDs? If so, the implementation would just require
searching the subxacts as well as the main XID for a match to
recovery_target_xid.
Yes, I believe so.
Thanks,
Jeremy
Jeremy Finzel <finzelj@gmail.com> writes:
>> I'd be in favor of that for recovery_target_xid, but I'm not at all
>> convinced about changing the behavior for a target LSN. The fact that
>> the target is a subcommit seems irrelevant when you specify by LSN.
> For this use case, my goal is simply to be able to recover the the point
> immediately after a particular decoded log line is visible, without
> necessarily having to find out the final parent transaction id.
> Given this, I am open to different implementations but I would like to
> either be able to specify an LSN or transaction ID, and have a feature that
> allows the recovery target to roll forward just until it is visible, even
> if the LSN or transaction ID is not the actual commit of the parent
> transaction.
I find this to be unactionably vague. What does it mean to claim "an
LSN is visible"? An LSN might not even point to a WAL record, or it
might point to one that has nontransactional effects. Moreover, any
behavior of this sort would destroy what I regard as a bedrock property
of recover-to-LSN, which is that there's a well defined, guaranteed-finite
stopping point. (A property that recover-to-XID lacks, since the
specified XID might've crashed without recording either commit or abort.)
I think what you ought to be doing is digging the xmin out of the inserted
tuple you're concerned with and then doing recover-to-XID. There's
definitely room for us to make it easier if the XID is a subxact rather
than a main xact. But I think identifying the target XID is your job
not the job of the recovery-stop-point mechanism.
regards, tom lane
I find this to be unactionably vague. What does it mean to claim "an
LSN is visible"? An LSN might not even point to a WAL record, or it
might point to one that has nontransactional effects. Moreover, any
behavior of this sort would destroy what I regard as a bedrock property
of recover-to-LSN, which is that there's a well defined, guaranteed-finite
stopping point. (A property that recover-to-XID lacks, since the
specified XID might've crashed without recording either commit or abort.)
I mentioned that my specific use case is that I am picking out an LSN or XID within the context of logical decoding. So I don't think that's vague, but let me clarify. When using the peek_changes or get_changes functions, they only show a particular update to a particular row, with a corresponding LSN and transaction ID. That's what I see using both test_decoding and pglogical_output, both of which I have used in this way. In that context at least, all LSNs and XIDs point to committed WAL data only.
I think what you ought to be doing is digging the xmin out of the inserted
tuple you're concerned with and then doing recover-to-XID. There's
definitely room for us to make it easier if the XID is a subxact rather
than a main xact. But I think identifying the target XID is your job
not the job of the recovery-stop-point mechanism.
I'm open to that, but how will it help if I can't guarantee that the tuple wasn't updated again after the original insert/update of interest?
Thank you,
Jeremy