После импорта данных в PostgreSQL дублирующее значение ключа нарушает уникальное ограничение

Недавно я перенесла приложение rails в PostgreSQL, чтобы использовать полнотекстовый поиск.

Поскольку миграция совпала с переходом на новый веб-хост, шаги для миграции были следующими:

  • Развертывание приложения и db: create/db: schema: загрузка на новый сервер с соответствующим файлом database.yml
  • данные mysqldump только из существующей базы данных MySQL MySQL
  • импортировать данные в базу данных PostgreSQL

Приложение работает успешно, но проблема возникает при попытке добавить новый контент в базу данных. Например, когда я запускаю задачу rake для обновления моего твиттера:

PG::Error: ERROR:  duplicate key value violates unique constraint "twitter_feeds_pkey" DETAIL:  Key (id)=(3) already exists.

Это также происходит для всех других моделей, создания новых статей, пользователей и т.д. В разработке я вижу, что публикация инструкции insert n + 1 раз успешно сохранит запись без ошибок.

Мой вопрос: Как сообщить PostgreSQL о том, чтобы начать индексирование последовательно из существующих данных?

Я прочитал страницу REINDEX, но не думаю, что это действительно операция, которую я ищу.

Ответы

Ответ 1

Если схема содержит последовательные или последовательные столбцы, вы должны reset их до максимального значения, которое встречается в соответствующем столбце. (обычно вам не нужно импортировать сериалы из файла, но дать им свободу автоинкремента.)

Для всех импортированных таблиц необходимо определить поля последовательности и запустить на них следующий код. (замените имя схемы для "sch", ваше имя таблицы для "mytable" и ваше имя столбца id для "id" )

WITH mx AS ( SELECT MAX(id) AS id FROM sch.mytable)
SELECT setval('sch.mytable_id_seq', mx.id) AS curseq
FROM mx
        ;

Ответ 2

В Rails вы можете использовать команду

ActiveRecord::Base.connection.reset_pk_sequence!('users')

чтобы снова добавить индекс первичного ключа для таблицы пользователя.

Ответ 3

Вы можете автоматизировать решение wildplasser, чтобы все последовательности были синхронизированы с текущим максимальным значением соответствующего столбца:

do
$block$
declare 
  r        record;
  stmt     text;
  max_id   integer;
begin
  for r in (
              select *
              from (
                select table_schema, 
                       table_name, 
                       column_name, 
                       pg_get_serial_sequence(table_schema||'.'||table_name, column_name) as col_sequence
                from information_schema.columns
                where table_schema not in ('pg_catalog', 'information_schema')
              ) t
              where col_sequence is not null
        ) 
  loop
    stmt := 'select coalesce(max('||r.column_name||'), 0) + 1 from '||r.table_schema||'.'||r.table_name;
    execute stmt into max_id;
    raise notice 'Next ID for %.%.% is %', r.table_schema, r.table_name, r.column_name, max_id;
    perform setval(r.col_sequence, max_id); 
  end loop;
end;
$block$

Обратите внимание, что это будет работать, только если столбцы были определены как serial, bigserial или были сделаны "владельцем" последовательности.

Ответ 4

Другой способ - удалить первичный ключ (id) из столбцов (или не сбрасывать идентификатор). Таким образом, ваши данные будут выглядеть как

INSERT INTO book (name, price) VALUES ('Alchemist' , 10);

вместо

INSERT INTO book (id, name, price) VALUES (1 , 'Alchemist' , 10);

Таким образом, после загрузки исходных данных вам не придется reset первичный ключ