RE: sequences and Transactions

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

>> -----Original Message-----
>> From: Elmar(dot)Haneke(at)gmx(dot)de [mailto:Elmar(dot)Haneke(at)gmx(dot)de]
>> Sent: 27 April 2000 09:39
>> To: pgsql-general(at)postgresql(dot)org
>> Subject: [GENERAL] sequences and Transactions
>>
>>
>>
>>
>> > ----------
>> > From: Elmar(dot)Haneke(at)gmx(dot)de[SMTP:ELMAR(dot)HANEKE(at)GMX(dot)DE]
>> > Sent: Thursday, April 27, 2000 10:38:55 AM
>> > To: pgsql-general(at)postgresql(dot)org
>> > Subject: [GENERAL] sequences and Transactions
>> > Auto forwarded by a Rule
>> >
>> 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.
>>
>> Elmar
>>