Skip site navigation (1) Skip section navigation (2)

Re: Transaction atomicity

From: Giuseppe Sacco <giuseppe(at)eppesuigoccas(dot)homedns(dot)org>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Transaction atomicity
Date: 2007-03-07 16:39:00
Message-ID: 1173285540.20645.59.camel@scarafaggio (view raw or flat)
Thread:
Lists: pgsql-jdbc
Il giorno mer, 07/03/2007 alle 08.12 -0800, Jeff Hubbach ha scritto:
> 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).
> 
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

In response to

pgsql-jdbc by date

Next:From: Giuseppe SaccoDate: 2007-03-07 17:25:05
Subject: Re: Transaction atomicity
Previous:From: Heikki LinnakangasDate: 2007-03-07 16:36:44
Subject: Re: Transaction atomicity

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group