4

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.

4
  • The updating order is not definite (and it cannot be defined - UPDATE statement does not allow ORDER BY clause). Disable constraints check before updating and restore after.
    – Akina
    Dec 1, 2020 at 4:57
  • 1
    "Disable constraints check before updating and restore after." that sounds risky. Isn't there any other way to achieve this?
    – orange
    Dec 1, 2020 at 5:42
  • that sounds risky. What is the risk? drop unique index, update table, recreate index. This does not affect on any other database object. I hope there is no FKs on this table?
    – Akina
    Dec 1, 2020 at 5:58
  • Unrelated, but: the CREATE UNIQUE INDEX idx_seq is unnecessary because you already defined the column as unique in the create table
    – user1822
    Dec 1, 2020 at 6:44

1 Answer 1

7

You need to create a deferrable constraint rather than an index.

CREATE TABLE foo
(
  id uuid NOT NULL primary key
  seq integer NOT NULL,
  CONSTRAINT seq_key UNIQUE (seq)
    deferrable initially immediate --<< this
);

By declaring the constraint as deferrable it will be checked at the end of the UPDATE statement (to be more precise: at the end of the transaction) rather than row-by-row.

2
  • That did the trick - thanks. I wonder why this isn't the default setting (it would be reasonable to assume that the constraint isn't checked during a query; after all I would expect a query to be atomic).
    – orange
    Dec 2, 2020 at 4:50
  • One disadvantage of a deferrable constraint (compared to a unique index): ".. deferrable constraints cannot be used as conflict arbitrators in an INSERT statement that includes an ON CONFLICT DO UPDATE clause." (postgresql.org/docs/12/sql-createtable.html)
    – Jared Beck
    Feb 24, 2022 at 22:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.