如果存储过程在中间失败,那么从SP开始的那一点的更改是否隐式回滚,或者我们是否必须编写任何显式代码以确保SP仅在数据库事务中运行？
解决方法
严格来说,Postgres目前(不包括版本10)具有ANSI标准中定义的“存储过程”.一切都是通过“函数”完成的,它们提供了与其他RDBMS提供的存储过程相同的功能(和更多).主要区别在于交易处理.
> What are the differences between “Stored Procedures” and “Stored Functions”?
Postgres 11终于推出了真正的stored procedures：
> When to use stored procedure / user-defined function?
Functions在Postgres中是原子的,并且在自己的事务中自动运行,除非在外部事务中调用.它们总是在单个事务中运行,并且完全成功或失败.因此,无法在函数内开始或提交事务.并且不允许在事务块中运行的VACUUM或CREATE INDEX CONCURRENTLY等命令.
Per documentation on PL/pgSQL:
Functions and trigger procedures are always executed within a
transaction established by an outer query — they cannot start or
commit that transaction,since there would be no context for them to
execute in. However,a block containing an EXCEPTION clause
effectively forms a subtransaction that can be rolled back without
affecting the outer transaction.
Error handling:
By default,any error occurring in a PL/pgSQL function aborts
execution of the function,and indeed of the surrounding transaction
as well. You can trap errors and recover from them by using a BEGIN
block with an EXCEPTION clause.
有特殊例外,包括但不限于：
>写入日志文件的数据
> changes made to a sequence
Important: Some PostgreSQL data types and functions have special rules
regarding transactional behavior. In particular,changes made to a
sequence (and therefore the counter of a column declared using serial)
are immediately visible to all other transactions and are not rolled
back if the transaction that made the changes aborts.
>准备好的陈述
> SQL Fiddle演示
> dblink调用(或类似)
> Does Postgres support nested or autonomous transactions? （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
