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

Hi,

On further testing I found the same behaviour in Unique keys too, 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;

I agree with the statement “Success will depend on the order in which postgresql decides to visit rows: if it visits all rows in descending order, you'll be fine,”

But since you can’t control the order, it is likely that the operation will sometimes and will succeed sometimes, with the same data.. Weird I feel .

The work around given by you will work, Does it mean that whenever we are updating a unique key we have to take this precaution ?

Anoo S

From: Reece Hart [mailto:reece(at)harts(dot)net]
Sent: 25 September 2007 03:28
To: Anoo Sivadasan Pillai
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Why the ERROR: duplicate key violates uniqueconstraint "master_pkey" is raised? - Is this a Bug?

On Mon, 2007-09-24 at 12:50 +0530, Anoo Sivadasan Pillai wrote:

CREATE TABLE master ( m1 INT primary key , m2 VARCHAR(100)) ;
INSERT INTO master VALUES ( 1, 'm1' ) ;
INSERT INTO master VALUES ( 2, 'm2' ) ;
UPDATE master SET m1 = m1 + 1;
Update fails with the message - ERROR: duplicate key violates unique constraint "master_pkey"

Primary key constraints are not deferred and are not deferrable (try: \x, then select * from pg_constraint where conname~'master_pkey'). This means that the constraint is checked immediately for each row updated. Success will depend on the order in which postgresql decides to visit rows: if it visits all rows in descending order, you'll be fine, but that's unlikely and uncontrollable (AFAIK).

Here's a sketch of an easy workaround. You might have to modify it for your particular range of m1.

begin;
update master set m1=-m1;
update master set m1=-m1+1;
commit;

You could just as easily add N to m1, then subtract (N-1) from m1. You'll need N>max(m1).

Good luck,
Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Visit our Website at http://www.rmesi.co.in

This message is confidential. You should not copy it or disclose its contents to anyone. You may use and apply the information for the intended purpose only. Internet communications are not secure; therefore, RMESI does not accept legal responsibility for the contents of this message. Any views or opinions presented are those of the author only and not of RMESI. If this email has come to you in error, please delete it, along with any attachments. Please note that RMESI may intercept incoming and outgoing email communications.

Freedom of Information Act 2000
This email and any attachments may contain confidential information belonging to RMESI. Where the email and any attachments do contain information of a confidential nature, including without limitation information relating to trade secrets, special terms or prices these shall be deemed for the purpose of the Freedom of Information Act 2000 as information provided in confidence by RMESI and the disclosure of which would be prejudicial to RMESI’s commercial interests.

This email has been scanned for viruses by Trend ScanMail.