postgresql - 强制 PostgreSQL 在函数中使用不同的模式

Question

我想为 PG SQL 数据库中的模式创建一个更新函数。下面是一个测试函数。它不起作用，因为它永远不会发出通知，但会在运行 test_schema_update('second') 时发出通知。

CREATE OR REPLACE FUNCTION test_schema_update(my_schema_name VARCHAR(200)) 
RETURNS void AS
$__marker__$
DECLARE
    actualValue varchar(1000);
    testValue varchar(1000);
BEGIN
    EXECUTE 'SET search_path TO ' || quote_ident(my_schema_name);

    testValue := (SELECT max(value) FROM setting WHERE settingkey = 'libraryname');
    EXECUTE ('SELECT max(value) FROM setting WHERE settingkey = ''libraryname''')
        INTO actualValue;

    IF (actualValue != testValue)
    THEN
        RAISE NOTICE '% != %', actualValue, testValue;
        RAISE INFO 'Schema was: %', current_schema();
    END IF;

    RESET search_path;
END;
$__marker__$ LANGUAGE plpgsql;

test_schema_update('first');
test_schema_update('second');

问题是 PG SQL 似乎每个 session 只分析一次 SELECT 语句，然后表被固定到特定的模式。有趣的是，您将得到 Schema was: second。

那么有没有办法重置 SELECT 语句分析或其他一些方法来解决这个问题？

旁注:所有模式创建函数(ALTER TABLE、CREATE TABLE...)工作正常。似乎只有数据操作函数受到影响(SELECT、INSERT、UPDATE)。

解决方法

之前:

IF (
    SELECT max(id) FROM dimtime
)
THEN
    INSERT INTO dimtime SELECT * FROM public.src_dimtime;
END IF;

之后:

EXECUTE ('
    SELECT max(id) FROM dimtime
')
INTO testInt;
IF (testInt IS NULL)
THEN
    EXECUTE 'INSERT INTO dimtime SELECT * FROM public.src_dimtime';
END IF;

编辑:问题出现在PostgreSQL 9.2，但似乎不会出现在9.3。也许它是固定的？

Answer 1

这种行为是意料之中的。原因是 PL/pgSQL 对 SQL 语句使用计划缓存，在内部使用标准的准备好的语句。

Per documentation:

As each expression and SQL command is first executed in the function, the PL/pgSQL interpreter creates a prepared execution plan (using the SPI manager's SPI_prepare and SPI_saveplan functions). Subsequent visits to that expression or command reuse the prepared plan.

这也是为什么 plpgsql 函数对于复杂操作通常更快的原因:

• Difference between language sql and language plpgsql in PostgreSQL functions

准备好的语句在 session 的生命周期内保存，而不仅仅是事务(但在底层对象更改时失效，这对于并发访问是安全的)。 The documentation once again:

Once PL/pgSQL has made an execution plan for a particular command in a function, it will reuse that plan for the life of the database connection. This is usually a win for performance, but it can cause some problems if you dynamically alter your database schema.

大胆强调我的。

如果您想“更改”表名的架构，您实际上将引用一个完全不同的表并且需要使用动态 SQL 和 EXECUTE，每次都会生成一个新计划(具有所有优点和缺点):

Because PL/pgSQL saves execution plans in this way, SQL commands that appear directly in a PL/pgSQL function must refer to the same tables and columns on every execution; that is, you cannot use a parameter as the name of a table or column in an SQL command. To get around this restriction, you can construct dynamic commands using the PL/pgSQL EXECUTE statement — at the price of constructing a new execution plan on every execution.

阅读手册中的引用章节。它非常全面。

代码示例

您添加的代码示例不需要动态 SQL，单个语句会更快:

INSERT INTO dimtime  -- you may want list columns
SELECT *             -- here as well
FROM   public.src_dimtime
WHERE  NOT EXISTS (SELECT 1 FROM dimtime);