我可以要求 Postgresql 忽略事务中的错误吗
我使用 Postgresql 和 PostGIS 扩展来进行临时空间分析。我通常在 psql 中手动构建和发出 SQL 查询。我总是将分析会话包装在事务中,因此如果我发出破坏性查询,我可以将其回滚。
但是,当我发出包含错误的查询时,它会取消事务。任何进一步的查询都会引发以下警告:
错误:当前事务是 中止,命令被忽略直到结束 交易区块
没有办法可以关闭此行为?每次输入错误时回滚事务并重新运行以前的查询是很烦人的。
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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(6)
(更新:不需要手动执行此操作,我在 postgresql 邮件列表中询问,结果表明此行为已经由 ON_ERROR_ROLLBACK 在 psql 客户端中设置)
为了详细说明 Simon 的答案(+1),在您的场景中,您可以在每个交互式查询之后通常添加一个保存点,始终具有相同的名称(如果查询成功,它会覆盖先前的名称)。如果出现错误,您可以返回到最后保存的位置并从那里继续。
此工作模式的一个示例:
(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)
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:
关闭它是不可能的,但是您可以使用不同的东西。有类似保存点的东西:
http://www.postgresql.org/docs /8.4/interactive/sql-savepoint.html
因此您可以将事务回滚到某个较早的点,而无需滚动整个事务。
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
so you can rollback your transaction to some earlier point without rolling black the whole transaction.
可以编写一个采用字符串参数的函数, 执行它,并使用例外子句是为了不中止您的事务,但是随后必须为您希望执行的每个语句调用该函数是一个巨大的痛苦。
It's possible to write a function that takes a string argument, executes it, and uses an exception clause 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.
不,没有办法关闭它。错误会隐式中止事务,因此您必须回滚并重试。
No, there is no way to turn this off. An error implicitly aborts the transaction for you, so you must rollback and try again.
简单的答案是
在交互式会话中运行。另请参阅其实现者的这篇博文。
The simple answer is to run
in the interactive session. See also this blog post by its implementor.
想要添加你可以忽略 postgres 本身内的错误。我将其用于唯一的键约束,因此我不必先检查某些内容是否存在。
Wanted to add you can ignore errors within postgres itself. I use this for unique key constraints so I don't have to check if something exists first.