Thread: AutoIncrement not working on this table only

AutoIncrement not working on this table only

From

"Ben Kassel"

Date:

04 November 2002, 17:51:08

MY WORK MAIL SERVER IS DOING STRANGE THINGS TODAY. IF YOU WANT TO RESPOND DIRECTLY TO ME PLEASE RESPOND TO THE kasselb@nswccd.navy.mil as refelcted in the cc line.

thanks,

ben

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

Re: AutoIncrement not working on this table only

From

Kassel Ben CRBE

Date:

05 November 2002, 00:11:51

A huge thank you to all who helped me solve my problem. To synopsize for those keeping a lessons learned:

The sequence table got out of sync with the with the column being sequenced. This was mot probably caused by reloading
dataimproperly without regard for the sequence generated value.  

The sequence can be resynced using the command:

select (setval('my_seq',max(my_col)) from my_table;

or in my case the actual command which solved the problem:

SELECT setval('datadef_datadefindex_seq',MAX(datadefindex)) from datadef;

later,
ben