I need to expose my data through Rest API for a mobile offline experience.
The idea is basically that a mobile client downloads all data to his device. after a week he wants to download only the changes into his device. each mobile client synced to a different point of time.
My problem is how to create a deterministic cursor for the client which they will send it on their next sync request and I'll be able to figure out the changes since their last sync.
- Clients tolerate data duplication.
- My database is insert-update only (never delete).
- Logical Decoding
It's relatively (to other solutions we have in mind) hard to create a plugin and it doesn't fit our needs because our permission model, each client needs to get only rows he is permitted to see.
- txid_current +
Save the transaction id on each row to create incremental value,
cminis needed to create a more precise cursor (many rows may have the same
txid, but only one row has a specific
xminonly, and re-sync (sync from scratch) user's upon wraparound. (according to the epoch counter).
SERIALcolumn/sequence, and bump it up manually or with triggers, it won't make us have to serialize our transaction if we are using Overlapping Sync as describe below.
- Serialize all transactions that act on the most top resource in my model (database), which is least preferred because of the performance and complexity it introduces to the application code.
Currently, I really want to use option 2, because it seems like the simplest solution.
How I'm going to handle
If the client syncs up to the latest visible (committed) change there may be active transactions that include earlier changes that are not yet visible.
Overlapping Sync the clients, Sync up to the latest visible change but keep track of what the latest safe change was. On the next sync, ask for changes since the lastest safe change rather than the latest visible change.
Is option 2 valid? (under the assumption above and the Overlapping Sync solution), if not, what is the next simplest solution? there is another solution which I didn't think about?