Продолжение транзакции после ошибки нарушения первичного ключа
Я делаю объемную вставку записей в базу данных от файла журнала. Иногда (~1 строка из каждой тысячи) одна из строк нарушает первичный ключ и заставляет транзакцию перестать работать. В настоящее время пользователь должен вручную пройти файл, который вызвал отказ, и удалите незаконную строку прежде, чем попытаться повторно импортировать. Учитывая, что существуют сотни этих файлов для импорта его, непрактично.
Мой вопрос: Как я могу пропустить вставку записей, которые нарушат ограничение первичного ключа, не имея необходимость делать a SELECT оператор перед каждой строкой, чтобы видеть, существует ли это уже?
Примечание: Я знаю об очень похожем вопросе № 1054695, но это, кажется, определенный для SQL Server ответ, и я использую PostgreSQL (импортирующий через Python/psycopg2).
3 ответа
Вы также можете использовать SAVEPOINT в транзакции.
Питонский псевдокод проиллюстрирован со стороны приложения:
database.execute("BEGIN")
foreach data_row in input_data_dictionary:
database.execute("SAVEPOINT bulk_savepoint")
try:
database.execute("INSERT", table, data_row)
except:
database.execute("ROLLBACK TO SAVEPOINT bulk_savepoint")
log_error(data_row)
error_count = error_count + 1
else:
database.execute("RELEASE SAVEPOINT bulk_savepoint")
if error_count > error_threshold:
database.execute("ROLLBACK")
else:
database.execute("COMMIT")
Изменить: вот реальный пример этого в действии в psql, основанный на небольшом изменении примера в документации (операторы SQL с префиксом ">"):
> CREATE TABLE table1 (test_field INTEGER NOT NULL PRIMARY KEY);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "table1_pkey" for table "table1"
CREATE TABLE
> BEGIN;
BEGIN
> INSERT INTO table1 VALUES (1);
INSERT 0 1
> SAVEPOINT my_savepoint;
SAVEPOINT
> INSERT INTO table1 VALUES (1);
ERROR: duplicate key value violates unique constraint "table1_pkey"
> ROLLBACK TO SAVEPOINT my_savepoint;
ROLLBACK
> INSERT INTO table1 VALUES (3);
INSERT 0 1
> COMMIT;
COMMIT
> SELECT * FROM table1;
test_field
------------
1
3
(2 rows)
Обратите внимание, что значение 3 было вставлено после ошибки, но все еще внутри той же транзакции!
Документация по SAVEPOINT находится по адресу http://www.postgresql.org/docs/8.4/static/sql-savepoint.html .
Я бы использовал хранимую процедуру, чтобы перехватывать исключения для ваших уникальных нарушений. Пример:
CREATE OR REPLACE FUNCTION my_insert(i_foo text, i_bar text)
RETURNS boolean LANGUAGE plpgsql AS
$BODY$
begin
insert into foo(x, y) values(i_foo, i_bar);
exception
when unique_violation THEN -- nothing
return true;
end;
$BODY$;
SELECT my_insert('value 1','another value');
Или вы можете использовать SSIS и указать, что для неудачных строк путь отличается от пути успешных.
Если вы используете другую базу данных, можете ли вы массово вставить файлы в промежуточную таблицу, а затем использовать код SQL для выбора только тех записей, у которых нет существующего идентификатора?