Using pghistory for an optimistic concurrency check

[wutje]

I am looking for a solution that has both history tracking and concurrency detection (aka two different users editing the same table row).
My naive approach would be something like:

• on a get request, get latest history id and send it along
• on post/patch check that the received history id is still the latest history id. Using SQL to ensure atomic operation. If it fails send a http 409.

For my use case this setup seems to be sufficient. Basically doing what django-concurrency is doing, but by using pghistory tables instead of adding extra columns.

Am I forgetting something obvious? Is this something that would be good fit for pghistory?

[wesleykendall]

I'd argue for using pgtrigger for something like this. For example, here's a trigger example for versioning a model on any change. You can check if the version has been updated without writing snapshots to a totally new table.

If, of course, you're already writing snapshots on every change using pghistory, then you can also use the ID of the history row as a version. Are you wondering if this could go in the library? Seems better suited for a third-party tool

[wutje]

Thank you for your response.

I was not suggesting to put in this library, just checking this is a "valid" and sane use case.

[wesleykendall]

Ah I see, yea totally valid use case as long as you are tracking all changes