Thread: AutoIncrement not working on this table only

When I try to create a new row in this table and do not explicitly define a unique value for datadefindex I get the
followingerror message: 

 ERROR:  Cannot insert a duplicate key into unique index datadef_pkey

 Here is the INSERT statement that generated the error:

 tmdb=# insert into datadef (cfgmgmtid, datadefname, datadefformat,
datadefunits, datadefdescription)
 VALUES (2, 'TestValue', 'REAL', 'N/A', 'This is a test of placing a new
row without an explicit datadefindex');

 Here is the table definition:

 tmdb=# \d datadef
                                              Table "datadef"
        Column       |         Type          |
Modifiers


--------------------+-----------------------+---------------------------
--------
 -------------------------
  datadefindex       | integer               | not null default
nextval('datadef_
 datadefindex_seq'::text)
  cfgmgmtid          | integer               |
  datadefname        | character varying(80) | not null
  datadefformat      | character varying(80) | not null
  datadefunits       | character varying(80) | not null
  datadefdescription | text                  | not null
 Primary key: datadef_pkey
 Unique keys: datadefname_idx
 Triggers: RI_ConstraintTrigger_19507,
           RI_ConstraintTrigger_19509,
           RI_ConstraintTrigger_19511,
           RI_ConstraintTrigger_19513,
           RI_ConstraintTrigger_19515,
           RI_ConstraintTrigger_19659,
           RI_ConstraintTrigger_19661,
           RI_ConstraintTrigger_19663,
           RI_ConstraintTrigger_19665,
           RI_ConstraintTrigger_19667

 And finally here is the entry in the datadef_datadefindex_seq table:

 tmdb=# select * from datadef_datadefindex_seq;
       sequence_name       | last_value | increment_by | max_value  |
min_value |
  cache_value | log_cnt | is_cycled | is_called

--------------------------+------------+--------------+------------+----
-------+
 -------------+---------+-----------+-----------
  datadef_datadefindex_seq |          8 |            1 | 2147483647 |
1 |
            1 |      32 | f         | t
 (1 row)

 Notice that last_value = 8, owever the current number of rows in the
datadef table = 67.

 My current workaround is to do a MAX(datadefindex) on datadef, increment it by one and explicitly place that  value as
thedatradefindex for the new row, however I am worried about the database stability. 

 More information : If I DROP the database, recreate it, and enter
values into the table manually, the autoincrement works on this table. It seems that the problem arises after I reload
thedata into the table using the \i command on a file which was created using the pg_dump command. 

 I have recently upgraded from 7.2.1 to 7.2.3 using the RPM.

 Thanks in advance,

 ben

Kassel Ben CRBE wrote:
> When I try to create a new row in this table and do not
> explicitly define a unique value for datadefindex I get the following
> error message:
>
>  ERROR:  Cannot insert a duplicate key into unique index datadef_pkey
>
>  Here is the INSERT statement that generated the error:
>
>  tmdb=# insert into datadef (cfgmgmtid, datadefname, datadefformat,
> datadefunits, datadefdescription)
>  VALUES (2, 'TestValue', 'REAL', 'N/A', 'This is a test of placing a
> new row without an explicit datadefindex');
>
>  Here is the table definition:
>
>  tmdb=# \d datadef
>                                               Table "datadef"
>         Column       |         Type          |
> Modifiers
>
>
> --------------------+-----------------------+-----------------
> ----------
> --------
>  -------------------------
>   datadefindex       | integer               | not null default
>  nextval('datadef_ datadefindex_seq'::text)
>   cfgmgmtid          | integer               |
>   datadefname        | character varying(80) | not null
>   datadefformat      | character varying(80) | not null
>   datadefunits       | character varying(80) | not null
>   datadefdescription | text                  | not null  Primary key:
>  datadef_pkey Unique keys: datadefname_idx
>  Triggers: RI_ConstraintTrigger_19507,
>            RI_ConstraintTrigger_19509,
>            RI_ConstraintTrigger_19511,
>            RI_ConstraintTrigger_19513,
>            RI_ConstraintTrigger_19515,
>            RI_ConstraintTrigger_19659,
>            RI_ConstraintTrigger_19661,
>            RI_ConstraintTrigger_19663,
>            RI_ConstraintTrigger_19665,
>            RI_ConstraintTrigger_19667
>
>  And finally here is the entry in the datadef_datadefindex_seq table:
>
>  tmdb=# select * from datadef_datadefindex_seq;
>        sequence_name       | last_value | increment_by | max_value  |
>   min_value | cache_value | log_cnt | is_cycled | is_called
>
> --------------------------+------------+--------------+-------
> -----+----
> -------+
>  -------------+---------+-----------+-----------
>   datadef_datadefindex_seq |          8 |            1 | 2147483647 |
>             1 | 1 |      32 | f         | t
>  (1 row)
>
>  Notice that last_value = 8, owever the current number of rows in the
> datadef table = 67.
>
>  My current workaround is to do a MAX(datadefindex) on
> datadef, increment it by one and explicitly place that  value
> as the datradefindex for the new row, however I am worried about the
> database stability.
>
>  More information : If I DROP the database, recreate it, and enter
> values into the table manually, the autoincrement works on
> this table. It seems that the problem arises after I reload
> the data into the table using the \i command on a file which
> was created using the pg_dump command.
>
>  I have recently upgraded from 7.2.1 to 7.2.3 using the RPM.
>
>  Thanks in advance,
>
>  ben
>
Try:
SELECT setval('datadef_datadefindex_seq',MAX(datadefineindex));
This should set the sequence value for furture inserts, however if you insert without using the sequence genereated value it will become out of sync again.

hth,
- Stuart