sequences and Transactions

Hi!

how can I setup sequences to have the current-value reset in case
of an Transaction rollback.

My intension is to get an contignous numbering of the rows.
Currently in case of an Rollback one number is skipped since
the record itself is not inserted but the counter is not reset.

People have asked this before. If we did that rollback, we would have
to lock the sequence value for the duration of the transaction, which is
not good. No real way to do this, sorry.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

You can't. Sequences are not designed for continuity, they are designed for
uniqueness. If you want to have a set of contiguous numbers, in ascending
order, then you will probably have to write a trigger to insert the next
value, which it has to scan the table to work out. And you have to decide
what to do in case of deletions: do you reuse the number on the next insert
(add complexity and run-time to the code), or just carry on anyway, meaning
that you have holes in your sequence, in which case, you could have used a
sequence anyway, probably. Depending on the number of expected rows in the
table, you may find that the time to insert doesn't justify having
contiguous numbers. For each insert, the minimum you are going to get away
with is a full table scan.

MikeA