Skip site navigation (1) Skip section navigation (2)

Re: Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?

From: Nis Jørgensen <nis(at)superlativ(dot)dk>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?
Date: 2007-09-27 14:15:59
Message-ID: fdgdv1$j7r$1@sea.gmane.org (view raw or flat)
Thread:
Lists: pgsql-general
Ardian Xharra skrev:

> *From:* Anoo Sivadasan Pillai <mailto:aspillai(at)in(dot)rm(dot)com>

>> I am not using any sequences, The following batch can reproduce the
>> behaviour.
>> CREATE TABLE master ( m1 INT primary key , m2 int unique ) ;
>> INSERT  INTO master VALUES  ( 1, 1 ) ;
>> INSERT  INTO master VALUES  ( 2, 2) ;
>> UPDATE  master SET m2 = m2 + 1;

> It's normal behaviour, because after the first update it will be 2 same
> values for m2 and you don't want that since you have a unique constraint
> for that column. 

Please note: This is a bug in Postgresql, not "normal behaviour". From a
conceptual perspective, there is no "after the first update" - the
statement is supposed to be atomic.

Unfortunately, the problem is waiting for someone to get a great idea:

http://svr5.postgresql.org/pgsql-bugs/2007-02/msg00075.php


If you can't wait, you are probably better off working around the
problem. Standard solution is to do:

UPDATE  master SET m2 = -m2;
UPDATE  master SET m2 = -m2+1;

or something similar.

Nis


In response to

Responses

pgsql-general by date

Next:From: Aleksander Kmetec - INTERADate: 2007-09-27 14:33:06
Subject: Getting the search_path value for a query listed in pg_stat_activity output (feature request?)
Previous:From: John SmithDate: 2007-09-27 14:07:19
Subject: access privileges: grant select on (all current and future tables)?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group