我可以要求Postgresql忽略事务中的错误 [英] Can I ask Postgresql to ignore errors within a transaction

问题描述

我使用Postgresql与PostGIS扩展进行ad-hoc空间分析。我通常构造和发出SQL查询手工从psql。我总是在一个事务中封装一个分析会话，所以如果我发出一个破坏性的查询，我可以回滚。

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.

但是，当我发出一个包含错误的查询，它取消该事务。任何进一步的查询都会产生以下警告：

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.

推荐答案

（更新：无需重新启动这个手动，我在postgresql邮件列表中提出，并且它转向，这个行为已经实现，通过

(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_ROLLBACK set in the psql client)

要详细说明Simon的回答（+1），在你的场景中，你可以添加一个新的名字，例如： =noreferrer> 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.

这种工作模式的一个例子：

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)

这篇关于我可以要求Postgresql忽略事务中的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！