Automated way to find actual COMMIT LSN of subxact LSN

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