pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedupdate vs unique index
2+ messages / 2 participants
[nested] [flat]
* update vs unique index
@ 2005-07-25 12:58 jacekp@poczta.wprost.pl
0 siblings, 1 reply; 2+ messages in thread
From: jacekp@poczta.wprost.pl @ 2005-07-25 12:58 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: update vs unique index
@ 2005-07-25 16:52 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: jacekp@poczta.wprost.pl
0 siblings, 0 replies; 2+ messages in thread
From: Alvaro Herrera @ 2005-07-25 16:52 UTC (permalink / raw)
To: jacekp@poczta.wprost.pl; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-07-25 16:52 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-25 12:58 update vs unique index jacekp@poczta.wprost.pl
2005-07-25 16:52 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox