Problem with Auto Increment

Поиск
Список
Период
Сортировка
От Ben Kassel
Тема Problem with Auto Increment
Дата
Msg-id 3DC6BC9B.3C628D23@nswccd.navy.mil
обсуждение исходный текст
Ответы Re: Problem with Auto Increment  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
Список pgsql-sql
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 nullPrimary key: datadef_pkeyUnique keys:
datadefname_idxTriggers: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



В списке pgsql-sql по дате отправления:

Предыдущее
От: Terry Yapt
Дата:
Сообщение: Re: Comparing Numeric and Double Precision (float8)..
Следующее
От: Stephan Szabo
Дата:
Сообщение: Re: Problem with Auto Increment