Продолжение транзакции после ошибки нарушения первичного ключа

Question

Я делаю массовую вставку записей в базу данных из файла журнала. Иногда (~1 строка из каждой тысячи) одна из строк нарушает первичный ключ и вызывает сбой транзакции. В настоящее время пользователь должен вручную просмотреть файл, вызвавший сбой, и удалить строку, вызвавшую сбой, перед попыткой повторного импорта. Учитывая, что есть сотни таких файлов для импорта, это нецелесообразно.

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

Примечание. Мне известен очень похожий вопрос # 1054695, но, похоже, это специфический ответ для SQL Server, и я использую PostgreSQL (импорт через Python/psycopg2).

Answer 1

Вы также можете использовать SAVEPOINTs в транзакции.

Pythonish псевдокод является иллюстрацией со стороны приложения:

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.

Answer 2

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

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');

Answer 3

Вы можете сделать rollback к транзакции или откат к точке сохранения непосредственно перед кодом, который вызывает исключение (курсор является кр):

name = uuid.uuid1().hex
cr.execute('SAVEPOINT "%s"' % name)
try:
    # your failing query goes here
except Exception:
    cr.execute('ROLLBACK TO SAVEPOINT "%s"' % name)
    # your alternative code goes here 
else:
    cr.execute('RELEASE SAVEPOINT "%s"' % name)

Этот код предполагает, что выполняется транзакция, в противном случае вы не получили бы это сообщение об ошибке.

Бэкэнд Django postgresql создает курсоры непосредственно из psycopg. Возможно, в будущем они создадут прокси-класс для курсора Django, аналогичный курсору odoo. Они расширяют курсор следующим кодом (self - курсор):

@contextmanager
@check
def savepoint(self):
    """context manager entering in a new savepoint"""
    name = uuid.uuid1().hex
    self.execute('SAVEPOINT "%s"' % name)
    try:
        yield
    except Exception:
        self.execute('ROLLBACK TO SAVEPOINT "%s"' % name)
        raise
    else:
        self.execute('RELEASE SAVEPOINT "%s"' % name)

Таким образом, контекст делает ваш код проще, он будет:

try:
    with cr.savepoint():
        # your failing query goes here
except Exception:
    # your alternative code goes here 

и код более читабелен, потому что там нет транзакций.

Answer 4

Или вы можете использовать службы SSIS, чтобы ошибочные строки проходили по другому пути, чем успешные.

Так как вы используете другую базу данных, можете ли вы массово вставить файлы в промежуточную таблицу, а затем использовать код SQL, чтобы выбрать только те записи, которые не имеют существующего идентификатора?