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

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