存储过程是否在Postgres的数据库事务中运行？ [英] Do stored procedures run in database transaction in 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

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