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