From: Glenn Maynard <glennfmaynard@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: changing multiple pk's in one update
Date: Wed, 15 Apr 2009 16:25:58 -0400
Message-ID: <d18085b50904151325h6533b7e1o6e11702ed570d1c5@mail.gmail.com> (raw)
In-Reply-To: <gs4kok$hdn$1@reversiblemaps.ath.cx>
References: <49DC1034.1060705@frii.com>
<grnl9e$49a$1@reversiblemaps.ath.cx>
<49E36E1B.6020107@frii.com>
<gs4kok$hdn$1@reversiblemaps.ath.cx>
On Wed, Apr 15, 2009 at 8:43 AM, Jasen Betts <jasen@xnet.co.nz> wrote:
> the update takes a long time too if it's updating all the rows.
> and updating the index piecewise at the same time.
> with the index extant I get from 20 (if the start and end ranges don't overlap) and 28s (with ,
> to 28 seconds (maximum overlap) for a table with 1000000 (short) rows
He's using a compound key as his PK, eg. (entry, order), where there
are only a few order values for each entry, so I think the real case
is only updating order for a specific set of entries.
>> I have continued searching for other ways to do this
>> but most of my google results are floating in a sea
>> of "update if insert fails, like mysql" results so
>> I still have a little hope it is possible.
>> I thought I remember seeing, a year or two ago, an
>> update statement with an ordered subquery that avoided
>> duplicate key errors but I am probably misrembering.
Bear in mind that the update is failing based on the order the data is
in the table, not the PK order.
create table test (id integer primary key);
insert into test (id) values (2), (1), (3);
update test set id=id+1;
ERROR: duplicate key value violates unique constraint "test_pkey"
update test set id=id-1;
ERROR: duplicate key value violates unique constraint "test_pkey"
Both fail, because it tries to update 2 first.
I suppose in a real pinch, you could renumber in two steps. For
example, if you wanted to delete id 6 and move everything else down:
insert into test (id) values (2), (7), (3), (1), (4), (5), (6), (8), (9);
begin;
set transaction isolation level serializable;
delete from test where id=6;
update test set id = id+1000000 where id >= 6;
update test set id = id-1000001 where id >= 6;
commit;
Not very nice, but if "id" is really a sequence number starting at 1
in your case and not an always-increasing generated regular serial (so
there's no chance of it actually reaching the arbitrarily large number
1000000), it should work. (It'd probably be workable for real
serials, too, with a much larger offset.)
If someone else creates a new sense for that entry after the first
update, it'll sit on the order number you were about to use and the
operation will fail. Serialize so nobody else will insert until
you're done.
--
Glenn Maynard
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: glennfmaynard@gmail.com
Subject: Re: changing multiple pk's in one update
In-Reply-To: <d18085b50904151325h6533b7e1o6e11702ed570d1c5@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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