[GENERAL] Duplicate Unique Key constraint error
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
Re: [GENERAL] Duplicate Unique Key constraint error
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
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Duplicate Unique Key constraint error
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
Re: [GENERAL] 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 <[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
>
Re: [GENERAL] Duplicate Unique Key constraint error
On Jul 10, 2007, at 13:22 , Harpreet Dhaliwal wrote: 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. Why would the server lock the table? It can't know your intention is to add one to the number returned and insert. If this is what you want, you have to lock the table explicitly. Please guide me throug to get rid of this problem. This exact reason is why sequences are often used for primary keys. I recommend you change your primary key. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Duplicate Unique Key constraint error
"Harpreet Dhaliwal" <[EMAIL PROTECTED]> writes: > 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. This is exactly why you're recommended to use sequences (ie serial columns) for generating IDs. Taking max()+1 does not work, unless you're willing to lock the whole table and throw away vast amounts of concurrency. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Duplicate Unique Key constraint error
Thanks alot for all your suggestions gentlemen. I changed it to a SERIAL column and all the pain has been automatically alleviated :) Thanks a ton. ~Harpreet On 7/10/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Harpreet Dhaliwal" <[EMAIL PROTECTED]> writes: > 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. This is exactly why you're recommended to use sequences (ie serial columns) for generating IDs. Taking max()+1 does not work, unless you're willing to lock the whole table and throw away vast amounts of concurrency. regards, tom lane
Re: [GENERAL] Duplicate Unique Key constraint error
On Jul 10, 2007, at 3:09 PM, Tom Lane wrote: "Harpreet Dhaliwal" <[EMAIL PROTECTED]> writes: 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. This is exactly why you're recommended to use sequences (ie serial columns) for generating IDs. Taking max()+1 does not work, unless you're willing to lock the whole table and throw away vast amounts of concurrency. I wonder how SQL server is handling this? Are they locking the table? I realize it's off-topic, but I'm still curious. Sequences are your friend. they come in INT and BIGINT flavors, but BIGINT is a lot of rows. Can set set Sequences to automatically rollover back to zero? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Duplicate Unique Key constraint error
How can one rollover a sequence back to zero after you delete records from a table with one such sequence. I see it starting with the last value of the sequence inserted. On 7/11/07, Tom Allison <[EMAIL PROTECTED]> wrote: On Jul 10, 2007, at 3:09 PM, Tom Lane wrote: > > "Harpreet Dhaliwal" <[EMAIL PROTECTED]> writes: >> 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. > > This is exactly why you're recommended to use sequences (ie serial > columns) for generating IDs. Taking max()+1 does not work, unless > you're willing to lock the whole table and throw away vast amounts of > concurrency. I wonder how SQL server is handling this? Are they locking the table? I realize it's off-topic, but I'm still curious. Sequences are your friend. they come in INT and BIGINT flavors, but BIGINT is a lot of rows. Can set set Sequences to automatically rollover back to zero?
Re: [GENERAL] Duplicate Unique Key constraint error
Harpreet Dhaliwal escribió: > How can one rollover a sequence back to zero after you delete records from > a > table with one such sequence. > I see it starting with the last value of the sequence inserted. You can use setval(), but normally you just leave it alone. Having numbers not starting from 0 is not a problem in most cases. -- Alvaro Herrera http://www.flickr.com/photos/alvherre/ Hi! I'm a .signature virus! cp me into your .signature file to help me spread! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
