Thread: begin/end/abort work for sequences?
I assumed that wrapping a call to nextval() in a transaction would allow
me to roll back the update of the sequence. But when I do the following:
begin;
select nextval('po_seq');
abort;
The sequence po_seq is advancing to the next value even though the
transaction was aborted. If I put other things like updates, inserts, etc.
inside the transaction, they seem to be rolled back OK, but not the
update of the sequence.
Am I doing something wrong?
----------------------------------------------------
Kyle Bateman President, Action Target Inc.
"Viva Yo!" kyle@actarg.com (801)377-8033x101
----------------------------------------------------
Kyle ha scritto:
> I assumed that wrapping a call to nextval() in a transaction would allow
> me to roll back the update of the sequence. But when I do the following:
>
> begin;
> select nextval('po_seq');
> abort;
>
> The sequence po_seq is advancing to the next value even though the
> transaction was aborted. If I put other things like updates, inserts, etc.
> inside the transaction, they seem to be rolled back OK, but not the
> update of the sequence.
>
> Am I doing something wrong?
No. I can't explain you why but AFAIK sequences don't rollback.
______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'
> > The sequence po_seq is advancing to the next value even though the > > transaction was aborted. If I put other things like updates, inserts, etc. > > inside the transaction, they seem to be rolled back OK, but not the > > update of the sequence. > > > > Am I doing something wrong? > > No. I can't explain you why but AFAIK sequences don't rollback. If we rolled back sequence numbers, we would have to lock the table until the transaction commited or was rolled back. That is too much locking so was not worth it. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian wrote: <blockquote type="CITE">> > The sequence po_seq is advancing to the next value even though the<br />> > transaction was aborted. If I put other things like updates, inserts, etc. <br />> > inside thetransaction, they seem to be rolled back OK, but not the <br />> > update of the sequence. <br />> > <br />>> Am I doing something wrong? <br />> <br />> No. I can't explain you why but AFAIK sequences don't rollback.<p>If we rolled back sequence numbers, we would have to lock the table <br />until the transaction commited or wasrolled back. That is too much <br />locking so was not worth it. <br /> </blockquote><p><br />That seems like a prettybig thing to sacrifice. Did sequence locking work before 6.5? It was my impression that it did. <p>Do you know ifthere is a workaround? In my particular situation, it is critical that all instances of the sequence actually get used. If a transaction is aborted, I lose an instance and everything gets messed up. <pre>-- ---------------------------------------------------- Kyle Bateman President, Action Target Inc. "Viva Yo!" kyle@actarg.com (801)377-8033x101 ----------------------------------------------------</pre>
> > If we rolled back sequence numbers, we would have to lock the table > > until the transaction commited or was rolled back. That is too much > > locking so was not worth it. > > > > That seems like a pretty big thing to sacrifice. Did sequence locking work before > 6.5? It was my impression that it did. No, sequence numbers of aborted transactions never were saved. > Do you know if there is a workaround? In my particular situation, it is critical > that all instances of the sequence actually get used. If a transaction is > aborted, I lose an instance and everything gets messed up. I recommend hand-rolling a sequence number, similar to how the sequence mechanism works, but explicitly lock your sequence table, retrieve the value, and the commit/abort will unlock the table. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Thus spake Kyle Bateman
> Do you know if there is a workaround? In my particular situation, it is critical
> that all instances of the sequence actually get used. If a transaction is
> aborted, I lose an instance and everything gets messed up.
I had to solve this once in another RDBMS (Progress) and this is how I
did it. I had a table of available sequence numbers consisting of two
fields, a name and a number. The table was seeded with 10 entries for
each required sequence numbered 1 to 10. When I needed a sequence I
started a transaction and grabbed and locked the lowest numbered record
for that sequence that wasn't locked. I save the number then add 10 to
it. If I abort the transaction, the number remains unchanged. Under
this scheme 10 users can be working on the same sequence without locking
others out. Numbers can be used out of sequence from time to time if
someone aborts but they all get used eventually.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
"D'Arcy J.M. Cain" wrote: <blockquote type="CITE">Thus spake Kyle Bateman <br />> Do you know if there is a
workaround? In my particular situation, it is critical <br />> that all instances of the sequence actually get
used. If a transaction is <br />> aborted, I lose an instance and everything gets messed up. <p>I had to solve this
oncein another RDBMS (Progress) and this is how I <br />did it. I had a table of available sequence numbers consisting
oftwo <br />fields, a name and a number. The table was seeded with 10 entries for <br />each required sequence
numbered1 to 10. When I needed a sequence I <br />started a transaction and grabbed and locked the lowest numbered
record<br />for that sequence that wasn't locked. I save the number then add 10 to <br />it. If I abort the
transaction,the number remains unchanged. Under <br />this scheme 10 users can be working on the same sequence without
locking<br />others out. Numbers can be used out of sequence from time to time if <br />someone aborts but they all
getused eventually. <p>-- <br />D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves <br /><a
href="http://www.druid.net/darcy/">http://www.druid.net/darcy/</a> | and a sheep voting on <br />+1 416
4242871 (DoD#0082) (eNTP) | what's for dinner.</blockquote> Thanks for taking the time to reply! <pre>--
----------------------------------------------------
Kyle Bateman President, Action Target Inc.
"Viva Yo!" kyle@actarg.com (801)377-8033x101
----------------------------------------------------</pre>