Re: perl/DBI transaction rollback of sequences

Hi Doug,

>I'm just trying to confirm the perl DBI transaction/rollback feature,
>which works as expected except for one thing, one field that is SERIAL
>keeps incrementing, i.e. the sequence doesn't get rolled back.  Does this
>mean I need to manually rollback (setval (current value-1))??
>
>I tried both automatically inserting into the table and manually getting
>the next value, but it doesn't matter.
>
>I'm using perl 5.6, DBI-1.20, DBD-Pg-1.01, and postgresql-7.1.3.

It doesn't get rolled back for a reason:

sequence starts at 4

User1 runs transaction, (marks 5 as being used)

user2 runs transaction, (marks 6 as being used)

user1 rolls back (so sequence goes back to 4), but user2 commits (so 6 is
being used)

user3 comes along and sequence updates to 5 again (which is fine)

then user4 comes along and sequence updates to 6.

STOP - trying to insert a value already used.

Hope that makes sense.

-----------------
      Chris Smith
http://www.squiz.net/