Thread: Auto Increase
Hello All,
I am building a database and I am in troubles to get a field
autonumbering itself.
I have:
% -- Begin -----------------------------------------------
CREATE SEQUENCE patr_seq;
CREATE TABLE patr_local (
local_cod INTEGER default nextval('patr_seq')
local_descr CHAR(30) not null
);
% -- End --------------------------------------------------
And:
% -- Begin ------------------------------------------------
CREATE TABLE patr_local (
local_cod SERIAL,
local_descr CHAR(30) UNIQUE not null
);
% -- End --------------------------------------------------
In both cases, it runs properly, the field local_cod is automatically
written; in both case happens:
=> insert into patr_local (local_descr) values ('local 1');
INSERT
=> insert into patr_local (local_descr) values ('local 2');
INSERT
=> select * from patr_local;
1 - local 1
2 - local 2
BUT, if I try to insert a wrong tupple (duplicating the value of the field
local_descr, that is unique), the counter is also added +1, so the table
become:
=> insert into patr_local (local_descr) values ('local 1');
INSERT
=> insert into patr_local (local_descr) values ('local 1');
ERROR: duplicated key local_descr
=> insert into patr_local (local_descr) values ('local 2');
INSERT
=> select * from patr_local;
1 - local 1
3 - local 2
As you can see, the #2 wasn't used. So, as many errors I get while
inserting tupples in the table will be number of 'numbers' not used.
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.
Thanks in advance,
Best regards,
Marcelo Pereira
-- Remember that only God and Esc+:w saves.
__
(_.\ Marcelo Pereira |
/ / ___ |
/ (_/ _ \__ Matematica/99 - IMECC |
_______\____/_\___)___Unicamp_______________/
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
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
:>
--- fcanedo@hotpop.com, with his fast fingers, wrote:
:> Nice problem. I solve it by using a trigger that always overrides the
:> value of the id (in your case local_cod) with the value of
:> nextval('some_sequence'). It's also advisable to set the default for your
:> id to some bogus value like 1!!! This way you don't force people to set
:> the value in insert statements.
:>
:> Warning: untested example follows!
:> ======================================
:> CREATE FUNCTION some_trigger_function()
:> RETURNS OPAQUE
:> AS
:> '
:> BEGIN
:> NEW.id := nextval('some_sequence');
:>
:> RETURN NEW;
:> END;'
:> LANGUAGE 'plpgsql';
:> ======================================
Ok, but where (and how) do I have to use this function while creating the
database?
[]'s
Marcelo Pereira
-- Remember that only God and Esc+:w saves.
__
(_.\ Marcelo Pereira |
/ / ___ |
/ (_/ _ \__ Matematica/99 - IMECC |
_______\____/_\___)___Unicamp_______________/
:>
:>
:> On Thu, 24 Jan 2002, Marcelo Pereira wrote:
:>
:> > 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
:>
:>
:>
Marcelo Pereira wrote:
>
> 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.
You can either do that with a function, written in PL/PgSQL for example
(see the manual), but then the search for the next value can take an
arbitrary time, depending on how big the biggest value is.
Another variant would be to set the sequence to the value after a
successful insertion to local_cod, but I guess this has some problems
when multiple users are trying to do an insert.
Yet another method would be to ignore the value passed and always use
the next_val from the sequence, but then there's no elegant method to
see which value local_cod will have at the next insertion, which makes
it difficult to write client code that needs this value when it is used
for another table to refer to the new record for example.
In short, the easiest solution is probably to always use next_val
instead of a DEFAULT.