I lately figured out the actual problem PHEW.
Its something like two different transactions are seeing the same snapshot
of the database.
Transaction 1 started, saw max(dig_id) = 30 and inserted new dig_id=31.
Now the time when Transaction 2 started and read max(dig_id) it was still 30
and by the time it tried to insert 31, 31 was already inserted by
Transaction 1 and hence the unique key constraint error.
I thought this would be taken care by the database itself by locking the
transactions but now I really don't know how does this locking takes place
in postgres. I used to work with SQL Server and never faced this problem
there.
Please guide me throug to get rid of this problem.
Thanks,
~Harpreet
On 7/10/07, Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote:
my primary key is neither SERIAL nor a SEQUENCE.
CONSTRAINT pk_dig PRIMARY KEY (dig_id)
This is the clause that I have for my primary key in the create table
script.
thanks,
~Harpreet
On 7/10/07, Ron St-Pierre <[EMAIL PROTECTED]> wrote:
>
> Harpreet Dhaliwal wrote:
> > Hi,
> >
> > I keep getting this duplicate unique key constraint error for my
> > primary key even
> > though I'm not inserting anything duplicate. It even inserts the
> > records properly
> > but my console throws this error that I'm sure of what it is all
> about.
> >
> > Corruption of my Primary Key can be one of the possibilities but I'm
> > really not sure how
> > to get rid of this corruption and how to re-index the primary key.
> >
> > Also, I was wondering what could be the cause of this PK corruption,
> > if possible and what does can this corruption lead to.
> > I mean what are its cons.
> >
> > Thanks,
> > ~Harpreet
> You haven't really given any useful information about your primary key,
> but if you are using SERIAL as the column type (INT type with a
> sequence) you may just be having a problem with its current value (but
> then inserts shouldn't work).
>
> If you are using a sequence here, see what it's current value is and
> compare it to the highest value in the column. If its value is less than
>
> the columns max() value, just reset the value in the sequence.
>
> imp=# CREATE TABLE dup_pkey (id SERIAL PRIMARY KEY, insert_order int);
> imp=# INSERT INTO dup_pkey (insert_order) VALUES (1);
> imp=# INSERT INTO dup_pkey (insert_order) VALUES (2);
> imp=# INSERT INTO dup_pkey (insert_order) VALUES (3);
> imp=# INSERT INTO dup_pkey (insert_order) VALUES (4);
>
> imp=# SELECT * FROM dup_pkey;
> id | insert_order
> ----+--------------
> 1 | 1
> 2 | 2
> 3 | 3
> 4 | 4
> (4 rows)
>
> Now, if you set the value below what the max() column value is, you will
> have a problem with inserts.
> imp=# SELECT setval('dup_pkey_id_seq',3);
> setval
> --------
> 3
> (1 row)
>
> imp=# INSERT INTO dup_pkey (insert_order) VALUES (5);
> ERROR: duplicate key violates unique constraint "dup_pkey_pkey"
>
>
> If this is the case, use setval() to update the value of the sequence to
>
> the max() value of your primary key. You can use \d to get information
> about your table, including the sequence name. However if, as you say,
> it IS inserting records properly, then this ISN'T going to help.
>
> hth
>
> Ron
>
