Adrien, thanks a lot for taking the time to try and explain all of these details to me. I'm looking at incremental rollups, and thinking through various alternative designs. It sounds like pg_xact_commit_timestamp just isn't the right tool for my purposes, so I'll go in another direction.
All the same, I've learned a _lot_ of important points about Postgres from trying to sort all of this out. Your messages have been a real help. On Tue, Jul 16, 2019 at 7:03 PM Adrien Nayrat <adrien.nay...@anayrat.info> wrote: > 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 > >
