Odoo Sequence Drift Recovery Runbook (Duplicate Key Failures)

When Odoo starts throwing duplicate key value violates unique constraint on inserts, sequence drift is usually the root cause. This runbook keeps recovery deterministic: confirm drift, repair only affected sequences, then validate writes.

Incident signals

• New records fail on create for core models (sale.order, account.move, stock.picking).
• Odoo logs include duplicate key errors on id columns.
• Error appears after database restore, bulk import, or manual SQL inserts.

Step 1 — Capture failing relation and key

Start with the first real insert failure in logs, not cascading retries.

journalctl -u odoo --since "20 min ago" --no-pager | grep -E "duplicate key value|violates unique constraint" | tail -n 40

Capture:

1. table name (public.sale_order)
2. conflicting key (id=(48219))
3. failing workflow (UI action, API endpoint, or queue job)

Step 2 — Detect drift across serial-backed primary keys

This query generates and executes checks for every id column backed by a sequence.

psql "$ODOO_DB_URI" <<'SQL'
with serial_id_columns as (
  select
    ns.nspname as schema_name,
    cls.relname as table_name,
    att.attname as column_name,
    pg_get_serial_sequence(format('%I.%I', ns.nspname, cls.relname), att.attname) as seq_fqname
  from pg_class cls
  join pg_namespace ns on ns.oid = cls.relnamespace
  join pg_attribute att on att.attrelid = cls.oid
  where cls.relkind = 'r'
    and att.attnum > 0
    and not att.attisdropped
    and att.attname = 'id'
)
select format(
  $$select %L as table_name,
           %L as sequence_name,
           (select coalesce(max(id), 0) from %I.%I) as max_id,
           (select last_value from %s) as seq_last,
           (select last_value from %s) < (select coalesce(max(id), 0) from %I.%I) as is_drifted;$$,
  schema_name || '.' || table_name,
  seq_fqname,
  schema_name, table_name,
  seq_fqname,
  seq_fqname,
  schema_name, table_name
)
from serial_id_columns
where seq_fqname is not null
\gexec
SQL

Focus on rows where is_drifted = t.

Step 3 — Repair only drifted sequences

Generate setval statements and review before executing.

psql "$ODOO_DB_URI" <<'SQL'
with serial_id_columns as (
  select
    ns.nspname as schema_name,
    cls.relname as table_name,
    att.attname as column_name,
    pg_get_serial_sequence(format('%I.%I', ns.nspname, cls.relname), att.attname) as seq_fqname
  from pg_class cls
  join pg_namespace ns on ns.oid = cls.relnamespace
  join pg_attribute att on att.attrelid = cls.oid
  where cls.relkind = 'r'
    and att.attnum > 0
    and not att.attisdropped
    and att.attname = 'id'
)
select format(
  $$select setval(%L, greatest((select coalesce(max(id), 0) from %I.%I), 1), true);$$,
  seq_fqname,
  schema_name, table_name
)
from serial_id_columns
where seq_fqname is not null
\gexec
SQL

If you need strict change control, run the query once without \gexec, save output, and execute statements manually.

Step 4 — Validate recovery before reopening traffic

• Re-run Step 2 and confirm no drifted rows remain.
• Retry one safe create flow in each critical domain (sales, invoicing, inventory).
• Watch logs for 10–15 minutes for recurring duplicate key errors.

Post-incident hardening

• After every restore/import, run a sequence drift check before enabling write traffic.
• Treat direct SQL inserts into Odoo tables as exceptional and documented.
• Add this check to your release preflight or recovery checklist.

Sequence drift incidents are usually fast to fix if you avoid guesswork. Detect globally, repair narrowly, and validate writes immediately.