Marcelo Pereira wrote:
>
> Hello All,
>
> I am building a database and I am in troubles to get a field
> autonumbering itself.
...
> How can I create a field that avoid this action? I would like the field
> local_cod had the sequence:
>
> 1,2,3,4,5,6,7,8,9,10,...,N,...
>
> ...whatever happens while inserting tupples.
You can't. When transactions are rolled back, sequence values are not
reset. Imagine the following scenario:
Transaction #1
BEGIN;
SELECT nextval('seq'); <= 1
Transaction #2
BEGIN;
SELECT nextval('seq'); <= 2
Transaction #1
ABORT;
BEGIN;
SELECT nextval('seq'); ???
Just because Transaction #1 aborted doesn't mean the sequence value can
be reset. Sequences only guarantee you'll get a number bigger than
before, not that there won't be holes. I am unaware of a database where
this is not true (at least its not true of Oracle). The only way to what
you want is use a 1-row table and a SELECT FOR UPDATE, but that single
row will become a serious bottleneck for you as the number of
simultaneous sessions grows.
Hope that helps,
Mike Mascari
mascarm@mascari.com