AutoIncrement not working

From: Ben Kassel <kasselb(at)nswccd(dot)navy(dot)mil>
To: pgsql-admin(at)postgresql(dot)org
Subject: AutoIncrement not working
Date: 2002-11-04 18:21:31
Message-ID: 3DC6BAAB.CC26A6E1@nswccd.navy.mil
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

---------------------------(end of broadcast)---------------------------

TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Andrew Sullivan 2002-11-04 19:25:57 Re: HA for high insert volume
Previous Message dima 2002-11-04 15:18:46 Re: dumping a password-protected db from a perl script or