Re: Transaction atomicity

Hi Heikki,

Il giorno mer, 07/03/2007 alle 15.50 +0000, Heikki Linnakangas ha
scritto:
> Giuseppe Sacco wrote:
> > Thanks for this suggestion. I have been evaluating the use of sequences
> > in my application, but I was looking for a more portable code (this code
> > have to run on postgresq 8.1, mysql 5, oracle 9.2 and sql-server 2005).
> > As you may know postgresql nextval syntax isn't as required by the
> > standard; and other vendors use different syntax too. Moreover it would
> > make my code really complex since I would need a new sequence for any
> > connected user.
>
> A more portable way to use sequences is to define a default expression
> on the column when you create the table, like this:
>
> CREATE TABLE foo (
> key int DEFAULT (nextval('foo_seq')) PRIMARY KEY
> data ...
> )
[...]

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 sequences I would probably need to create all these sequences,
then use the right sequence to get the nextval and then call my INSERT
statement with the right value. This imply the explicitly use of a
sequence for every insert. This is not a big problem, I just need to
have a special case for every dbms.

Thanks again,
Giuseppe