Skip site navigation (1) Skip section navigation (2)

Re: perl/DBI transaction rollback of sequences

From: Chris <csmith(at)squiz(dot)net>
To: Doug Silver <dsilver(at)quantified(dot)com>, pgsql-novice(at)postgresql(dot)org
Subject: Re: perl/DBI transaction rollback of sequences
Date: 2002-03-20 22:39:20
Message-ID: 5.1.0.14.0.20020321093145.00ae80b0@cooee.cybersydney.com.au (view raw or flat)
Thread:
Lists: pgsql-novice
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/


pgsql-novice by date

Next:From: Joshua b. JoreDate: 2002-03-20 23:07:51
Subject: Re: Approximate string matching?
Previous:From: Josh BerkusDate: 2002-03-20 22:35:46
Subject: Re: [SQL] few admin questions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group