[Doc?] Correct ordering of events / guarantees

[dnmss]

Hi,
in the docs and the video presentation you suggest to jump back through events (and then revert) via the pgh_id. However, I just spent several days trying to figure out how a) this guarantees that I get the right revision of data (never used django before either) or b) how this sort of log/versioning system should be implemented on postgres in general - without a good answer. So I would love your input here.

Additionally I think it would add a lot of value to add this to the docs to:

• show how your solution is better (you point out in your conference talk how other solutions have race conditions).
• make people aware of any limitations
• enable people to contribute or work around unsolved edge cases themselves
• just general knowledge transfer

[wesleykendall]

The docs go a long way in explaining how the underlying system works, limitations, performance tuning, etc. Similarly, people can create their own trackers, customize the event model, and much more.

When it comes to race conditions, any history tracking solution that operates in the application layer is subject to race conditions if it does not lock the row ahead of time. Triggers, on the other hand, snapshot the exact version of the row before the modification took place.

What specific suggestions for the docs do you have?

[dnmss]

I am only talking about the database side of things here.

The primary issue I am currently aware of is that just using an autoincrement primary key may not provide correct transaction ordering since sequences are not part of transactions, but I am unsure if this is even relevant here or what else I am missing. Currently the 'most correct' solution (besides locking) I am aware of strictly requires a transaction id in addition to a sequence number. I know this is sort of asking to be educational, but this seems to be something you found out before or during development anyway and could be pointed out very briefly - e.g. 'we ensure correct order by using X, it breaks if you mess with Y'.

The docs dont seem to cover these technical aspects beyond what overlaps with user features which is usually fine. However, with the wealth of 'odd' solutions and the complexity of the subject I find this to be a relevant aspect in evaluating any library in this space, since you cannot fix some of the potential problems retroactively.

One source of (some of this) info: https://mattjames.dev/auto-increment-ids-are-not-strictly-monotonic/

[wesleykendall]

Pghistory installs triggers, and you have the ability to configure snapshotting fields that are available in these triggers. For updates, you have atomic access to both the old and the new copy of the row before/after the update is applied.

Auto-incrementing primary keys seem unrelated here. If you need to enforce or guarantee some sort of strict ordering in your event logs because you have high parallelism, advisory locks seem like your way to go for pghistory or any other history tracking solution.