Re: 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.
>
>
> 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?

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