在 ON UPDATE 规则中执行 Postgresql 子查询(可能是 postgresql 中的错误?) [英] Postgresql subquery execution within ON UPDATE rule (maybe bug in postgresql?)

问题描述

在 postgresql 的规则内子查询的执行顺序中，我遇到了一个奇怪的行为(或者这是 postgresql 中的错误?).考虑以下 SQL:

I am running into a strange behavior (or is this a bug within postgresql?) within postgresql's execution order of subqueries within rules. Consider the following SQL:

BEGIN;

CREATE OR REPLACE FUNCTION debug(anyelement) RETURNS bool AS $$

pg_raise('notice', 'debug(): ' . json_encode($args[0]));

RETURN TRUE;

$$ LANGUAGE PLPHP IMMUTABLE STRICT;

CREATE TABLE foo_table (c1 text);

CREATE OR REPLACE RULE foo_update_rule AS ON UPDATE TO foo_table DO INSTEAD
(
    WITH foobar_update AS
    (
        SELECT unnest('{a,b}'::text[]) AS _value, debug('update_inner'::text)
    )
    SELECT *, debug('update_outer_1'::text), debug('update_outer_2 -> '::text || _value::text) FROM foobar_update;


SELECT

        ( ROW(FALSE,FALSE) IN ( SELECT 
                        debug('update2_outer_1'::text), debug('update2_outer_2 -> '::text || _value::text)
                   FROM ( SELECT unnest('{a,b}'::text[]) AS _value, debug('update_inner'::text) ) AS foobar_update2     ))

);

-----------------------------------------------

WITH foobar_select AS
(
    SELECT unnest('{a,b}'::text[]) AS _value, debug('select_inner'::text)
)
SELECT *, debug('select_outer_1'::text), debug('select_outer_2 -> '::text || _value::text), debug('select_outer_3'::text) FROM foobar_select;

UPDATE foo_table SET c1 = NULL where c1 = 'aaa';

ROLLBACK;

上面的代码在执行时会产生如下输出:

The above code when executed generates the following output:

NOTICE:  plphp: debug(): "select_inner"
NOTICE:  plphp: debug(): "select_outer_1"
NOTICE:  plphp: debug(): "select_outer_3"
NOTICE:  plphp: debug(): "select_outer_2 -> a"
NOTICE:  plphp: debug(): "select_outer_2 -> b"
NOTICE:  plphp: debug(): "update_inner"
NOTICE:  plphp: debug(): "update_outer_1"
NOTICE:  plphp: debug(): "update2_outer_1"
NOTICE:  plphp: debug(): "update_inner"

从输出中可以看出，问题在于子查询(又名内部")是在 foo_update_rule 中的 2 个 SELECT 查询中的引用(又名外部")查询之后执行的.结果，在评估外部查询时尚未定义 _value 列(在子查询中定义)，导致 debug('update_outer_2 -> '::text || _value::text) 静默失败(并且不打印通知).

From the output, it shows that the problem is the subquery (aka 'inner') is executed AFTER its referencing (aka 'outer') query within the 2 SELECT queries in the foo_update_rule. As a result, the _value column (which is defined within the subquery) is not yet defined when the outer query is evaluated, causing the debug('update_outer_2 -> '::text || _value::text) to silently fail (and not print out a notice).

奇怪的是，ON INSERT 规则中的相同 SQL 可以正常工作(打印出两个 'outer_2 -> ...' 通知).但由于某种原因，SQL 在 ON UPDATE 规则中不起作用.

The wierd thing is, the same SQL within an ON INSERT rule will work fine (printing out both of the 'outer_2 -> ...' notices). But for some reason the SQL does not work within an ON UPDATE rule.

如何修复上述查询，以便打印以下 2 个通知?

How can the above query be fixed so that the following 2 notices are printed?

NOTICE:  plphp: debug(): "update_outer_2 -> a"
NOTICE:  plphp: debug(): "update_outer_2 -> b"

NOTICE:  plphp: debug(): "update2_outer_2 -> a"
NOTICE:  plphp: debug(): "update2_outer_2 -> b"

推荐答案

PostgreSQL，或者就此而言，SQL 本身不保证查询的不同部分的执行顺序.它只定义最终结果.事实上，查询的不同部分可以混合执行——如果数据库支持的话，可以完全并行执行.

PostgreSQL, or for that matter SQL itself, makes no guarantee in which order different parts of the query executes. It only defines the final result. in fact, the different parts of the query can execute intemixed - or fully parallelized if the database were to support that.

现在，规则使事情变得更糟，因为它们通常不会按用户期望的方式工作.规则在解析器级别工作，而不是在执行.因此，您的不同部分很可能会运行不止一次 - 仅仅是因为它们会突然在解析树中出现不止一次.

Now, RULEs make things even worse, in that they generally don't work the way users expect. RULEs work at the parser level, not at the execution. So your different parts may very well run more than once - simply because they will suddenly appear more than once in the parse tree.

在大多数情况下，您需要的是 TRIGGER 而不是 RULE.

In most cases, what you want is a TRIGGER rather than a RULE.

不过，最重要的是，您的应用程序不应依赖查询中的特定子查询(或连接或其他)以特定顺序执行.

Bottom line is, though, that your application should not rely on the specific subqueries (or joins or whatever) in a query to execute in a particular order.

这篇关于在 ON UPDATE 规则中执行 Postgresql 子查询(可能是 postgresql 中的错误?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！