Обсуждение: What's the best practice to compare the transaction with the checkpoint?

On Sat, 2020-07-04 at 12:19 -0400, Jialun Zhang wrote:
> I am trying to add code to determine whether the transaction is checkpointed or not.
> So I think I need to compare the transaction ID with the checkpoint's and determine
> whether it is older or not. Could anyone tell me what's the best practice of doing
> this in Postgres? Is it done somewhere in the existing code?

You could use

   SELECT next_xid FROM pg_control_checkpoint();

to get information about the last checkpoint.

I refrain from asking what the point of the exercise is...

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

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?

Thank you,

Jialun Zhang

Laurenz Albe <laurenz.albe@cybertec.at> 于2020年7月6日周一 上午4:09写道：

On Sat, 2020-07-04 at 12:19 -0400, Jialun Zhang wrote:
> I am trying to add code to determine whether the transaction is checkpointed or not.
> So I think I need to compare the transaction ID with the checkpoint's and determine
> whether it is older or not. Could anyone tell me what's the best practice of doing
> this in Postgres? Is it done somewhere in the existing code?

You could use

   SELECT next_xid FROM pg_control_checkpoint();

to get information about the last checkpoint.

I refrain from asking what the point of the exercise is...

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

-- 
siamo arrivati sani e salvi

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

Jialun Zhang <reatank@gmail.com> writes:
> Actually I am trying to add file support for TOAST. I mean store the
> toasted value in the file system and leave a file path in the toast
> pointer.

Why in the world would that be a good idea?

> Therefore, for garbage collection, we need to remove the file
> after confirming that a deleted tuple is older than the latest checkpoint.

Even granting that shoving each toasted value into its own file is a
sane design, I do not see why you'd need to invent a GC mechanism for it.
Seems like you could drive removal of the file off vacuum's removal of
the parent tuple.

Moreover, even if you want to do it like that, you still don't need to
know whether the deleting transaction is older than the last checkpoint;
indeed that's entirely the wrong question.  What you need to know is
whether it's older than global xmin, so that it's certain no other
transaction will wish to fetch the field value.

            regards, tom lane

Jialun Zhang <reatank@gmail.com> writes:
> My question is that,  consider such a situation (in the order of time):
> 1. toast create a file for a value V;
> 2. latest checkpoint;
> 3. use V (here the file is read);
> 4. delete the file when VACUUM deletes V;
> 5. crash, then when we recover from the latest checkpoint, the file will be
> accessed again but it has gone.

What is "use V", and why would it be part of what is replayed in step 5?
Replay generally doesn't do anything that could access potentially-deleted
data, because depending on whether or not the updated page from step 4 got
written out before crashing, there may or may not be any such tuple.

The replay of step 4 would need to perform something like "delete the file
for V if it exists, but don't complain if it does not", since we couldn't
be sure which state we'll find on-disk.

            regards, tom lane

On Mon, 2020-07-06 at 17:50 -0400, Jialun Zhang wrote:
> > The replay of step 4 would need to perform something like "delete the file
> > for V if it exists, but don't complain if it does not", since we couldn't
> > be sure which state we'll find on-disk.
>
> What I am actually asking is that, is it possible that the replay depends
> on a deleted value, which has been physically removed by a VACUUM.
> Bear with my very basic knowledge in Postgres.

The WAL records that affect your out-of-line stored file would be written
by you, so you'd have to make sure that replaying such WAL records works
whether the file is present or not.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com