Thread: Lifetime of commit timestamps
Hello.
I don't find any description in the documentation about the
guaranteed lifetime of commit timestamps. I think they are
preserved until corresponding xid goes beyond the freeze horizen,
even though they are actually preserved longer for several
reasons.
If it is not, I think such description is required in
pg_xact_commit_timestamp().
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5dce8ef178..633e488cec 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18029,7 +18029,8 @@ SELECT collation for ('foo' COLLATE "de_DE");
These functions mainly provide information about when the transactions
were committed. They only provide useful data when
<xref linkend="guc-track-commit-timestamp"/> configuration option is enabled
- and only for transactions that were committed after it was enabled.
+ and only for transactions that were committed after it was enabled. Commit
+ timestamps for frozen tuples are removed at vacuum time.
</para>
<table id="functions-commit-timestamp">
On Fri, Jun 22, 2018 at 05:21:32PM +0900, Kyotaro HORIGUCHI wrote:
> Hello.
>
> I don't find any description in the documentation about the
> guaranteed lifetime of commit timestamps. I think they are
> preserved until corresponding xid goes beyond the freeze horizen,
> even though they are actually preserved longer for several
> reasons.
>
> If it is not, I think such description is required in
> pg_xact_commit_timestamp().
> diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
> index 5dce8ef178..633e488cec 100644
> --- a/doc/src/sgml/func.sgml
> +++ b/doc/src/sgml/func.sgml
> @@ -18029,7 +18029,8 @@ SELECT collation for ('foo' COLLATE "de_DE");
> These functions mainly provide information about when the transactions
> were committed. They only provide useful data when
> <xref linkend="guc-track-commit-timestamp"/> configuration option is enabled
> - and only for transactions that were committed after it was enabled.
> + and only for transactions that were committed after it was enabled. Commit
> + timestamps for frozen tuples are removed at vacuum time.
> </para>
Is this documentation change still relevant?
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
On Mon, Nov 13, 2023, at 9:47 PM, Bruce Momjian wrote:
Is this documentation change still relevant?
I think so. AFAICS nothing changed. Unless you read the source code, it is not
clear that VACUUM removes the information for frozen tuples. They are decoupled
(but executed in the same routine for convenience), hence, someone can ask why
the pg_xact_commit_timestamp() returns NULL for a transaction that was executed
*after* you enable track_commit_timestamp. The answer is the design used a
existing mechanism to clean up data in order to avoid creating a new one.
On Fri, Nov 17, 2023 at 03:39:14PM -0300, Euler Taveira wrote: > On Mon, Nov 13, 2023, at 9:47 PM, Bruce Momjian wrote: > > Is this documentation change still relevant? > > > I think so. AFAICS nothing changed. Unless you read the source code, it is not > clear that VACUUM removes the information for frozen tuples. They are decoupled > (but executed in the same routine for convenience), hence, someone can ask why > the pg_xact_commit_timestamp() returns NULL for a transaction that was executed > *after* you enable track_commit_timestamp. The answer is the design used a > existing mechanism to clean up data in order to avoid creating a new one. Okay, I have developed the attached patch based on Horiguchi-san's version. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Attachment
Hi, On 2023-11-17 15:39:14 -0300, Euler Taveira wrote: > On Mon, Nov 13, 2023, at 9:47 PM, Bruce Momjian wrote: > > Is this documentation change still relevant? > > I think so. AFAICS nothing changed. Unless you read the source code, it is not > clear that VACUUM removes the information for frozen tuples. They are decoupled > (but executed in the same routine for convenience), hence, someone can ask why > the pg_xact_commit_timestamp() returns NULL for a transaction that was executed > *after* you enable track_commit_timestamp. I think the connection between freezing and removal of commit timestamps is a lot less direct that your suggested docs suggest. There can be no freezing and we'll still remove timestamps (if tuples were deleted/updated). And tuples can be frozen without the committs being truncated (if other tables have an older relfrozenxid). The relevant limiting factor is minimum of all databases datfrozenxid. Which in turn is limited by relfrozenxid of each table in said database. And relfrozenxid is limited by snapshots (and prepared transactions, replication slots, etc). > The answer is the design used a existing mechanism to clean up data in order > to avoid creating a new one. I don't really understand this part - independent of the mechanism (i.e. an slru), at some point we need to remove old data, just for space reasons. Greetings, Andres Freund
On Fri, Nov 17, 2023 at 01:20:46PM -0800, Andres Freund wrote: > Hi, > > On 2023-11-17 15:39:14 -0300, Euler Taveira wrote: > > On Mon, Nov 13, 2023, at 9:47 PM, Bruce Momjian wrote: > > > Is this documentation change still relevant? > > > > I think so. AFAICS nothing changed. Unless you read the source code, it is not > > clear that VACUUM removes the information for frozen tuples. They are decoupled > > (but executed in the same routine for convenience), hence, someone can ask why > > the pg_xact_commit_timestamp() returns NULL for a transaction that was executed > > *after* you enable track_commit_timestamp. > > I think the connection between freezing and removal of commit timestamps is a > lot less direct that your suggested docs suggest. There can be no freezing and > we'll still remove timestamps (if tuples were deleted/updated). And tuples can > be frozen without the committs being truncated (if other tables have an older > relfrozenxid). > > The relevant limiting factor is minimum of all databases datfrozenxid. Which > in turn is limited by relfrozenxid of each table in said database. And > relfrozenxid is limited by snapshots (and prepared transactions, replication > slots, etc). Okay, I went with more weasel-wording in the attached patch. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Attachment
On Fri, Nov 17, 2023 at 04:36:44PM -0500, Bruce Momjian wrote: > On Fri, Nov 17, 2023 at 01:20:46PM -0800, Andres Freund wrote: > > On 2023-11-17 15:39:14 -0300, Euler Taveira wrote: > > > > I think the connection between freezing and removal of commit timestamps is a > > lot less direct that your suggested docs suggest. There can be no freezing and > > we'll still remove timestamps (if tuples were deleted/updated). And tuples can > > be frozen without the committs being truncated (if other tables have an older > > relfrozenxid). > > > > The relevant limiting factor is minimum of all databases datfrozenxid. Which > > in turn is limited by relfrozenxid of each table in said database. And > > relfrozenxid is limited by snapshots (and prepared transactions, replication > > slots, etc). > > Okay, I went with more weasel-wording in the attached patch. Patch applied back to PG 16. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.