我可以要求 Postgresql 忽略事务中的错误吗
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2741919/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can I ask Postgresql to ignore errors within a transaction
提问by fmark
I use Postgresql with the PostGIS extensions for ad-hoc spatial analysis. I generally construct and issue SQL queries by hand from within psql. I always wrap an analysis session within a transaction, so if I issue a destructive query I can roll it back.
我将 Postgresql 与 PostGIS 扩展一起用于临时空间分析。我通常从 psql 中手动构建和发出 SQL 查询。我总是在一个事务中包装一个分析会话，所以如果我发出一个破坏性的查询，我可以回滚它。
However, when I issue a query that contains an error, it cancels the transaction. Any further queries elicit the following warning:
但是，当我发出包含错误的查询时，它会取消事务。任何进一步的查询都会引起以下警告：
ERROR: current transaction is aborted, commands ignored until end of transaction block
错误：当前事务被中止，命令被忽略，直到事务块结束
Is there a way I can turn this behaviour off? It is tiresome to rollback the transaction and rerun previous queries every time I make a typo.
有没有办法可以关闭这种行为？每次我打错字时，回滚事务并重新运行以前的查询是很烦人的。
采纳答案by leonbloy
(UPDATE: No need to to this by hand, I asked in the postgresql mailing lists, and it turned that this behaviour is already implemented, by the ON_ERROR_ROLLBACKset in the psql client)
（更新：无需手动执行此操作，我在 postgresql 邮件列表中询问，结果发现此行为已通过psql 客户端中设置的ON_ERROR_ROLLBACK实现）
To elaborate on Simon's answer (+1) , in your scenario you could rutinarily add a savepoint after each interactive query, always with the same name (it ovewrites the previous if the query is succesfull). In the case of error, you go back to the last saved one and continue from there.
为了详细说明 Simon 的答案 (+1) ，在您的场景中，您可以在每个交互式查询之后粗暴地添加一个保存点，始终使用相同的名称（如果查询成功，它会覆盖前一个）。如果出现错误，您将返回到上次保存的并从那里继续。
An example of this working pattern:
这种工作模式的一个例子：
db=# select * from test_gral ;
i | t | n
---+------+------
1 | text | 10.0
(1 row)
db=# begin;
BEGIN
db=# insert into test_gral values (2,'xx',20); savepoint sp;
INSERT 0 1
SAVEPOINT
db=# insert into test_gral values (3,'xx',30); savepoint sp;
INSERT 0 1
SAVEPOINT
db=# insert into test_gralxx values (4,'xx',40); savepoint sp;
ERROR: relation "test_gralxx" does not exist
LINE 1: insert into test_gralxx values (4,'xx',40);
^
ERROR: current transaction is aborted, commands ignored until end of transaction block
db=# ROLLBACK TO SAVEPOINT sp;
ROLLBACK
db=# insert into test_gral values (4,'xx',40); savepoint sp;
INSERT 0 1
SAVEPOINT
db=# commit;
COMMIT
db=# select * from test_gral ;
i | t | n
---+------+------
1 | text | 10.0
2 | xx | 20
3 | xx | 30
4 | xx | 40
(4 rows)
回答by Szymon Lipiński
Switching that off is not possible however you can use something different. There is something like savepoint:
关闭它是不可能的，但是你可以使用不同的东西。有类似保存点的东西：
http://www.postgresql.org/docs/8.4/interactive/sql-savepoint.html
http://www.postgresql.org/docs/8.4/interactive/sql-savepoint.html
so you can rollback your transaction to some earlier point without rolling black the whole transaction.
因此您可以将事务回滚到某个较早的时间点，而无需将整个事务滚黑。
回答by Stephen Denne
It's possible to write a function that takes a string argument, executesit, and uses an exceptionclause in order to not abort your transaction, but it's a huge pain to then have to call that function for each statement you wish to execute.
可以编写一个函数，该函数接受一个字符串参数，执行它，并使用一个异常子句来不中止您的事务，但是然后必须为您希望执行的每个语句调用该函数是一个巨大的痛苦。
回答by Yann Ramin
No, there is no way to turn this off. An error implicitly aborts the transaction for you, so you must rollback and try again.
不，没有办法关闭它。错误会为您隐式中止事务，因此您必须回滚并重试。
回答by Dan
The simple answer is to run
简单的答案是运行
my_db=> \set ON_ERROR_ROLLBACK interactive
in the interactive session. See also this blog postby its implementor.
在互动环节。另请参阅其实现者的这篇博客文章。
