In table foo
with two columns (id
and 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=9999
-> 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.
When running
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.
CREATE UNIQUE INDEX idx_seq
is unnecessary because you already defined the column asunique
in thecreate table