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

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