skip to content

After COPY loads legacy order ids into a PostgreSQL identity column, why does the next INSERT fail, and how does setval fix it?

level: middleimportance: should knowfreq 40%

answer

  1. loads that bypass the generator
  2. COPY acts like OVERRIDING SYSTEM VALUE
  3. pg_get_serial_sequence finds it
  4. is_called decides the next value
  5. empty tables and cached sessions

basics

~20 s

COPY FROM writes the supplied ids even into a GENERATED ALWAYS column without calling nextval(), so the sequence still offers numbers the loaded rows hold. Resync it with setval(pg_get_serial_sequence('orders', 'order_id'), max(order_id)) while writers are paused.

solid answer

~50 s

An identity column's sequence only moves when PostgreSQL generates a value. `COPY ... FROM` writes identity values from the input as if `OVERRIDING SYSTEM VALUE` were given, even on a `GENERATED ALWAYS` column, and never calls `nextval()`. After loading legacy ids up to 4,800,000, the next ordinary `INSERT` gets a low number and fails on the primary key with SQLSTATE `23505`. Point the sequence past the data: `SELECT setval(pg_get_serial_sequence('orders', 'order_id'), coalesce(max(order_id), 0) + 1, false) FROM orders;`. `pg_get_serial_sequence()` finds the implicit sequence of an identity or serial column; the three-argument form with `false` makes the next `nextval()` return exactly that value and also copes with an empty table. Run it with writers paused: `setval()` takes effect at once and is never rolled back, and sessions holding a cached block keep using it until it runs out.

code

sql · 12 lines
sql
-- legacy shard export loaded with its original ids
COPY orders (order_id, shard_id, placed_at)
FROM '/srv/import/legacy_orders.csv' WITH (FORMAT csv, HEADER true);

-- the identity sequence did not move: resync it
SELECT setval(pg_get_serial_sequence('orders', 'order_id'),
              coalesce(max(order_id), 0) + 1,
              false)
FROM orders;

-- check the sequence row directly
SELECT last_value, is_called FROM orders_order_id_seq;

go deeper

for a junior

Recall that loading explicit ids does not move the sequence, so the next INSERT collides on the primary key, and that setval() is the fix.

for a middle

Explain COPY's identity behaviour, what pg_get_serial_sequence() returns, the is_called argument of setval(), and why the empty-table case needs coalesce.

for a senior

Script the resync into the migration itself, with writers paused, pooled connections recycled for cached sequences, and a check of the sequence row afterwards.

for a principal

Set the range policy for migrations into a live table: resync after the load, or move the generator clear of legacy ids beforehand so loads and traffic never meet.

## Why the generator falls behind An identity column, like a `serial` column, has two independent parts: the values stored in the rows, and a **sequence** that remembers the last number it handed out. The sequence advances only when PostgreSQL calls it, that is when an `INSERT` omits the column or writes `DEFAULT`. Anything that supplies explicit ids leaves it where it was: - `COPY orders FROM ...`: for identity columns, **COPY FROM always writes the values provided in the input**, behaving like `INSERT ... OVERRIDING SYSTEM VALUE`, even when the column is `GENERATED ALWAYS`. No `428C9` error is raised. - `INSERT ... OVERRIDING SYSTEM VALUE SELECT ...` from a staging table. - Any insert into a `BY DEFAULT` identity or a `serial` column that names the id. In the checkout service, migrating a legacy shard's orders with their original `order_id` values therefore leaves the sequence at, say, 1. The next application `INSERT` asks for a value, gets 1, and the primary key rejects it with `duplicate key value violates unique constraint`, SQLSTATE `23505` (`unique_violation`). The constraint catches the collision, not the identity declaration. ## PostgreSQL's tools for resyncing | Tool | What it does | Notes | |---|---|---| | `pg_get_serial_sequence(table, column)` | returns the sequence behind an identity or serial column | table name is parsed and lower-cased, column name taken literally | | `setval(seq, n)` | sets `last_value` to `n` with `is_called = true` | next `nextval()` returns `n` plus the increment | | `setval(seq, n, false)` | sets `last_value` to `n` with `is_called = false` | next `nextval()` returns exactly `n` | | `ALTER TABLE ... ALTER COLUMN ... RESTART WITH n` | restarts the identity's sequence | `n` must be a literal, so compute it first | `setval()` is the one that accepts a computed value in a single statement, which is why it is the usual fix. ## The safe statement ```sql SELECT setval(pg_get_serial_sequence('orders', 'order_id'), coalesce(max(order_id), 0) + 1, false) FROM orders; ``` Each piece earns its place: 1. `pg_get_serial_sequence()` avoids guessing the implicit sequence's name, and its result is accepted by the sequence functions. 2. `coalesce(..., 0) + 1` handles an **empty table**. `max()` returns null there, and `setval()` is strict, so a bare `setval(seq, max(order_id))` silently does nothing, while `setval(seq, 0)` on a sequence whose minimum is 1 fails with `setval: value 0 is out of bounds`. 3. `false` makes the next `nextval()` hand out exactly `max + 1`. With the two-argument form you would pass `max(order_id)` instead. ## Concurrency and visibility traps - **Run it with writers paused**, or with the table locked against writers. Between reading `max()` and calling `setval()`, a concurrent insert can take a value, and loads still supplying explicit ids can land above your new start. - **`setval()` is not transactional.** Its effect is visible to other sessions at once and is **not undone** if the surrounding transaction rolls back, so a `ROLLBACK` will not repair a wrong value. - **Cached sessions lag.** With `CACHE` above 1, other sessions keep serving numbers from the block they already reserved until it is used up; only then do they see the new position. Recycle the application's connections after the resync, or keep `CACHE 1` on tables you bulk-load. - **Check the sequence row itself**: `SELECT last_value, is_called FROM orders_order_id_seq;`. The `last_value` column of `pg_sequences` reads null for a sequence that has not been read since a `setval(..., false)`, so it is a poor check at this moment. ## Where the step belongs A `pg_dump` and `pg_restore` round trip already emits a `setval()` for each sequence it dumps, so restores rarely need this. The step belongs in hand-written migrations: a `COPY` of exported CSV files, an ETL job moving a staging table into `orders` with `OVERRIDING SYSTEM VALUE`, or the merge of a legacy shard. Script it immediately after the load, in the same maintenance window, and make it part of the runbook rather than a fix applied after the first `23505` alert. ## Keeping legacy ids out of the generator's way If legacy ids must coexist with new ones and writers cannot be paused for long, move the generator clear once, before the load starts: `ALTER TABLE orders ALTER COLUMN order_id RESTART WITH 10000000`. The loaded range and the generated range then never meet, and the post-load `setval()` becomes a safety check rather than a race. Choosing identifier ranges across shards in general is a schema-design decision; this step is only about keeping one table's sequence ahead of its own data.

  • Why might a PostgreSQL session keep issuing old order_id values after you ran setval()?
    If the sequence has `CACHE` above 1, each session serves numbers from the block it reserved before the `setval()`; the manual notes other sessions do not notice the change until they have used that block up. Those numbers may fall inside the range you just loaded. Pause writers, run `setval()`, then recycle the application's connections, or keep `CACHE 1` on tables you bulk-load.
  • When is ALTER TABLE ... ALTER COLUMN order_id RESTART WITH a better tool than setval()?
    When you know the number in advance, for example moving the generator to 10,000,000 before a legacy load so the two ranges never meet. `RESTART WITH` takes a literal, not an expression, so it cannot read `max(order_id)` itself; `setval()` can, in one statement. Both act on the same implicit sequence of the identity column.

  • COPY FROM advances the identity sequence for every id it loads
  • GENERATED ALWAYS stops COPY from writing explicit ids
  • setval() is rolled back if the migration transaction aborts
  • setval(seq, max(id)) makes the next nextval() return max(id) itself
  • pg_get_serial_sequence() works only for serial columns, not identity columns