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

问题描述

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

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

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

解决方案

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

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）
 
如果error_count> error_threshold：
 database.execute（ROLLBACK）
 else：
 database.execute（COMMIT）
  

编辑：以下是基于文档中的示例（以>为前缀的SQL语句）的略微变化的psql中的实际示例：

 > CREATE TABLE table1（test_field INTEGER NOT NULL PRIMARY KEY）; 
注意：CREATE TABLE / PRIMARY KEY将为表table1创建隐式索引table1_pkey
 CREATE TABLE 
 
>开始; 
 BEGIN 
> INSERT INTO table1 VALUES（1）; 
 INSERT 0 1 
> SAVEPOINT my_savepoint; 
 SAVEPOINT 
> INSERT INTO table1 VALUES（1）; 
错误：重复键值违反唯一约束table1_pkey
> ROLLBACK TO SAVEPOINT my_savepoint; 
 ROLLBACK 
> INSERT INTO table1 VALUES（3）; 
 INSERT 0 1 
>承诺; 
 COMMIT 
> SELECT * FROM table1; 
 test_field 
 ------------ 
 1 
 3 
（2行）
  
 
 
 
请注意，值3插入在错误之后，但仍在同一个事务中！
 
 
 
 SAVEPOINT的文档位于 http://www.postgresql.org /docs/8.4/static/sql-savepoint.html 。
 
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.

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?

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).
解决方案
You can also use SAVEPOINTs in a transaction.

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:  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)
Note that the value 3 was inserted after the error, but still inside the same transaction!

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

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