AutoIncrement not working on this table only

From: "Ben Kassel" <benk(at)ix(dot)netcom(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Cc: <kasselb(at)nswccd(dot)navy(dot)mil>
Subject: AutoIncrement not working on this table only
Date: 2002-11-03 16:21:35
Message-ID: 000d01c28355$166a76d0$de0c2e82@codeine
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

MY WORK MAIL SERVER IS DOING STRANGE THINGS TODAY. IF YOU WANT TO RESPOND DIRECTLY TO ME PLEASE RESPOND TO THE kasselb(at)nswccd(dot)navy(dot)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

Browse pgsql-novice by date

  From Date Subject
Next Message Nick Sayer 2002-11-03 16:50:06 Novice schema question
Previous Message Reshat Sabiq 2002-11-03 08:38:27 moving a postgreSQL DB, etc.