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

本文介绍了存储过程是否在Postgres的数据库事务中运行？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

$严格来说，Postgres目前不支持（包括版本9.5）有存储过程，如第ANSI标准。一切都由函数完成，它提供了与其他RDBMS提供的存储过程几乎相同的功能（和更多）。主要区别在于交易处理。

• 存储过程和存储函数之间有什么区别？

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

有关PL / pgSQL的每篇文档：

函数和触发器过程总是在外部查询建立的事务中执行 - 它们无法启动或提交该事务，因为它们没有上下文给执行。但是，一个包含 EXCEPTION 子句的块形成子事务，可以回滚而不影响外部事务的。

错误处理：

默认情况下，在PL / pgSQL函数中发生的任何错误都会中止执行该函数，事实上还会执行周围的事务。您可以通过使用 BEGIN 块和 EXCEPTION 子句来捕获错误并从中恢复。 p>

有特殊例外，包括但不限于：

• 写入日志文件的数据

• 对序列进行的更改

  ：某些PostgreSQL数据类型和函数有关于事务行为的特殊规则。特别地，对序列（以及因此使用 serial ）声明的列的计数器对所有其他事务立即可见，并且

• 预备语句

  • SQL Fiddle 演示

• dblink呼叫

  • Postgres支持嵌套事务或自主属性事务

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?

解决方案

Strictly speaking Postgres does not currently (up to and including version 9.5) have "stored procedures" as defined in the ANSI standard. Everything is done with "functions" instead, which provide almost the same functionality (and more) as other RDBMS provide with stored procedures. The main difference being transaction handling.

• What are the differences between "Stored Procedures" and "Stored Functions"?

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 or CREATE INDEX CONCURRENTLY which cannot run within a transaction block are not allowed.

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.

There are special exceptions, including but not limited to:

• data written to log files

• 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.

• prepared statements

  • SQL Fiddle demo

• dblink calls

  • Postgres supports nested transaction or autonomus transactions or not