Re: Take your postgresSql on the road, and live to tell of it.

At 5:13 PM -0500 8/7/07, Scott Marlowe wrote:
>On 8/7/07, Owen Hartnett <owen(at)clipboardinc(dot)com> 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