Hosting Provided By

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

From: Alban Hertroys <a.hertroys(at)magproductions.nl>
Date: Fri Sep 28 2007 - 07:39:12 EDT

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.

Do you need help?

> 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 //


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               
http://archives.postgresql.org/

Received on Fri Sep 28 07:43:08 2007

Contemporary messages sorted: [ By Date ] [ By Thread ] [ By Subject ] [ By Author ] [ By messages with attachments ]

This archive was generated by hypermail 2.1.8 : Sun Oct 07 2007 - 11:56:33 EDT

Do you need more help?