Re: changing multiple pk's in one update

On Wed, Apr 15, 2009 at 8:43 AM, Jasen Betts <jasen(at)xnet(dot)co(dot)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