On PostgreSQL, two transactions applying different values for the same row at the same time can leave the lower col_version as the winner. Applying the same two payloads one after the other always converges on the higher one, so the outcome depends on concurrency.
Reproduction
Two payloads for the same row and column, from two different origin sites, built with cloudsync_payload_encode with explicit clocks: higher (col_version 3) and lower (col_version 2).
Serial, both orders, on a fresh target:
lower then higher -> value = higher
higher then lower -> value = higher
Concurrent (dblink; session x applies higher inside an open transaction, session y applies lower meanwhile, then x commits and y proceeds):
value = lower, col_version = 2 (y waited on the row lock: wait_event_type = Lock)
Cause
The merge reads the local clocks for the row and column, decides the incoming change wins, and then writes the winner clock with an unconditional upsert (SQL_CLOUDSYNC_INSERT_RETURN_CHANGE_ID in src/postgresql/sql_postgresql.c, via merge_set_winner_clock in src/cloudsync.c). Session y reads the clocks before x commits, so it sees no winner, decides lower wins, and then blocks on the base-row lock. When x commits, y proceeds on its stale decision and overwrites both the row and the clock.
Impact
Not specific to fragmented values: it happens with ordinary monolithic payloads, wherever concurrent transactions apply changes for the same row. The server applies each uploaded chunk as its own job, so this is reachable there. The result is a replica that disagrees with peers, which will not be repaired on its own, because the local clock now claims the lower version is the winner.
SQLite serializes writers, so this affects the PostgreSQL backend only.
Possible direction
Serialize the merge of one (table, primary key) on PostgreSQL before its clocks are read, for example with a transaction-level advisory lock on the row key, as database_fragment_lock already does per fragmented value. Any such lock has to be bounded: see the lock-table exhaustion fixed in 9d0abb3. Alternatively, make the winner-clock write conditional on the clock the decision was based on and retry when it no longer holds.
On PostgreSQL, two transactions applying different values for the same row at the same time can leave the lower
col_versionas the winner. Applying the same two payloads one after the other always converges on the higher one, so the outcome depends on concurrency.Reproduction
Two payloads for the same row and column, from two different origin sites, built with
cloudsync_payload_encodewith explicit clocks:higher(col_version3) andlower(col_version2).Serial, both orders, on a fresh target:
Concurrent (dblink; session x applies
higherinside an open transaction, session y applieslowermeanwhile, then x commits and y proceeds):Cause
The merge reads the local clocks for the row and column, decides the incoming change wins, and then writes the winner clock with an unconditional upsert (
SQL_CLOUDSYNC_INSERT_RETURN_CHANGE_IDinsrc/postgresql/sql_postgresql.c, viamerge_set_winner_clockinsrc/cloudsync.c). Session y reads the clocks before x commits, so it sees no winner, decideslowerwins, and then blocks on the base-row lock. When x commits, y proceeds on its stale decision and overwrites both the row and the clock.Impact
Not specific to fragmented values: it happens with ordinary monolithic payloads, wherever concurrent transactions apply changes for the same row. The server applies each uploaded chunk as its own job, so this is reachable there. The result is a replica that disagrees with peers, which will not be repaired on its own, because the local clock now claims the lower version is the winner.
SQLite serializes writers, so this affects the PostgreSQL backend only.
Possible direction
Serialize the merge of one
(table, primary key)on PostgreSQL before its clocks are read, for example with a transaction-level advisory lock on the row key, asdatabase_fragment_lockalready does per fragmented value. Any such lock has to be bounded: see the lock-table exhaustion fixed in9d0abb3. Alternatively, make the winner-clock write conditional on the clock the decision was based on and retry when it no longer holds.