当单个约束失败时，如何告诉PostgreSQL不要中止整个事务？ [英] How can I tell PostgreSQL not to abort the whole transaction when a single constraint has failed?

问题描述

每当任何SQL语句以错误（包括任何约束冲突）终止时，Postgres都会自动中止事务。例如：

Postgres automatically aborts transactions whenever any SQL statement terminates with an error, which includes any constraint violation. For example:

glyph=# create table foo (bar integer, constraint blug check(bar > 5));
CREATE TABLE
glyph=# begin;
BEGIN
glyph=# insert into foo values (10);
INSERT 0 1
glyph=# insert into foo values (1);
ERROR:  new row for relation "foo" violates check constraint "blug"
STATEMENT:  insert into foo values (1);
ERROR:  new row for relation "foo" violates check constraint "blug"

没有消息尚未发布，但交易已回滚。我在本次会议上最喜欢的一行是：

No message has yet been issued to that effect, but the transaction is rolled back. My personal favorite line of this session is the following:

glyph=# commit;
ROLLBACK

...因为 ROLLBACK 对于 COMMIT 来说似乎是奇怪的成功消息。但是，实际上，它已经回滚了，表中没有行：

... since "ROLLBACK" seems like an odd success-message for COMMIT. But, indeed, it's been rolled back, and there are no rows in the table:

glyph=# select * from foo;
 bar 
-----
(0 rows)

我知道我可以创建大量的 SAVEPOINT 并以这种方式处理SQL中的错误，但这涉及到数据库的更多流量，更多的延迟（我可能必须处理毕竟来自 SAVEPOINT 的错误），收益相对较小。我真的只是想用 try / except 处理应用程序语言中的错误（Python），所以我想要从SQL中删除的唯一行为是错误而不是触发自动回滚。我该怎么办？

I know that I can create a ton of SAVEPOINTs and handle errors in SQL that way, but that involves more traffic to the database, more latency (I might have to handle an error from the SAVEPOINT after all), for relatively little benefit. I really just want to handle the error in my application language anyway (Python) with a try/except, so the only behavior I want out of the SQL is for errors not to trigger automatic rollbacks. What can I do?

推荐答案

我对PostgreSQL非常陌生，但是其中一个示例PostgreSQL文档中的触发器/服务器端编程看起来完全符合您的要求。

I'm extremely new to PostgreSQL, but one of the examples in the PostgreSQL documentation for triggers / server-side programming looks like it does exactly what you're looking for.

请参阅： http://www.postgresql.org/docs/9.2/static/trigger-example.html

页面中的代码段： 因此，触发器充当非空约束，但不会中止事务。

这篇关于当单个约束失败时，如何告诉PostgreSQL不要中止整个事务？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！