Initiating a sync from an instance using postgres fails due to transactional issues

[bjester]

Background

We recently made fixes to Morango that correct the database transaction handling, which was meant to perform certain operations within a transaction with higher transaction isolation when using postgres. Due to connection mismanagement, the operations weren't actually executing in a transaction.

Observed behavior

The sync command initiates a sync as a client instance, and wraps certain stages with a db_lock context manager. When Kolibri is configured to use postgres, the db_lock context manager opens a transaction and executes an advisory lock. Since the sync command is wrapping several stages (initialization, serializing, and queuing), it conflicts with morango's internal management in a way that causes unexpected behavior, such as being unable to read records written during the outer transaction.

Expected behavior

The motivation for wrapping parts of the sync with a transaction and advisory lock have likely been corrected and handled by the recent transactional fixes made within morango. In addition, if conflicting changes occur during a transaction within morango (one using isolation), then it's likely that a transaction rollback error will be raised from a serializable error, and morango will retry.

User-facing consequences

The effects of this issue can be very confusing, and were very difficult to debug.

Steps to reproduce

1. Set up a kolibri instance with postgres
2. Initiate a sync from the postgres instance
3. Observe the majority of the sync appears to complete successfully, but errors in the final steps of the sync command
4. Observe the morango_store has 0 records, but records exist in the buffer

Context

Kolibri 0.14.8-alpha, 0.15.x, 0.16.x
Morango 0.6.12+
Postgresql

[marcellamaki]

Fixed with #9556