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

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...