X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 264BA529DF
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 25 Jul 2005 13:52:28 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 26770-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 25 Jul 2005 16:52:19 +0000 (GMT)
Received: from mr1.surnet.cl (smtp1.surnet.cl [216.155.73.162])
	by svr1.postgresql.org (Postfix) with ESMTP id B417452808
	for <pgsql-sql@postgresql.org>; Mon, 25 Jul 2005 13:52:18 -0300 (ADT)
Received: from smtp1.surnet.cl (216.155.73.168) by mr1.surnet.cl (7.0.031.3)
	id 42587EDE017F2D95; Mon, 25 Jul 2005 12:51:23 -0400
Received: from smtp1.surnet.cl (mr1.surnet.cl [])
	by mr1.surnet.cl ([216.155.73.168]); Mon, 25 Jul 2005 16:51:23 +0000
Received: from cluster.surnet.cl (216.155.73.164) by smtp1.surnet.cl
	(7.0.031.3) id 42587C6E01240177; Mon, 25 Jul 2005 12:51:23 -0400
Received: from alvh.no-ip.org (200.85.218.66) by cluster.surnet.cl (7.0.043)
	(authenticated as alvherre@surnet.cl)
	id 42B3EF6000531E6B; Mon, 25 Jul 2005 12:52:01 -0400
Received: by alvh.no-ip.org (Postfix, from userid 1000)
	id 5361BC2D450; Mon, 25 Jul 2005 12:52:49 -0400 (CLT)
Date: Mon, 25 Jul 2005 12:52:49 -0400
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: jacekp@poczta.wprost.pl
Cc: pgsql-sql@postgresql.org
Subject: Re: update vs unique index
Message-ID: <20050725165249.GA29395@alvh.no-ip.org>
References: <1122296323.723165.27560@o13g2000cwo.googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <1122296323.723165.27560@o13g2000cwo.googlegroups.com>
User-Agent: Mutt/1.5.9i
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.409 tagged_above=0 required=5 tests=AWL,
	DNS_FROM_RFC_ABUSE, FORGED_RCVD_HELO, RCVD_IN_NJABL_PROXY
X-Spam-Level: *
X-Archive-Number: 200507/222
X-Sequence-Number: 22200

On Mon, Jul 25, 2005 at 05:58:43AM -0700, jacekp@poczta.wprost.pl wrote:

> I can imagine why it fails. Update operates on first row, making 2 out
> of 1 and that collides with second row (which has 2 as its value
> already). However, when you look at the update efect as a whole
> uniqueness is preserved, so index schould not veto update.
> 
> My question is: is there a chance to bypass this behaviour? Something
> like controlling the order in which rows go into update. If update
> would start from last row, it would be successful for sure.

Yeah, this is a known limitation.  Usual workaround is issue two
updates instead of one,

update foo set a = -a where <condition>;
update foo set a = -a + 1 where <condition>;

The point is to move all unique keys to an unused interval and then
move them back, changed all at a time.

It'll eventually be fixed, but don't hold your breath.

-- 
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"No hay ausente sin culpa ni presente sin disculpa" (Prov. francés)