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

如果存储过程在中间失败,那么从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?