Dear Gurus,
I need some solution for a transaction-based system that may have
offline clients. Something like CVS in version management, but
SQL-based.
I need this because we have a team of 6 people, concurrently updating
shared XML files and such. When I leave the corporate network I can
keep updating corporate data, along with my colleagues. When I join
the corporate network again the data will then be published to a
common place.
I thought about PostgreSQL for several reasons. First of all,
concurrent writes while online will not cause confusion.
What I need:
- If client is online the data should be stored in master DB with data integrity
- if client is offline the data should be stored locally
- If data is stored locally and client goes online data should be
synced to master.
- Way of handling conflicting records
e.g. client A goes offline, client B modifies master, client A
modifies locally, and when goes online again cannot commit the
changes.
i.e. I need to insert/update (maybe not delete) records even at times
when I cannot access the DB (of course, will be committed later).
What I DON'T need:
- No need for performance (no massive data)
- No need for automatic sync (client-initiated sync is sufficient;
automatic is a bonus though)
- No need to be generic, just some tables should be monitored. Even if
I have to write triggers for each table it's OK.
- Don't even need master, if it can be solved without master.
Crudely speaking I can imagine it something like:
- when client goes offline a "snapshot" is present locally, just like
at the beginning of a transaction
- when client goes online all the changes are "committed" to the
master db, just like at end of transaction.
The difference is that I do not know when will the client go offline,
even client does not know (e.g. ISP goes down) and, of course, there
may be a lot of connections while client is offline.
Inserting is piece of cake but updates give me a headache.
Is there some addon, contrib, whatever for PostgreSQL that can be used
for such purpose?
Is there some guide how to implement such a system? (my first guess is
helper tables, similar in redo logs, but only for specific changes in
the database)
--
G.