主键冲突后继续事务

Question

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

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

注意：我知道有一个非常类似的问题#1054695，但它似乎是一个特定于SQL服务器的答案，我正在使用PostgreSQL（通过Python/psycopg2导入）。

Answer 1

您可以对事务执行rollback，也可以在引发异常的代码之前回滚到保存点（cr是游标）：

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后端creates cursors直接来自psycopg。也许将来他们会为Django游标创建一个代理类，类似于cursor of odoo。它们用following code扩展游标（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 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

您还可以在事务中使用保存点。

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。