为什么PL/pgSQL函数会有副作用，而SQL函数却没有? [英] Why can PL/pgSQL functions have side effect, while SQL functions can't?

问题描述

PostgreSQL文档说:

SQL函数的整个主体在执行任何函数之前都会被解析.尽管SQL函数可以包含个命令，这些命令会更改 系统目录(例如CREATE TABLE)，这些命令的效果 在后面的命令的解析分析过程中将不可见 功能.因此，例如，CREATE TABLE foo (...); INSERT INTO foo VALUES(...); 如果打包为一个，将无法按预期工作 SQL函数，因为解析INSERT命令时foo尚不存在.

建议使用PL/pgSQL代替SQL函数 在这种情况下.

• 为什么建议在这种情况下使用PL/pgSQL代替SQL函数"，其中PL/pgSQL或SQL函数包含更改系统目录的命令，例如CREATE TABLE foo (...); INSERT INTO foo VALUES(...);吗? /p>

• 在执行任何SQL函数之前，先对其进行分析". PL/pgSQL函数不是真的吗?在解析和执行其主体中的命令方面，SQL函数和PL/pgSQL函数之间有什么区别?

解决方案

您加粗了> 解析器阶段的信息在手册中.

它由两个主要部分组成:解析器和转换过程. 引用手册:

转换过程将解析器返回的树作为 输入并进行语义解释以了解哪个 查询引用表，函数和运算符.

如果SQL函数包含以下命令:

CREATE TABLE foo (...);
INSERT INTO foo VALUES(...);

两个语句实际上是在同一时间计划的(基于系统目录的相同快照).因此，INSERT无法看到由上一个CREATE命令创建的表"foo".这会产生以下问题之一:

1. 如果没有名为"foo"的 other 表

2. 如果search_patch中已经存在另一个名为"foo"的表(并且您没有使用冲突的列名)，则Postgres将基于该表来计划INSERT.如果任何值导致(错误！)表中的冲突，通常会在执行时出现错误.或者，如果运气不好，它甚至可能在没有错误消息的情况下写入该表！非常狡猾的错误.

使用 PL/pgSQL 函数不会发生这种情况，因为它会将SQL命令像已准备好的语句一样处理，并按 顺序计划和执行.因此，每个语句都可以查看先前语句中创建的对象.

因此，从未访问过的语句甚至都不会被计划-与SQL函数不同.语句的执行计划可以缓存在同一会话中-与SQL函数不同. 阅读有关PL/pgSQL函数中计划缓存的详细信息在此处的手册中.
对于某些用例，每种方法都具有优势.进一步阅读:

• 差异PostgreSQL函数中的语言sql和语言plpgsql之间

PostgreSQL document says:

The entire body of a SQL function is parsed before any of it is executed. While a SQL function can contain commands that alter the system catalogs (e.g., CREATE TABLE), the effects of such commands will not be visible during parse analysis of later commands in the function. Thus, for example, CREATE TABLE foo (...); INSERT INTO foo VALUES(...); will not work as desired if packaged up into a single SQL function, since foo won't exist yet when the INSERT command is parsed.

It's recommended to use PL/pgSQL instead of a SQL function in this type of situation.

• Why "It's recommended to use PL/pgSQL instead of a SQL function in this type of situation", where the PL/pgSQL or SQL function contains commands that alter the system catalogs, such as CREATE TABLE foo (...); INSERT INTO foo VALUES(...); ?

• "The entire body of a SQL function is parsed before any of it is executed". Is it not true for a PL/pgSQL function? What differences are between SQL functions and PL/pgSQL functions, in terms of parsing and executing the commands in their bodies?

解决方案

You bolded the key sentence in the manual yourself:

The entire body of a SQL function is parsed before any of it is executed.

Also read about The Parser Stage in the manual.

It consists of two major parts: the parser and the transformation process. Quoting the manual:

the transformation process takes the tree handed back by the parser as input and does the semantic interpretation needed to understand which tables, functions, and operators are referenced by the query.

If an SQL function contains these commands:

CREATE TABLE foo (...);
INSERT INTO foo VALUES(...);

Both statements are planned at virtually the same time (based on the same snapshot of the system catalogs). Hence, the INSERT cannot see the table "foo" presumably created with the previous CREATE command. That creates one of the following problems:

1. If there is no other table named "foo" in your search_patch (yet), Postgres complains when trying to create the function:

   ERROR:  relation "foo" does not exist
   

2. If another table named "foo" already exists in your search_patch (and you don't use conflicting column names), Postgres will plan the INSERT based on that pre-existing table. Typically that results in an error at execution time, if any values cause conflicts in the (wrong!) table. Or, with some bad luck, it might even write to that table without error message! Very sneaky bug.

That cannot happen with a PL/pgSQL function, because it treats SQL commands like prepared statements, planned and executed sequentially. So each statement can see objects created in previous statements.

Consequently, statements that are never visited are never even planned - unlike with SQL functions. And the execution plan for statements can be cached within the same session - also unlike SQL functions. Read details about plan caching in PL/pgSQL functions in the manual here.
Each approach has advantages for some use cases. Further reading:

• Difference between language sql and language plpgsql in PostgreSQL functions

这篇关于为什么PL/pgSQL函数会有副作用，而SQL函数却没有?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！