Re: Detailed questions about pg_xact_commit_timestamp - Mailing list pgsql-hackers

On 7/12/19 2:50 PM, Morris de Oryx wrote:
> Adrien, thanks very much for answering my question. Just a couple of follow-up
> points, if you don't mind.
>
> In our answer, you offer an example of pg_xact_commit_timestamp showing
> out-of-sequence commit times:
>
> Session     xid          pg_xact_commit_timestamp
> A           34386826     2019-07-11 09:32:38.994440+00  Started earlier,
> committed later
> B           34386827     2019-07-11 09:32:29.806183+00
>
> I may not have asked my question clearly, or I may not understand the answer
> properly. Or both ;-) If I understand it correctly, an xid is assigned when a
> transaction starts.

It is a little bit more complicated :) When a transaction start, a *virtual* xid
is assigned. It is when the transaction change the state of the database, an xid
is assigned:
> Throughout running a transaction, a server process holds an exclusive lock on the transaction's virtual transaction ID. If a permanent ID is assigned to the transaction (which normally happens only if the transaction changes the state of the database), it also holds an exclusive lock on the transaction's permanent transaction ID until it ends.

https://www.postgresql.org/docs/current/view-pg-locks.html

(It shouldn't change anything for you)


> One transaction might take a second, another might take ten
> minutes. So, the xid sequence doesn't imply anything at all about commit
> sequence. What I'm trying to figure out is if it is possible for the commit
> timestamps to somehow be out of order.

I am sorry but I don't understand what you mean by "commit timestamps to somehow
be out of order"?

> What I'm looking for is a way of finding
> changes committed since a specific moment. When the transaction started doesn't
> matter in my case.


Yes, the commit timestamp is the time when the transaction is committed :
postgres=# begin;
BEGIN
postgres=# select now();
             now
------------------------------
 2019-07-16 08:46:59.64712+00
(1 row)

postgres=# select txid_current();
 txid_current
--------------
     34386830
(1 row)

postgres=# commit;
COMMIT
postgres=# select pg_xact_commit_timestamp('34386830'::xid);
   pg_xact_commit_timestamp
-------------------------------
 2019-07-16 08:47:30.238746+00
(1 row)


>
> Is pg_xact_commit_timestamp suitable for this? I'm getting the impression that
> it isn't. But I don't understand quite how. And if it isn't suited to this
> purpose, does anyone know what pg_xact_commit_timestamp is for? What I'm after
> is something like a "xcommitserial" that increases reliably, and monotonically
> on transaction commit. That's how I'm hoping that pg_xact_commit_timestamp
> functions.

I don't think so. pg_xact_commit_timestamp returns the timestamp. If you want
some kind of ordering you have to fetch all commit timestamps (with their
respective xid) and order them.

You also can implement this tracking by yourself with triggers which insert a
row containing xid and timestamp in a tracking table. You can add an index on
timestamp column. With this approach you don't have to worry about vacuum freeze
which remove old timestamps. As you add more write, it could be more expensive
than track_commit_timestamp.

>
> Thanks also for making me understand that pg_xact_commit_timestamp applies to a
> *transaction*, not to each row. That makes it a lot lighter in the database. I
> was thinking 12 bytes+ per row, which is completely off for my case. (I tend to
> insert thousands of rows in a transaction.)
>
>> Yes timestamp are stored in pg_commit_ts directory. Old timestamp are removed
> after freeze has explained in
>> https://www.postgresql.org/docs/current/routine-vacuuming.html
>
> Thanks for the answer, and for kindly pointing me to the right section of the
> documentation. It's easy to get impatient with new(er) users. I'm _not_ lazy
> about reading manuls and researching but, well, the Postgres documentation is
> over 3,000 pages long when you download it. So, I may have missed a detail or
> two.... If I read that correctly, the ~4 billion number range is made into an
> endless circle by keeping ~2 billions numbers in the past, and 2 billion in the
> future. If that's right, I'm never going to be so out of data that the ~2
> billion number window is too small.
>

Yes it is a circular counter because xid are stored on 32 bits. However you have
to keep in mind that vacuum freeze old visible rows (default is 200 millions
transactions) and you lose timestamp information.

Sawada-san made a good presentation on freezing:
https://www.slideshare.net/masahikosawada98/introduction-vauum-freezing-xid-wraparound

You can also look at this website:
http://www.interdb.jp/pg/pgsql05.html#_5.1.
http://www.interdb.jp/pg/pgsql06.html#_6.3.

Regards,

--
Adrien