OOL - Optimistic object locking implementation

[pySilver]

Hi! I'm new to this lib and I'm wondering if that is possible to implement OOL with it? I saw Versioned model example, but thats not exactly that. What we need is to protect writes:

• without version
• where supplied version isn't the same as in db

[wesleykendall]

I imagine you could make a trigger that checks if the NEW.version is not equal to the OLD.version before incrementing it, failing otherwise. I'd leave it up to someone else to answer if this actually implements OOL though. If so, we could add it to the docs

[pySilver]

Typical OOL-protected model should follow the following logic:

1. On every update it should bump version field +1
2. It should prevent direct modifications of the version field
3. It should require version for every kind of updates no matter if it's partial or full object update it should require current version field value to be provided in payload
4. While version field value is provided in payload for write operation, it should be equal to version of the object in database we are about to modify and throw some VersionException otherwise

I see how 1 & 2 can be implemented but can't find solution for 3 & 4. Is that even possible?

[wesleykendall]

For 3) and 4), one pattern is the official interface pattern in pgtrigger. In other words, force every update in your app to go through the same interface(s) so that the version field is always provided.

Briefly, the official interface pattern is to block all updates on the table with protection triggers and decorator your official interfaces with @pgtrigger.ignore("protection_trigger_name").

Hope that helps. I'm happy to accept more examples in the docs for patterns like these. There's many useful patterns like these you can apply with both triggers and pgtrigger's ability to ignore execution of triggers from the application