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

From: Jialun Zhang <[email-masked]>
Date: Sat, 4 Jul 2020 12:19:15 -0400

Hi All,

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?

Thanks,
Jialun Zhang

---

From: Laurenz Albe <[email-masked]>
Date: Mon, 06 Jul 2020 10:09:09 +0200

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

---

From: Jialun Zhang <[email-masked]>
Date: Mon, 6 Jul 2020 10:09:35 -0400

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.

Thank you,
Jialun Zhang

Laurenz Albe <[email-masked]> 于2020年7月6日周一 上午4:09写道：

> checkpointed or not.

---

From: Gerald Cheves <[email-masked]>
Date: Mon, 6 Jul 2020 10:58:40 -0400

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?

>
> Thank you,
> Jialun Zhang
>
> Laurenz Albe <[email-masked] 
> <mailto:[email-masked]>> 于2020年7月6日周一 上午4:09写道：
>

-- 
siamo arrivati sani e salvi

---

From: Tom Lane <[email-masked]>
Date: Mon, 06 Jul 2020 11:20:14 -0400

> 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

---

From: Jialun Zhang <[email-masked]>
Date: Mon, 6 Jul 2020 11:27:39 -0400

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. Therefore, for garbage collection, we need to remove the file
after confirming that a deleted tuple is older than the latest checkpoint.

Thanks,
JIalun Zhang

---

From: Tom Lane <[email-masked]>
Date: Mon, 06 Jul 2020 11:44:35 -0400

Jialun Zhang <[email-masked]> writes:
> pointer.

Why in the world would that be a good idea?

> Therefore, for garbage collection, we need to remove the file

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

---

From: Jialun Zhang <[email-masked]>
Date: Mon, 6 Jul 2020 12:23:31 -0400

Thanks for your detailed reply!

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.

Is my understanding correct? Is this situation possible? Actually this
question is my reason to think about the checkpointing things.

Best,
Jialun Zhang

---

From: Tom Lane <[email-masked]>
Date: Mon, 06 Jul 2020 12:34:48 -0400

> 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

---

From: Jialun Zhang <[email-masked]>
Date: Mon, 6 Jul 2020 17:50:17 -0400

If we use V to compute another value, and then V is deleted and becomes a
dead tuple. Later, we do VACUUM, V and its file is removed. Then the system
crashes. When it tries to replay the xlog, it only sees the file path in
the checkpoint but the file doesn't exist any more.

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.

Best regards,
Jialun Zhang

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 07 Jul 2020 11:41:46 +0200

> 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