为PostgreSQL存储过程设置隔离级别 [英] set isolation level for postgresql stored procedures

问题描述

希望这是一个简单的问题，但是我还没有找到一个不错的答案。我已可靠地得知PostgreSQL（特别是9.0.4版）中的存储过程（用户定义的DB函数）本质上是事务性的，因为它们是通过本身是事务的SELECT语句调用的。那么如何选择存储过程的隔离级别呢？我相信在其他DBMS中，所需的事务性块将包装在START TRANSACTION块中，对于该块，所需的隔离级别是可选参数。

Hopefully a simple question, but one for which I haven't readily found a decent answer. I'm reliably informed that stored procedures (user-defined DB functions) in PostgreSQL (specifically, version 9.0.4) are inherently transactional, inasmuch as they are called through a SELECT statement which itself is a transaction. So how does one choose the isolation level of the stored procedure? I believe in other DBMSs the desired transactional block would be wrapped in a START TRANSACTION block for which the desired isolation level is an optional parameter.

作为一个具体的示例，说我想这样做：

As a specific made-up example, say I want to do this:

CREATE FUNCTION add_new_row(rowtext TEXT)
RETURNS VOID AS 
$$
BEGIN
        INSERT INTO data_table VALUES (rowtext);
        UPDATE row_counts_table SET count=count+1;
END;
$$  
LANGUAGE plpgsql
SECURITY DEFINER;

并且想像一下，我想确保此功能始终作为可序列化的事务执行（是的，是的， PostgreSQL SERIALIZABLE不能正确序列化，但这不是重点。我不想要求将其称为

And imagine I want to make sure this function is always performed as a serializable transaction (yes, yes, PostgreSQL SERIALIZABLE isn't proper serializable, but that's not the point). I don't want to require it to be called as

START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT add_new_row('foo');
COMMIT;

那么如何将所需的隔离级别下推到函数中呢？我相信我不能只将隔离级别放在 BEGIN 语句中，如手册说

So how do I push the required isolation level down into the function? I believe I cannot just put the isolation level in the BEGIN statement, as the manual says

重要的是不要混淆$ b的使用BEGIN / END的$ b用于将PL / pgSQL中的语句
分组，并使用类似的
SQL命令进行事务控制。
PL / pgSQL的BEGIN / END仅用于
分组；他们不会开始或结束
交易。函数和触发器
过程始终在外部
查询建立的
a事务内执行-它们无法启动或提交
该事务，因为没有上下文的

It is important not to confuse the use of BEGIN/END for grouping statements in PL/pgSQL with the similarly-named SQL commands for transaction control. PL/pgSQL's BEGIN/END are only for grouping; they do not start or end a transaction. 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.

对我来说，最明显的方法是使用 SET TRANSACTION 在函数定义中的某个位置，例如：

The most obvious approach to me would be to use SET TRANSACTION somewhere in the function definition, e.g.,:

CREATE FUNCTION add_new_row(rowtext TEXT)
RETURNS VOID AS 
$$
BEGIN
        SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
        INSERT INTO data_table VALUES (rowtext);
        UPDATE row_counts_table SET count=count+1;
END;
$$  
LANGUAGE plpgsql
SECURITY DEFINER;

虽然可以接受，但尚不清楚我是否可以依靠它来工作。 SET TRANSACTION的文档 说

While this would be accepted, it's not clear than I can rely on this to work. The documentation for SET TRANSACTION says

如果在开始交易或开始前没有
a的情况下执行SET TRANSACTION，则
似乎无效，因为
交易将立即结束。

If SET TRANSACTION is executed without a prior START TRANSACTION or BEGIN, it will appear to have no effect, since the transaction will immediately end.

这让我感到困惑，因为如果我调用一个单独的 SELECT add_new_row（'foo'）; 语句，我希望（假设我没有禁用自动提交），SELECT将作为单行事务与会话默认隔离级别。

Which leaves me puzzled, since if I call a solitary SELECT add_new_row('foo'); statement I would expect (provided I haven't disabled autocommit) the SELECT to be running as a single-line transaction with the session default isolation level.

手册也说：

在第一个操作之后，不能更改事务隔离级别
查询或
数据修改语句（SELECT，
INSERT，DELETE，UPDATE，FE TCH或
COPY）已执行
。

The transaction isolation level cannot be changed after the first query or data-modification statement (SELECT, INSERT, DELETE, UPDATE, FETCH, or COPY) of a transaction has been executed.

如果函数是从较低隔离级别的事务中调用，例如：

So what happens if the function is called from within a transaction with a lower isolation level, e.g.,:

START TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE row_counts_table SET count=0;
SELECT add_new_row('foo');
COMMIT;

一个奖励问题：函数的语言有什么区别吗？在PL / pgSQL中设置隔离级别与在普通SQL中设置隔离级别会有所不同吗？

For a bonus question: does the language of the function make any difference? Would one set the isolation level differently in PL/pgSQL than in plain SQL?

我是标准和有据可查的最佳实践的拥护者，因此，任何体面的参考文献都将不胜感激。

I'm a fan of standards and documented best practices, so any decent references would be appreciated.

推荐答案

您不能这样做。

您可以做的是让您的函数检查当前的事务隔离级别并中止。如果不是您想要的那个。您可以通过运行 SELECT current_setting（'transaction_isolation'）然后检查结果来完成此操作。

What you could do is have your function check what the current transaction isolation level is and abort if it's not the one you want. You can do this by running SELECT current_setting('transaction_isolation') and then checking the result.

这篇关于为PostgreSQL存储过程设置隔离级别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！