From: jacekp@poczta.wprost.pl
Subject: update vs unique index
Date: 25 Jul 2005 05:58:43 -0700
Organization: http://groups.google.com
Lines: 31
Message-ID: <1122296323.723165.27560@o13g2000cwo.googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
User-Agent: G2/0.2
Complaints-To: groups-abuse@google.com
Injection-Info: o13g2000cwo.googlegroups.com; posting-host=62.111.149.146;
	posting-account=5pOcrgwAAAAea_pm-cR3TwkmTPBCVBAc
To: pgsql-sql@postgresql.org

Consider such table:

CREATE TABLE test (idx integer);

populated by following statements:

INSERT INTO test VALUES (1);
INSERT INTO test VALUES (2);
INSERT INTO test VALUES (3);

since idx schould be unique, we need an index

CREATE UNIQUE INDEX i_test ON test(idx);

Following SQL command fails:

UPDATE test SET idx = idx + 1;

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.

regards,
-- 
Jacek Prucia