Skip site navigation (1) Skip section navigation (2)

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

From: Alban Hertroys <a(dot)hertroys(at)magproductions(dot)nl>
To: Nis Jørgensen <nis(at)superlativ(dot)dk>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Re: Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?
Date: 2007-09-28 10:16:54
Message-ID: 46FCD496.4090507@magproductions.nl (view raw or flat)
Thread:
Lists: pgsql-general
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 //


In response to

Responses

pgsql-general by date

Next:From: Nis JørgensenDate: 2007-09-28 10:36:39
Subject: Re: Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?
Previous:From: Stefan SchwarzerDate: 2007-09-28 09:25:44
Subject: Re: Normalized Tables & SELECT [was: Find "smallest common year"]

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group