Postgres Pro
Downloads
Home   >   mailing lists

Thread: sequences and Transactions

sequences and Transactions

From
Elmar.Haneke@gmx.de
Date:
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

RE: sequences and Transactions

From
Michael Ansley
Date:

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.Haneke@gmx.de [mailto:Elmar.Haneke@gmx.de]
>>   Sent: 27 April 2000 09:39
>>   To: pgsql-general@postgresql.org
>>   Subject: [GENERAL] sequences and Transactions
>>  
>>  
>>  
>>  
>>   > ----------
>>   > From:    Elmar.Haneke@gmx.de[SMTP:ELMAR.HANEKE@GMX.DE]
>>   > Sent:    Thursday, April 27, 2000 10:38:55 AM
>>   > To:      pgsql-general@postgresql.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
>>  

Re: sequences and Transactions

From
Bruce Momjian
Date:
> 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