If the underlying changeset is
commutative, then you get a CRDT.
> When processing an UPDATE change, the following conflicts may be detected:
> The target database may contain a row with the specified PRIMARY KEY values, but the current values of the fields that will be modified by the change may not match the original values stored within the changeset. This type of conflict is not detected when using a patchset.
Edit:
I wander if there is a way to add a last_mod column to the tables and so only a more recent UPDATE would be executed from a patch set? It would only work at the row level but for most cases that should be ok. You could always create other tables for more granular control.
It looks like you can provide a conflict hander too that can replace the update statement, that could be useful for merging conflicting changes.
This could be exactly what I want for something!
Clock skew is an insidious problem: things may look like they’re working, but they’re not. Given all of the clock bugs out there it’s sometimes not even safe to rely on the clock on a single machine. Across multiple machines it’s hopeless.
Please note that this doesn’t apply if you have a fancy time source like Google uses in their fancy cloud DB (whose name escapes me at the moment).
I’m looking for a system using SQLite that gives you the same eventual consistency guarantees as CouchDB/PouchDB on consumer devices. You need to be able to have nodes disconnect from each other and the internet for long periods of time before syncing. Hours or days not seconds or milliseconds.
If two nodes overwrite the same row while disconnected you need a deterministic way to decide which is the winner. This could be though a hash of the values in the row (lowest value wins) or using a timestamp. Pros and cons for each and it depends on use case. So for example if you are building a notebook application you almost certainly want the most recent update to win. The transaction rate would be low and devices with an internet connection will be “relatively” accurate on time.
Obviously this could be open to abuse, someone changing their clock to force their edit to win. But again pros and cons. The use case I’m looking for is within a trusted group where this wouldn’t be a problem.