Обсуждение: SERIAL behaviour

Hi everyone,

I am new to PostgreSQL and I am using it in PHP application. For the most
part, I like it a lot. However, I am quite dismayed by the behavior of the
SERIAL type, or, more specifically, the PostgreSQL sequences.

I have three tables where table1 has its primary key of type SERIAL. Two
other tables use that same id (of type integer) as a foreign key (and also
part of the compound primary key). When inserting a new record, I have the
code that essentially looks like this:

BEGIN
id = nextval('sequence');
insert_in_table1(id, data1);
insert_in_table2(id, data2);
insert_in_table3(id, data3);

if( success ) {
    COMMIT
} else {
    ROLLBACK
}

The problem is that on a rollback, the value of the sequence does not go
back to its previous value! I know that I can use setval() to adjust the
value of the sequence, but that's not a solution I like. I expect ROLLBACK
to bring the database to the state it was before BEGIN, but that's not what
happens! What is the proper way to handle this? Also, how can I ensure
consistency when multiple clients are changing the same tables? (sequence
doesn't seem to be part of the transaction...)

thanks in advance,

Eugene

-------------------------------------------------------------------
eestrulyov at uwaterloo dot ca

i use php and postgresql a lot.

the behaviour of the sequence is ok as they are there to generate sequences
of values mainly to be used as keys.

point 1.

it's not really reasonable to expect a sequence to rollback as in a multi
user system it would be practicly impossible to 'track' all users and
sessions and transactions etc so that the values were consecutive or indeed
in a consistent state after a rollback as you mention.

the database itself will be in a consistent state when you rollback a
transaction and if fresh values are generated for your sequence then i don't
understand what the problem would be.

point 2.

as far as i am aware sequence values are safe within transactions. the app i
developed at work hold calendar information, bookings, appointments, reports
etc for 500 people and has been running for 18 motnhs with no problems. it
uses sequence values for all appointment inserts (over 100k so far) and i
have never had a problem with a duplicate value being given to 2 users. i
think i read somewhere that sequences are safe for multiple users but can't
remember where ...

i would suggest that the proper way to handle it is to not bother worrying.

Steve

> -----Original Message-----
> From: Eugene [mailto:foo@bar.com]
> Sent: 14 July 2002 02:59
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] SERIAL behaviour
>
>
> Hi everyone,
>
> I am new to PostgreSQL and I am using it in PHP application.
> For the most
> part, I like it a lot. However, I am quite dismayed by the
> behavior of the
> SERIAL type, or, more specifically, the PostgreSQL sequences.
>
> I have three tables where table1 has its primary key of type
> SERIAL. Two
> other tables use that same id (of type integer) as a foreign
> key (and also
> part of the compound primary key). When inserting a new
> record, I have the
> code that essentially looks like this:
>
> BEGIN
> id = nextval('sequence');
> insert_in_table1(id, data1);
> insert_in_table2(id, data2);
> insert_in_table3(id, data3);
>
> if( success ) {
>     COMMIT
> } else {
>     ROLLBACK
> }
>
> The problem is that on a rollback, the value of the sequence
> does not go
> back to its previous value! I know that I can use setval() to
> adjust the
> value of the sequence, but that's not a solution I like. I
> expect ROLLBACK
> to bring the database to the state it was before BEGIN, but
> that's not what
> happens! What is the proper way to handle this? Also, how can
> I ensure
> consistency when multiple clients are changing the same
> tables? (sequence
> doesn't seem to be part of the transaction...)
>
> thanks in advance,
>
> Eugene
>
> -------------------------------------------------------------------
> eestrulyov at uwaterloo dot ca
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>

> However, I am quite dismayed by the behavior of the
> SERIAL type, or, more specifically, the PostgreSQL sequences.
>
> The problem is that on a rollback, the value of the sequence does not go
> back to its previous value!
>
If I understand SEQUENCEs right, that should be the expected behaviour:
Sequences generate values that are unique both in space (ie. other users/transactions)
and time. Thus they must not be rescrewed in case of ROLLBACK.

> Also, how can I ensure
> consistency when multiple clients are changing the same tables? (sequence
> doesn't seem to be part of the transaction...)
>
That sequences are not "part of the transaction" actually guarantees
consistency among multiple clients.

Christoph Dalitz

On Sat, 13 Jul 2002, Eugene wrote:

> The problem is that on a rollback, the value of the sequence does not go
> back to its previous value!

And this is a big efficiency win. Say you get sequence number 100.
You start your work, and while you're chugging along, three other
transactions also start, and also request sequence numbers, getting
101, 102 and 103. If you then rollback your transaction, you can't put
the sequence number back to 100 unless you force the following three
transactions also to abort.

Alternatively, you could lock the sequence while the transaction is
running, but then numbers 101, 102 and 103 would have to wait for your
number 100 transaction to commit or roll back before they could even
start to work, thus slowing down the system.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

On Sun, 2002-07-14 at 03:59, Eugene wrote:
[ SERIAL type / SEQUENCEs and transactions ]

Closely related: what's the official/preferred way if I'm inserting rows
into a table with a auto assigned id (SERIAL), to get the newly created
id? asking the sequence obviously will not work - as the sequence will
advance when the next value is inserted from another transaction.

cheers
-- vbi

--
secure email with gpg                         http://fortytwo.ch/gpg

you can use currval() to get the current value of a sequence.

however if your key value is set to serial and it's default is the nextval
then don't reference it in your insert i.e.

insert into foo(forename,surname) values ('Steve','Brett');

where the key for foo is an id of type serial.

currval() 'should' be safe for use within transactions.

Steve

> -----Original Message-----
> From: Adrian 'Dagurashibanipal' von Bidder
> [mailto:avbidder@fortytwo.ch]
> Sent: 15 July 2002 14:47
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] SERIAL behaviour
>
>
> On Sun, 2002-07-14 at 03:59, Eugene wrote:
> [ SERIAL type / SEQUENCEs and transactions ]
>
> Closely related: what's the official/preferred way if I'm
> inserting rows
> into a table with a auto assigned id (SERIAL), to get the
> newly created
> id? asking the sequence obviously will not work - as the sequence will
> advance when the next value is inserted from another transaction.
>
> cheers
> -- vbi
>
> --
> secure email with gpg                         http://fortytwo.ch/gpg
>

On Mon, Jul 15, 2002 at 03:47:28PM +0200, Adrian 'Dagurashibanipal' von Bidder wrote:
> On Sun, 2002-07-14 at 03:59, Eugene wrote:
> [ SERIAL type / SEQUENCEs and transactions ]
>
> Closely related: what's the official/preferred way if I'm inserting rows
> into a table with a auto assigned id (SERIAL), to get the newly created
> id? asking the sequence obviously will not work - as the sequence will
> advance when the next value is inserted from another transaction.

You read the documentation and notice that currval() is not affected by
nextval()s executed upon other connections. In other words, it's a complete
non-issue and works exectly the way that is useful.
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

On Mon, 2002-07-15 at 16:07, Martijn van Oosterhout wrote:
> On Mon, Jul 15, 2002 at 03:47:28PM +0200, Adrian 'Dagurashibanipal' von Bidder wrote:
> > On Sun, 2002-07-14 at 03:59, Eugene wrote:
> > [ SERIAL type / SEQUENCEs and transactions ]
> >
> > Closely related: what's the official/preferred way if I'm inserting rows
> > into a table with a auto assigned id (SERIAL), to get the newly created
> > id? asking the sequence obviously will not work - as the sequence will
> > advance when the next value is inserted from another transaction.
>
> You read the documentation and notice that currval() is not affected by
> nextval()s executed upon other connections. In other words, it's a complete
> non-issue and works exectly the way that is useful.

Ok, thanks (and sorry for not rtfm in this case). Unexpected, but, yes,
useful.

cheers
-- vbi

--
secure email with gpg                         http://fortytwo.ch/gpg