At 5:13 PM -0500 8/7/07, Scott Marlowe wrote:
On 8/7/07, Owen Hartnett <owen@xxxxxxxxxxxxxxxx> wrote:
Here's what I want to do:
Checkpoint the database in whatever way is appropriate.
Make copies of the database on several laptops for use in the field
(in automobiles) to do database changes. Record all the changes made
since the checkpoint as the user makes them.
Periodically take all the changes back into the office, take the
changes made out in the field and apply them to the main database.
Repeat the process.
Notes:
1) Unless an user makes a mistake, there should be no changes to the
same records by multiple users. (i.e. any concurrency violations
should be registered as an exception.)
2) I'd prefer it to just record the sql commands executed by the
database as text, then use psql < myFieldcommands to update the
database. This will also help me isolate any concurrency exceptions,
and I'd like to wrap the whole update in a transaction, so I can roll
the whole thing back if it does detect concurrency problems anywhere
in the process (then I can edit out the offending lines).
3) There's no particular rush to update the database - I don't need
this real-time.
4) Users might make their checkpoint at a different time from other users.
Given that each person is likely to only be only operating on their
own data set, I'd use an integer range for each person. Make an int
field in each table, and give each use a 1,000,000 id range to play
in, or something like that. You can even set it up so that the app
uses sequences and have them start at whatever the user's first id is,
and not cycling and stopping when it reaches the end to keep them from
bumping into the next person's range.
Heck, go with bigint and give each person a 1,000,000,000 range. Then
you could still handle 9,223,372,035 or so users before you'd run out
of sequences for each.
Heck, you could even write a system of update functions that checked
the userid against their numeric range and only updated the data if it
was in their range. Send it to a coworker for approval if it's not.
I'm having a few too mad scientist moments right about now. Got to
get back to my data mining project...
This would probably work, but it seems like overkill...I'll have to
think about it some more...
-Owen
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
