Re: Duplicate Unique Key constraint error

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 <harpreet(dot)dhaliwal01(at)gmail(dot)com> 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 <ron(dot)pgsql(at)shaw(dot)ca> 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
> >
>
>