在主键违规错误后继续事务 [英] 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")

以下是 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屋！