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

Question

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

Answer 1

严格来说，Postgres 没有 stored procedures如版本 11 之前的 ISO/IEC 标准中所定义。该术语经常被错误地用于指代 functions ，它提供了很多与其他 RDBMS 提供的“存储过程”相同的功能(甚至更多)。主要区别在于事务处理。

• What are the differences between “Stored Procedures” and “Stored Functions”?

真stored procedures最终在 Postgres 11 中引入:

• When to use stored procedure / user-defined function?

Functions在 Postgres 中是 atomic 并且在它们自己的事务中自动运行，除非在外部事务中调用。 它们总是在单个事务中运行并且完全成功或失败。因此，不能在函数内开始或提交事务。不允许不在事务上下文中运行的命令，如 VACUUM、CREATE DATABASE 或 CREATE INDEX CONCURRENTLY。

The manual on PL/pgSQL:

Functions and trigger procedures are always executed within atransaction established by an outer query — they cannot start orcommit that transaction, since there would be no context for them toexecute in. However, a block containing an EXCEPTION clauseeffectively forms a subtransaction that can be rolled back withoutaffecting the outer transaction.

Error handling:

By default, any error occurring in a PL/pgSQL function abortsexecution of the function, and indeed of the surrounding transactionas well. You can trap errors and recover from them by using a BEGINblock with an EXCEPTION clause.

存在异常(exception)情况，包括但不限于:

• 写入日志文件的数据

• changes made to a sequence

  Important: Some PostgreSQL data types and functions have special rulesregarding transactional behavior. In particular, changes made to asequence (and therefore the counter of a column declared using serial)are immediately visible to all other transactions and are not rolledback if the transaction that made the changes aborts.

• 准备好的语句
  SQL Fiddle演示

• dblink 调用(或类似)

  • Does Postgres support nested or autonomous transactions?