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

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.

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


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster