Re: What's the best practice to compare the transaction with the checkpoint?

Gerald Cheves <gcheves@verizon.net> writes:
> On 7/6/2020 10:09 AM, Jialun Zhang wrote:
>> Thanks! But what I actually mean is how to do this in the backend 
>> source code. I think I could find a way to do this SELECT in backend.

> What would be the advantage in that method?

I'm kind of wondering what the point is at all?  Generally, once a
transaction's actions have been fsynced down to WAL, we consider it
adequately persisted.  Checkpoints are an orthogonal mechanism that's
just meant to limit the amount of WAL that would have to be replayed
in event of a crash.

ISTM, if you're asking whether a transaction was completed before the
last checkpoint, you're essentially saying you don't trust WAL replay;
but at that point you're in a world of hurt.  Even if the transaction's
own actions are all forced out to the main storage area, there are very
likely other later transactions that have touched the same disk pages.
WAL replay failure would likely leave those pages corrupt and unsafe to
read, whereupon you've got nothing.

If, nonetheless, you think that "was the transaction completed before
the last checkpoint" is an interesting question, you'd have to figure
out where the transaction's commit record appeared in the WAL stream,
and then compare that to the replay restart point appearing in
pg_control.  Unfortunately, I do not think there's any easy way to
get from a transaction ID to a commit WAL location.  (Awhile back,
Heikki Linnakangas was working on a redesign of the snapshot mechanism
that would've required making such lookups cheap, but I'm not sure
that plan will ever reach fruition.)

            regards, tom lane