Re: SERIAL behaviour - Mailing list pgsql-general

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)
>