Re: Transaction atomicity

On 3/7/07 9:06 AM, "Giuseppe Sacco" <giuseppe(at)eppesuigoccas(dot)homedns(dot)org>
wrote:

> You are right, but I need different sequences for every user, i.e., if
> two users insert on the same table then I need a way to use different
> sequence. The reason of this is that I have to split my application into
> a few different postgresql instances based in different offices. Every
> night all instances synchronise their data (this is and INSERT only
> table), so I need a different table sequence in every office (or group
> of users or single user).

Using select(max(id)) won't work in this case, either (if I'm understanding
your setup correctly).

So each office has its own postgresql instance. Then every night you copy
the data from all offices to all other offices. Therefore, that whole day,
inserts were made in each office instance with the same IDs, which will
cause duplicate key exceptions.

Why not have a compound key on this table, with an ID generated by a
sequence (one sequence, named the same, for each instance of PostgreSQL for
each office), and an Office ID that is static for each instance? Then the
merge/sync would go through without a hitch.