Postgresql subquery execution within ON UPDATE rule (maybe bug in postgresql?)

Question

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"

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).

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.

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"

Use explain to see the order in which the statements are done. — Denis de Bernardy
– Denis de Bernardy, Commented Jul 17, 2011 at 7:06
To 'mu is too short', the notices are coming from the debug() function defined at the top of the code block. The 'update2_outer_2' is called in the code block starting with: ROW(FALSE,FALSE) IN ( SELECT... And, yes 'debug('update_inner'::text)' is called in 2 different places but that is the intention. — archmeta
– archmeta, Commented Jul 17, 2011 at 12:07
To 'Denis', I am not able to use EXPLAIN within the rule, as pgsql discards the rows. EXPLAIN on the stand-alone SELECT shows that it is executed in the correct order. But the original problem was that pgsql was executing the queries within the rule in an incorrect order, whereas the execution order in the stand-alone SELECT is fine. — archmeta
– archmeta, Commented Jul 17, 2011 at 12:12

Magnus Hagander · Accepted Answer · 2011-07-17 10:52:04Z

4

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.

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.

answered Jul 17, 2011 at 10:52

Magnus Hagander

25.4k6 gold badges60 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

archmeta Over a year ago

For various reasons, I am locked into using a RULE instead of a TRIGGER in this context. I am not relying on the order of the execution of the queries, as the debug() notices are only meant to illustrate the execution path that is leading to the problem. The ultimate problem here is that the pgsql RULE is evaluating the subquery in such a way that the FINAL RESULT is incorrect, since the columns defined by the WITH subquery are seemingly unavailable to the SELECT, which seems to go against the docs.

archmeta Over a year ago

In any case, the FINAL result is incorrect, since the WITH subquery inside the RULE is executed in the correct order (inner followed by outer), but the column (_value) defined within the WITH query is not available within the SELECT outer query inside the RULE, whereas _value is available to the SELECT outside the RULE.

archmeta Over a year ago

I guess the key question I have is, assuming we are constrained to do this within the RULE, how can the query be modified so that the _value column from the foobar (defined within the WITH in the RULE) become visible to the SELECT? Right now _value seems to be passed to SELECT as either NULL or VOID inside the RULE.

Collectives™ on Stack Overflow

Postgresql subquery execution within ON UPDATE rule (maybe bug in postgresql?)

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related