Out of curiosity, how do transaction logs handle things like created_at fields, or randomly generated UUID, which rely on contextual data? Is the server time/rng seed faked for each replayed transaction?
The transaction log is everything that gets done, not how it is done, so it can be replayed reliably. How values that are random, time sensitive, or otherwise arbitrary, are derived during a transaction is not important. What is logged is the fact that the values x/y/z were recorded in row 123,456 which is in page 987,654,321¹ which means that when the log is replayed you end up with exactly the same state the original database was in at the point the log is run to.
[1] in fact it could just be logged at the page level, the granularity of the log structure will vary between systems, if logged at the row level it may be the case that the physical datafile after restore is not exactly the same but the data will still be “random” values & all.
In most cases, transaction logs/write-ahead logs will contain the return value of non-determistic functions like created_at or random uuid, instead of the function call.
dspillett|4 years ago
[1] in fact it could just be logged at the page level, the granularity of the log structure will vary between systems, if logged at the row level it may be the case that the physical datafile after restore is not exactly the same but the data will still be “random” values & all.
dikei|4 years ago