在主键冲突错误后继续事务 [英] Continuing a transaction after primary key violation error

问题描述

我正在从日志文件批量插入记录到数据库中。偶尔（每千个中约有1行），其中一行违反主键，导致事务失败。目前，用户必须手动浏览导致失败的文件，并删除有问题的行，然后再尝试重新导入。考虑到要导入的这些文件有数百个是不切实际的。

I am doing a bulk insert of records into a database from a log file. Occasionally (~1 row out of every thousand) one of the rows violates the primary key and causes the transaction to fail. Currently, the user has to manually go through the file that caused the failure and remove the offending row before attempting to re-import. Given that there are hundreds of these files to import it is impractical.

我的问题：如何跳过插入将违反的记录主键约束，而不必在每行之前执行 SELECT 语句来查看它是否已经存在？

My question: How can I skip the insertion of records that will violate the primary key constraint, without having to do a SELECT statement before each row to see if it already exists?

注意：我知道非常相似的问题＃1054695 ，但它似乎是一个SQL Server特定的答案，我使用PostgreSQL（通过Python / psycopg2导入）。

Note: I am aware of the very similar question #1054695, but it appears to be a SQL Server specific answer and I am using PostgreSQL (importing via Python/psycopg2).

推荐答案

您还可以在事务中使用SAVEPOINT。

You can also use SAVEPOINTs in a transaction.

Pythonish伪代码从应用程序端说明：

Pythonish pseudocode is illustrate from the application side:

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")

Edit：下面是在psql中基于文档中的示例稍微变化的一个实际示例（以>为前缀的SQL语句）：

Here's an actual example of this in action in psql based on a slight variation of the example in the documentation (SQL statements prefixed by ">"):

> 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，但仍在同一个事务中！

Note that the value 3 was inserted after the error, but still inside the same transaction!

SAVEPOINT的文档位于 http://www.postgresql.org/ docs / 8.4 / static / sql-savepoint.html 。

The documentation for SAVEPOINT is at http://www.postgresql.org/docs/8.4/static/sql-savepoint.html.

这篇关于在主键冲突错误后继续事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！