Re: Lifetime of commit timestamps

2023-11-24 Thread Bruce Momjian
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  https://momjian.us
  EDB  https://enterprisedb.com

  Only you can decide what is important to you.




Re: Lifetime of commit timestamps

2023-11-17 Thread Bruce Momjian
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  https://momjian.us
  EDB  https://enterprisedb.com

  Only you can decide what is important to you.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 93f068edcf..0f59b9f5f5 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25978,7 +25978,8 @@ SELECT collation for ('foo' COLLATE "de_DE");
 They only provide useful data when the
  configuration option is
 enabled, and only for transactions that were committed after it was
-enabled.
+enabled.  Commit timestamp information is routinely removed during
+vacuum.

 



Re: Lifetime of commit timestamps

2023-11-17 Thread Andres Freund
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




Re: Lifetime of commit timestamps

2023-11-17 Thread Bruce Momjian
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  https://momjian.us
  EDB  https://enterprisedb.com

  Only you can decide what is important to you.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 93f068edcf..b311a11b33 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25978,7 +25978,8 @@ SELECT collation for ('foo' COLLATE "de_DE");
 They only provide useful data when the
  configuration option is
 enabled, and only for transactions that were committed after it was
-enabled.
+enabled.  The commit timestamps of frozen tuples are removed during
+vacuum.

 



Re: Lifetime of commit timestamps

2023-11-17 Thread Euler Taveira
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.


--
Euler Taveira
EDB   https://www.enterprisedb.com/


Re: Lifetime of commit timestamps

2023-11-13 Thread Bruce Momjian
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
>   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.
> 

Is this documentation change still relevant?

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  Only you can decide what is important to you.