Hi Mike,
Ok, I agree. But, take a look:
=> insert into patr_local (local_cod,local_descr) values (3,'local A');
INSERT
=> insert into patr_local (local_descr) values ('local B');
INSERT
=> insert into patr_local (local_descr) values ('local C');
INSERT
=> insert into patr_local (local_descr) values ('local D');
ERROR: Duplicated key
=> insert into patr_local (local_descr) values ('local D');
select * from patr_local
3 - local A
1 - local B
2 - local C
4 - local D
As you can see, the tupple 'local D' was unable to be inserted because it
could have '3' as the value of local_cod, but '3' has already been used.
How can I solve it? It would have to add once more to get an empty
value, and once, and once, an once, until it gets an legal value.
Thanks in advance,
Marcelo Pereira
-- Remember that only God and Esc+:w saves.
__
(_.\ Marcelo Pereira |
/ / ___ |
/ (_/ _ \__ Matematica/99 - IMECC |
_______\____/_\___)___Unicamp_______________/
--- Mike Mascari, with his fast fingers, wrote:
:> 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
:>
