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

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(at)magproductions(dot)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 //