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