Обсуждение: Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?

----- Original Message -----

From: Anoo Sivadasan Pillai

To: pgsql-general@postgresql.org

Cc: Anoo Sivadasan Pillai

Sent: Monday, September 24, 2007 12:50 PM

Subject: [GENERAL] Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?

I am using "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)" on Windows 2003 server

While I am trying to update a prmary key It is failing with the  following  message “ERROR: duplicate key violates unique constraint "master_pkey" “

Can anybody explain why this happens so?  Sending the script that I tried.

 

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"

 

If I insert data in the reverse order it is making no problem. Is this a Bug ?

I tried ,

 

TRUNCATE TABLE master;

INSERT  INTO master VALUES  ( 3, 'm3' ) ;

INSERT  INTO master VALUES  ( 2, 'm2' ) ;

UPDATE master SET m1 = m1 + 1;

It works perfectly.

 

 

Anoo S

 

Visit our Website at 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.

----- Original Message -----

From: Anoo Sivadasan Pillai

To: pgsql-general@postgresql.org

Cc: Anoo Sivadasan Pillai

Sent: Monday, September 24, 2007 3:20 AM

Subject: [GENERAL] Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?

I am using "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)" on Windows 2003 server

While I am trying to update a prmary key It is failing with the  following  message “ERROR: duplicate key violates unique constraint "master_pkey" “

Can anybody explain why this happens so?  Sending the script that I tried.

 

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"

 

If I insert data in the reverse order it is making no problem. Is this a Bug ?

I tried ,

 

TRUNCATE TABLE master;

INSERT  INTO master VALUES  ( 3, 'm3' ) ;

INSERT  INTO master VALUES  ( 2, 'm2' ) ;

UPDATE master SET m1 = m1 + 1;

It works perfectly.

 

 

Anoo S

 

Visit our Website at 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.

---

No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.488 / Virus Database: 269.13.30/1025 - Release Date: 23/09/2007 13:53

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"

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

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

On 25/09/2007, Anoo Sivadasan Pillai <aspillai@in.rm.com> wrote:
>
>
>
>
> 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;
>
>



Isn't this expected behavior? When you update the m2 of the first
record, it becomes 2 and violates the unqiue constraint as the second
row already has an m2 value of 2.

I have missed the thread, but this reeks of an unusual DB design. If
your really want the updates to go in reverse order so that keys are
not violated, you can do that in your application -- SELECT the keys
you wish to update sorted in DESC order by m2, and then foreach of
them, just increment the value accordingly. If you wrap this in a
transaction, it'll be pretty fast, depending on how many rows you're
talking about (I've found great speeds on records up to 50,000 for a
live DB).

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 ?

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

--- Phoenix Kiula <phoenix.kiula@gmail.com> wrote:

> Isn't this expected behavior? When you update the m2 of the first
> record, it becomes 2 and violates the unique constraint as the second
> row already has an m2 value of 2.

Well, it is a limitation PostgreSQL.  This type of update is should work without any problem
according to the SQL standard.   The Problem with performing two updates is that it double the
amount of dead tuples.  I guess that this would be another example where having a small fill
factor would help.

One kind of data model that depends heavily on this type of operation is the Hierarchical Nested
Set data model.  Inserting/updating/deleting nodes and branches into the table requires updating
the primary key of a lot of records.

Regards,
Richard Broersma Jr.

----- Original Message -----

From: Anoo Sivadasan Pillai

To: Ardian Xharra

Cc: pgsql-general@postgresql.org

Sent: Tuesday, September 25, 2007 12:27 AM

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

Hi,

 

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;

 

With Cheers,

Anoo S

From: Ardian Xharra [mailto:axharra@boxxo.info]
Sent: 25 September 2007 00:38
To: Anoo Sivadasan Pillai; pgsql-general@postgresql.org
Cc: Anoo Sivadasan Pillai
Subject: Re: [GENERAL] Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?

 

Probably you are using a sequence, and if so you need to update the value of sequence prior to update:

 

SELECT setval('master_m1_seq',((SELECT id_m1 FROM master ORDER BY 1 DESC LIMIT 1)+1));

----- Original Message -----

From: Anoo Sivadasan Pillai

To: pgsql-general@postgresql.org

Cc: Anoo Sivadasan Pillai

Sent: Monday, September 24, 2007 3:20 AM

Subject: [GENERAL] Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?

 

I am using "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)" on Windows 2003 server

While I am trying to update a prmary key It is failing with the  following  message “ERROR: duplicate key violates unique constraint "master_pkey" “

Can anybody explain why this happens so?  Sending the script that I tried.

 

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"

 

If I insert data in the reverse order it is making no problem. Is this a Bug ?

I tried ,

 

TRUNCATE TABLE master;

INSERT  INTO master VALUES  ( 3, 'm3' ) ;

INSERT  INTO master VALUES  ( 2, 'm2' ) ;

UPDATE master SET m1 = m1 + 1;

It works perfectly.

 

 

Anoo S

Visit our Website at 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.

---

No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.488 / Virus Database: 269.13.30/1030 - Release Date: 25/09/2007 08:02

On Mon, Sep 24, 2007 at 12:50:22PM +0530, Anoo Sivadasan Pillai wrote:
> I am using "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC
> gcc.exe (GCC) 3.4.2 (mingw-special)" on Windows 2003 server
>
> While I am trying to update a prmary key It is failing with the
> following  message "ERROR: duplicate key violates unique constraint
> "master_pkey" "
>
> Can anybody explain why this happens so?  Sending the script that I
> tried.
>
> 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;

One way to do this is with Postgres's UPDATE ... FROM construct:

CREATE TABLE foo(i INTEGER PRIMARY KEY);
INSERT INTO foo(i) VALUES (1), (2), (3), (4), (5);
UPDATE foo SET i=foo.i+1
    FROM (SELECT i FROM foo ORDER BY i DESC) f
    WHERE f.i = foo.i;

While specific to Postgres, this technique avoids a lot of messing
around with boundary conditions :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate

Ardian Xharra skrev:

> *From:* Anoo Sivadasan Pillai <mailto:aspillai@in.rm.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

Nis Jørgensen wrote:
> 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.

Would something like

UPDATE master set m2 = master2.m2
  FROM (
    SELECT m2 +1
      FROM master m
     WHERE m.master_id = master.master_id
     ORDER BY m2 DESC
 ) master2

work? I think it might be faster (and possibly cause less index bloat)
than doing two consequent updates.

Haven't tested this though...

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

Alban Hertroys skrev:
> Nis Jørgensen wrote:
>> 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.
>
> Would something like
>
> UPDATE master set m2 = master2.m2
>   FROM (
>     SELECT m2 +1
>       FROM master m
>      WHERE m.master_id = master.master_id
>      ORDER BY m2 DESC
>  ) master2
>
> work? I think it might be faster (and possibly cause less index bloat)
> than doing two consequent updates.


I don't understand your query. I don't think you can use a correlated
subquery in that way.

Anyway, tricks like these might work. They might stop working without
warning, if the plan changes. Relying on unspecified behavior is a
recipe for trouble.

Nis

Nis Jørgensen wrote:
> Alban Hertroys skrev:
>> Would something like
>>
>> UPDATE master set m2 = master2.m2
>>   FROM (
>>     SELECT m2 +1
>>       FROM master m
>>      WHERE m.master_id = master.master_id
>>      ORDER BY m2 DESC
>>  ) master2
>>
>> work? I think it might be faster (and possibly cause less index bloat)
>> than doing two consequent updates.
>
>
> I don't understand your query. I don't think you can use a correlated
> subquery in that way.

Hmm indeed, it complains something vague: "ERROR:  subquery in FROM may
not refer to other relations of same query level". Not sure why?

Effectively it orders the updates descending, so that the new value of
m2 can never be updated to an already existing value, because that has
been updated previously.

The WHERE condition makes the query look a bit more complex than it
actually is, but is necessary of course.

> Anyway, tricks like these might work. They might stop working without
> warning, if the plan changes. Relying on unspecified behavior is a
> recipe for trouble.

If I specifically ask for an ordering, I don't think the planner should
change or ignore that ordering. So I'm not relying on unspecified behaviour.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

Alban Hertroys skrev:
> Nis Jørgensen wrote:
>> Alban Hertroys skrev:
>>> Would something like
>>>
>>> UPDATE master set m2 = master2.m2
>>>   FROM (
>>>     SELECT m2 +1
>>>       FROM master m
>>>      WHERE m.master_id = master.master_id
>>>      ORDER BY m2 DESC
>>>  ) master2
>>>
>>> work? I think it might be faster (and possibly cause less index bloat)
>>> than doing two consequent updates.
>>
>> I don't understand your query. I don't think you can use a correlated
>> subquery in that way.
>
> Hmm indeed, it complains something vague: "ERROR:  subquery in FROM may
> not refer to other relations of same query level". Not sure why?

As I said, I don't understand what you think it does. What you are doing
is similar to writing

SELECT m2
FROM master, (
    SELECT m2
    FROM master m
    WHERE m.master_id = master.master_id
    )

Which doesn' make any sense either.

You probably want

UPDATE master set m2 = master2.m2
    FROM (
        SELECT m2 +1
        FROM master m
        ORDER BY m2 DESC
    ) master2
WHERE master2.master_id = master.master_id

> Effectively it orders the updates descending, so that the new value of
> m2 can never be updated to an already existing value, because that has
> been updated previously.
>
> The WHERE condition makes the query look a bit more complex than it
> actually is, but is necessary of course.
>
>> Anyway, tricks like these might work. They might stop working without
>> warning, if the plan changes. Relying on unspecified behavior is a
>> recipe for trouble.
>
> If I specifically ask for an ordering, I don't think the planner should
> change or ignore that ordering. So I'm not relying on unspecified behaviour.

According to the SQL spec, all the updates happen at the same time. Thus
any order this happens in is an implementation detail.

The only places where an "ORDER BY" clause is guaranteed to yield
specific results are those which are documented. - off the top of my
head, I can think of "outputting rows to the client", "LIMIT" and
"DISTINCT ON".

The fact that you stick an "ORDER BY" into a subquery guarantees
nothing. The planner might even see that it has no effect (according to
the spec) and ignore it. For instance this

SELECT *
FROM (SELECT *
    FROM mytable
    ORDER BY id
)
WHERE some_criteria

is not guaranteed to return an ordered result set. Thus the planner can
ignore the ORDER BY (but might not do so).

Nis

I had to manipulate the headers a bit, as I hadn't noticed the message
that reached me first was from the newsgroup instead of the ML.

Nis Jørgensen wrote:
> Alban Hertroys skrev:

> As I said, I don't understand what you think it does. What you are doing
> is similar to writing
>
> SELECT m2
> FROM master, (
>     SELECT m2
>     FROM master m
>     WHERE m.master_id = master.master_id
>     )
>
> Which doesn' make any sense either.
>
> You probably want
>
> UPDATE master set m2 = master2.m2
>     FROM (
>         SELECT m2 +1
>         FROM master m
>         ORDER BY m2 DESC
>     ) master2
> WHERE master2.master_id = master.master_id

So you do understand.

As I mentioned earlier, I didn't test that query. The extra alias
bothered me as unnecessary, and now I see why - I put the where clause
at the wrong place.

> According to the SQL spec, all the updates happen at the same time. Thus
> any order this happens in is an implementation detail.

According to the SQL spec the original update statement should have worked.

But it doesn't, so the updates _don't_ all happen at the same time. That
means there is an order in which they occur, and that order is likely to
be manipulatable.

> The fact that you stick an "ORDER BY" into a subquery guarantees
> nothing. The planner might even see that it has no effect (according to
> the spec) and ignore it. For instance this
>
> SELECT *
> FROM (SELECT *
>     FROM mytable
>     ORDER BY id
> )
> WHERE some_criteria
>
> is not guaranteed to return an ordered result set. Thus the planner can
> ignore the ORDER BY (but might not do so).

You are probably right that there's no way to guarantee that ordering,
but the method I suggested works in at least the version of Postgres I
have available (8.1.8), and they'll also work in database versions that
update atomically.

There _might_ be a small window of future PG versions where the planner
outsmarts this "trick" while it doesn't yet update atomically, but I
believe that to be rather unlikely.

I expect the priorities of the developers to be on atomic updates as
opposed to filtering out explicitly requested but unnecessary ordering.
The latter may be in use by many to massage the planner into picking a
different plan (even though it's not the right way to fix a bad plan of
course).

Here's some proof:

CREATE TABLE update_test (
    update_id serial NOT NULL PRIMARY KEY,
    num integer NOT NULL UNIQUE
);

INSERT INTO update_test (num) SELECT * FROM generate_series(5, 15);

-- fails
UPDATE update_test SET num = u2.num
FROM (
    SELECT update_id, num +1 AS num
    FROM update_test
) u2
WHERE update_test.update_id = u2.update_id;

-- succeeds
UPDATE update_test SET num = u2.num
FROM (
    SELECT update_id, num +1 AS num
    FROM update_test
    ORDER BY num DESC
) u2
WHERE update_test.update_id = u2.update_id;

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

Alban Hertroys skrev:
>
> Nis Jørgensen wrote:
>> Alban Hertroys skrev:
>
>> As I said, I don't understand what you think it does. What you are doing
>> is similar to writing
>>
>> SELECT m2
>> FROM master, (
>>     SELECT m2
>>     FROM master m
>>     WHERE m.master_id = master.master_id
>>     )
>>
>> Which doesn' make any sense either.
>>
>> You probably want
>>
>> UPDATE master set m2 = master2.m2
>>     FROM (
>>         SELECT m2 +1
>>         FROM master m
>>         ORDER BY m2 DESC
>>     ) master2
>> WHERE master2.master_id = master.master_id
>
> So you do understand.
>
> As I mentioned earlier, I didn't test that query. The extra alias
> bothered me as unnecessary, and now I see why - I put the where clause
> at the wrong place.
>
>> According to the SQL spec, all the updates happen at the same time. Thus
>> any order this happens in is an implementation detail.
>
> According to the SQL spec the original update statement should have worked.
>
> But it doesn't, so the updates _don't_ all happen at the same time. That
> means there is an order in which they occur, and that order is likely to
> be manipulatable.

Yes. No dispute here.

> You are probably right that there's no way to guarantee that ordering,
> but the method I suggested works in at least the version of Postgres I
> have available (8.1.8), and they'll also work in database versions that
> update atomically.

It works right now, for the current contents of the table. It might not
work tomorrow, when the planner chooses a different plan.

As an example, I just tried disabling seqscans. After doing this, the
update fails. The plans given for the two cases are estimated to 87 and
97 units, respectively. Do you really want to bet your money on this
plan staying ahead?

Nis