Re: Transaction atomicity

Il giorno mer, 07/03/2007 alle 08.12 -0800, Jeff Hubbach ha scritto:
> On 3/7/07 9:06 AM, "Giuseppe Sacco"
> <giuseppe@eppesuigoccas.homedns.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).
>
It works since I assigned ranges to each office. The query I wrote in my
original post was:

final String query = "INTO table (docId,seqNr) " +
                "VALUES (?, (SELECT 1 + coalesce (max(seqNr), 0) "+
                "FROM table " +
                "WHERE seqNr BETWEEN 0 AND (9223372036854775807-1) ))";

as you may see, I look for a MAX in a specific range. Every office has a
different range.
>
> 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.

You are right, this is a second option, but we cannot adopt it since we
have a lot of table that use foreign keys against this one. Adding one
field would require a change in every table in order to complete the
foreign key constraint.

Thanks for you hint,
Giuseppe