存储过程是否在 Postgres 的数据库事务中运行? [英] Do stored procedures run in database transaction in Postgres?

问题描述

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

If a stored procedure fails in middle, are changes at that point from the beginning of SP rolled back implicitly or do we have to write any explicit code to make sure that SP runs in a database transaction only?

推荐答案

严格来说，Postgres 没有 存储过程，在版本 11 之前的 ISO/IEC 标准中定义.该术语经常被错误地用于指代 函数，它们提供了许多相同的功能(以及更多)，因为其他 RDBMS 提供了存储过程".主要区别在于事务处理.

Strictly speaking, Postgres did not have stored procedures as defined in the ISO/IEC standard before version 11. The term is often used incorrectly to refer to functions, which provide much of the same functionality (and more) as other RDBMS provide with "stored procedures". The main difference being transaction handling.

• 存储过程"和存储函数"有什么区别?

真正的存储过程是最终在 Postgres 11 中引入:

True stored procedures were finally introduced with Postgres 11:

• 何时使用存储过程/用户定义函数?

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

Functions are atomic in Postgres and automatically run inside their own transaction unless called within an outer transaction. They always run inside a single transaction and succeed or fail completely. Consequently, one cannot begin or commit transactions within the function. And commands like VACUUM, CREATE DATABASE, or CREATE INDEX CONCURRENTLY which do not run in a transaction context are not allowed.

PL/pgSQL 手册:

函数和触发过程总是在一个由外部查询建立的事务——它们不能启动或提交该事务，因为他们没有上下文执行.然而，一个包含 EXCEPTION 子句的块有效地形成一个子事务，可以在不回滚的情况下回滚影响外部事务.

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.

错误处理:

默认情况下，PL/pgSQL 函数中发生的任何错误都会中止函数的执行，以及周围事务的执行以及.您可以使用 BEGIN 捕获错误并从中恢复带有 EXCEPTION 子句的块.

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.

有例外，包括但不限于:

• 写入日志文件的数据

• data written to log files

对序列所做的更改

重要:某些 PostgreSQL 数据类型和函数有特殊规则关于交易行为.特别是，对一个序列(因此是使用 serial 声明的列的计数器)对所有其他事务立即可见并且不会滚动如果进行更改的事务中止，则返回.

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 调用(或类似调用)

dblink calls (or similar)

• Postgres 是否支持嵌套或自主事务?

这篇关于存储过程是否在 Postgres 的数据库事务中运行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！