Обсуждение: Am I using the SERIAL type properly?

I tried doing numerous searches to see if this has been discussed before,
but I couldn't find anything.  If it has, perhaps my question can be
answered simply by a pointer to a previous thread.

I'm trying to convert a program from mysql to postgres (yes, I've seen the
light).  I'm running into some problems though.  From everything I've
read, the SERIAL type is supposed to be like using an auto_increment in
mysql.  However, when I try to mix and match user assigned ids (my serial
column) with database generated ids things break.

I've created a simple example (a.sql) which shows the problem.

--------------------
drop table A;
create table A (
    id SERIAL PRIMARY KEY,
    foo int default 5,
    bar int default 10
);

insert into A (id, foo, bar) values (1, 1, 1);
insert into A (id, foo, bar) values (2, 2, 2);
insert into A (id, foo, bar) values (3, 3, 3);
insert into A (id, foo, bar) values (4, 4, 4);
insert into A (foo, bar) values (5, 5);
insert into A (foo, bar) values (6, 6);
--------------------------

The output that I get is:

[ctindel@ct742301 Setup]$ p < a.sql
DROP TABLE
NOTICE:  CREATE TABLE will create implicit sequence 'a_id_seq' for SERIAL
column 'a.id'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'a_pkey'
for table 'a'
CREATE TABLE
INSERT 44289 1
INSERT 44290 1
INSERT 44291 1
INSERT 44292 1
ERROR:  Cannot insert a duplicate key into unique index a_pkey
ERROR:  Cannot insert a duplicate key into unique index a_pkey
--------------------------

However, if I add some debugging statements to see what I'm doing wrong,
the problem seems to go away (sort of).
----------------------
drop table A;
create table A (
    id SERIAL PRIMARY KEY,
    foo int default 5,
    bar int default 10
);

insert into A (id, foo, bar) values (1, 1, 1);
Select * from A;
Select nextval('a_id_seq');

insert into A (id, foo, bar) values (2, 2, 2);
Select * from A;
Select nextval('a_id_seq');

insert into A (id, foo, bar) values (3, 3, 3);
Select * from A;
Select nextval('a_id_seq');

insert into A (id, foo, bar) values (4, 4, 4);
Select * from A;
Select nextval('a_id_seq');
insert into A (foo, bar) values (5, 5);
Select * from A;
Select nextval('a_id_seq');

insert into A (foo, bar) values (6, 6);
Select * from A;
Select nextval('a_id_seq');
-----------------------------

Then the output is:

[ctindel@ct742301 Setup]$ p < a.sql
DROP TABLE
NOTICE:  CREATE TABLE will create implicit sequence 'a_id_seq' for SERIAL
column 'a.id'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'a_pkey'
for table 'a'
CREATE TABLE
INSERT 44319 1
 id | foo | bar
----+-----+-----
  1 |   1 |   1
(1 row)

 nextval
---------
       1
INSERT 44320 1
 id | foo | bar
----+-----+-----
  1 |   1 |   1
  2 |   2 |   2
(2 rows)

 nextval
---------
       2
(1 row)

INSERT 44321 1
 id | foo | bar
----+-----+-----
  1 |   1 |   1
  2 |   2 |   2
  3 |   3 |   3
(3 rows)

 nextval
---------
       3
(1 row)

INSERT 44322 1
 id | foo | bar
----+-----+-----
  1 |   1 |   1
  2 |   2 |   2
  3 |   3 |   3
  4 |   4 |   4
(4 rows)

 nextval
---------
       4
(1 row)

INSERT 44323 1
 id | foo | bar
----+-----+-----
  1 |   1 |   1
  2 |   2 |   2
  3 |   3 |   3
  4 |   4 |   4
  5 |   5 |   5
(5 rows)

 nextval
---------
       6
(1 row)

INSERT 44324 1
 id | foo | bar
----+-----+-----
  1 |   1 |   1
  2 |   2 |   2
  3 |   3 |   3
  4 |   4 |   4
  5 |   5 |   5
  7 |   6 |   6
(6 rows)

 nextval
---------
       8
(1 row)
---------------------------------------

So, no more errors from the database, but it seems to skip the "6" id
completely.  Can anybody explain what I'm seeing?  This is with postgresql
7.3.3.

Thanks very much in advance!

Chad

On Thu, Jul 10, 2003 at 12:10:31AM -0700, Chad N. Tindel wrote:
> I tried doing numerous searches to see if this has been discussed before,
> but I couldn't find anything.  If it has, perhaps my question can be
> answered simply by a pointer to a previous thread.
>
> I'm trying to convert a program from mysql to postgres (yes, I've seen the
> light).  I'm running into some problems though.  From everything I've
> read, the SERIAL type is supposed to be like using an auto_increment in
> mysql.  However, when I try to mix and match user assigned ids (my serial
> column) with database generated ids things break.

The serial fields is only a default so the counter is only advanced when you
don't specify a specific value in the insert. So in your first example, the
first four insert did not move the serial counter from 1 so the last tried
to insert keys 1 and 2 which failed because it's a primary key.

You second example works because you're explicitly calling nextval(). The
insert calls it too so you skip values.

Solutions are:
- Don't include the serial field in the insert statement
- Specify the value DEFAULT instead of an actual value in the inserts
- Specify nextval() explicitly in your inserts
- Create a trigger so it overrides any value you put in with the next serial

Note, you can't really avoid holes in the sequence. This is a FAQ somewhere.

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
>   - Samuel P. Huntington

> --------------------
> drop table A;
> create table A (
>     id SERIAL PRIMARY KEY,
>     foo int default 5,
>     bar int default 10
> );
>
> insert into A (id, foo, bar) values (1, 1, 1);
> insert into A (id, foo, bar) values (2, 2, 2);
> insert into A (id, foo, bar) values (3, 3, 3);
> insert into A (id, foo, bar) values (4, 4, 4);
   A serial data type will allow you to input values into it, but the
counter is still at 0.  That's why your first update statement's
nextval outputs "1".  It's not showing what was already inserted, it's
showing what would have been.  So at this point you need to set the
current value of id at 4.

> insert into A (foo, bar) values (5, 5);
> insert into A (foo, bar) values (6, 6);
> --------------------------
>
> The output that I get is:
>
> [ctindel@ct742301 Setup]$ p < a.sql
> DROP TABLE
> NOTICE:  CREATE TABLE will create implicit sequence 'a_id_seq' for
> SERIAL
> column 'a.id'
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'a_pkey'
> for table 'a'
> CREATE TABLE
> INSERT 44289 1
> INSERT 44290 1
> INSERT 44291 1
> INSERT 44292 1
> ERROR:  Cannot insert a duplicate key into unique index a_pkey
Here it's trying to insert "1"
> ERROR:  Cannot insert a duplicate key into unique index a_pkey
Here "2"

Nextval *sets* the sequence at N+1.

> INSERT 44319 1
>  id | foo | bar
> ----+-----+-----
>   1 |   1 |   1
> (1 row)
>
>  nextval
> ---------
>        1
   === These are unrelated.
>

Nextval is at 4 now, so the insert (foo,bar) works correctly.
>  id | foo | bar
> ----+-----+-----
>   1 |   1 |   1
>   2 |   2 |   2
>   3 |   3 |   3
>   4 |   4 |   4
>   5 |   5 |   5
> (5 rows)
>
>  nextval
> ---------
>        6
   < == This increases it again, showing the skipping behavior you were
seeing.

If you use a serial datatype, it simply says "the *default* behavior is
to use the next value of sequence a".  So you can insert 1, 2,
10,204,492 into the column just fine.  However, by inserting data into
something you want to auto-increment, you can run into non-uniqueness.
(Like you were).   So most people tend to leave the id field as
completely null.  Setting the value is only for loading data, or data
that already has something as a PK.

The command to set it is "select pg_catalog.setval('sequence_name',
value)".  Check the docs on sequences for more info.
On Saturday, July 12, 2003, at 02:36  AM, Chad N. Tindel wrote:

>>> --------------------
>>> drop table A;
>>> create table A (
>>>    id SERIAL PRIMARY KEY,
>>>    foo int default 5,
>>>    bar int default 10
>>> );
>>>
>>> insert into A (id, foo, bar) values (1, 1, 1);
>>> insert into A (id, foo, bar) values (2, 2, 2);
>>> insert into A (id, foo, bar) values (3, 3, 3);
>>> insert into A (id, foo, bar) values (4, 4, 4);
>>   A serial data type will allow you to input values into it, but the
>> counter is still at 0.  That's why your first update statement's
>> nextval outputs "1".  It's not showing what was already inserted, it's
>> showing what would have been.  So at this point you need to set the
>> current value of id at 4.
>
> I wasn't quite able to figure out what the solution to my problem
> should be...
> Are you saying that a serial type isn't smart enough to realize that I
> just
> inserted the values 1, 2, 3, 4, and to figure out that if I ask it to
> self-generate the next one that it should return a 5?  I have to tell
> it that
> the next value it should return is 5?
>
> Maybe I'm just using the wrong feature of postgres... is there some
> way to
> get the same behavior as a mysql auto_increment primary key column?
>
>
>> Nextval *sets* the sequence at N+1.
>
> OK... didn't know that.  How do I set the sequence to an arbitrary
> value X?
>
> Thanks for the help!  Hopefully I'll get the hang of this soon.
>
> Chad
>