foo with two columns (
seq), I'd like to add +1 to
seq for all records with an arbitrary
seq > 4738. The plan is to insert a new record at
seq=4739 once all the
seq > 4738 records are shifted by +1.
This is the table.
CREATE TABLE foo ( id uuid NOT NULL, seq integer NOT NULL, CONSTRAINT seq_key UNIQUE (seq) ) CREATE UNIQUE INDEX idx_id ON foo USING btree (id); CREATE UNIQUE INDEX idx_seq ON foo USING btree (seq);
I try to achieve the +1 shift with the following query. Note, that I use a subquery in an attempt to update the
> 4738 records in descending order (i.e. assuming max seq=10000 then the last record is updated first (10000->10001), then second last (
seq=10000 doesn't exist at this point,
seq=10000 (no constraint violation), then 9998 -> 9999, ... to avoid at any one time that a unique constraint violation occurs). However, this assumes sequential execution of the update query which isn't what's seemingly happening.
UPDATE foo SET seq=anon_1.new_seq FROM ( SELECT foo.id AS id, foo.seq + 1 AS new_seq FROM foo WHERE foo.seq > 4738 ORDER BY foo.seq DESC ) AS anon_1 WHERE foo.id = anon_1.id
I get the following error.
duplicate key value violates unique constraint "seq_key" DETAIL: Key (seq)=(7334) already exists.
Obviously, that's unexpected (as the constraint was satisfied before the
UPDATE). Is there anything I could try to resolve the problem (different index type, only constraint, only index)? I noticed that this error very much depends on the number of updated records. If fewer records are updated, then this problem doesn't seem to occur (this may hint to some parallel execution for when there are too many records to update which may interleave and cause some intermediate state in which the constraint/index is not unique any more). Some thoughts and ideas would be very appreciated.