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