Incrementing SERIAL

Hi, Does anybody know how NOT TO increment a SERIAL data type if an insert is rolled back or fails? Thanks, Don

It cannot be done. The reason is that even if you rollback your transaction you cannot know whether there were not other transactions that have incremented the serial value beyond even the aborted value you no longer need. There is no secure way to avoid holes in the serial numbers. The ONLY solution is to use a sequence table with a single row for each table that needs a sequence number and update the row as part of your transaction, then if you rollbback it rolls back as well. Of course you will completely blow concurrency and throughput by single threading all transactions through that single locked row. Art S. Kagel Don Ignacio wrote: > Hi, > > Does anybody know how NOT TO increment a SERIAL data type > if an insert is rolled back or fails? > > Thanks, > > Don