存储过程是否在Postgres中的数据库事务中运行？

如果存储过程在中间失败,那么从SP开始的那一点的更改是否隐式回滚,或者我们是否必须编写任何显式代码以确保SP仅在数据库事务中运行？

解决方法

> 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?

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!