8000 Fix mishandling of OLD/NEW references in subqueries in rule actions. · postgres/postgres@79f194c · GitHub
[go: up one dir, main page]
Skip to content

Commit 79f194c

Browse files
deanrasheeddeanrasheed
committed
Fix mishandling of OLD/NEW references in subqueries in rule actions.
If a rule action contains a subquery that refers to columns from OLD or NEW, then those are really lateral references, and the planner will complain if it sees such things in a subquery that isn't marked as lateral. However, at rule-definition time, the user isn't required to mark the subquery with LATERAL, and so it can fail when the rule is used. Fix this by marking such subqueries as lateral in the rewriter, at the point where they're used. Dean Rasheed and Tom Lane, per report from Alexander Lakhin. Back-patch to all supported branches. Discussion: https://postgr.es/m/5e09da43-aaba-7ea7-0a51-a2eb981b058b%40gmail.com
1 parent 44dbc96 commit 79f194c

File tree

3 files changed

+61
-4
lines changed

3 files changed

+61
-4
lines changed

‎src/backend/rewrite/rewriteHandler.c

Lines changed: 19 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -28,6 +28,7 @@
2828
#include "miscadmin.h"
2929
#include "nodes/makefuncs.h"
3030
#include "nodes/nodeFuncs.h"
31+
#include "optimizer/var.h"
3132
#include "parser/analyze.h"
3233
#include "parser/parse_coerce.h"
3334
#include "parser/parse_relation.h"
@@ -343,6 +344,7 @@ rewriteRuleAction(Query *parsetree,
343344
Query *sub_action;
344345
Query **sub_action_ptr;
345346
acquireLocksOnSubLinks_context context;
347+
ListCell *lc;
346348

347349
context.for_execute = true;
348350

@@ -381,6 +383,23 @@ rewriteRuleAction(Query *parsetree,
381383
ChangeVarNodes(rule_qual,
382384
PRS2_OLD_VARNO + rt_length, rt_index, 0);
383385

386+
/*
387+
* Mark any subquery RTEs in the rule action as LATERAL if they contain
388+
* Vars referring to the current query level (references to NEW/OLD).
389+
* Those really are lateral references, but we've historically not
390+
* required users to mark such subqueries with LATERAL explicitly. But
391+
* the planner will complain if such Vars exist in a non-LATERAL subquery,
392+
* so we have to fix things up here.
393+
*/
394+
foreach(lc, sub_action->rtable)
395+
{
396+
RangeTblEntry *rte = (RangeTblEntry *) lfirst(lc);
397+
398+
if (rte->rtekind == RTE_SUBQUERY && !rte->lateral &&
399+
contain_vars_of_level((Node *) rte->subquery, 1))
400+
rte->lateral = true;
401+
}
402+
384403
/*
385404
* Generate expanded rtable consisting of main parsetree's rtable plus
386405
* rule action's rtable; this becomes the complete rtable for the rule
@@ -422,8 +441,6 @@ rewriteRuleAction(Query *parsetree,
422441
*/
423442
if (parsetree->hasSubLinks && !sub_action->hasSubLinks)
424443
{
425-
ListCell *lc;
426-
427444
foreach(lc, parsetree->rtable)
428445
{
429446
RangeTblEntry *rte = (RangeTblEntry *) lfirst(lc);
@@ -521,8 +538,6 @@ rewriteRuleAction(Query *parsetree,
521538
*/
522539
if (parsetree->cteList != NIL && sub_action->commandType != CMD_UTILITY)
523540
{
524-
ListCell *lc;
525-
526541
/*
527542
* Annoying implementation restriction: because CTEs are identified by
528543
* name within a cteList, we can't merge a CTE from the original query

‎src/test/regress/expected/rules.out

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2915,6 +2915,31 @@ Rules:
29152915
RETURNING trgt.f1,
29162916
trgt.f2
29172917

2918+
--
2919+
-- Test implicit LATERAL references to old/new in rules
2920+
--
2921+
CREATE TABLE rule_t1(a int, b text DEFAULT 'xxx', c int);
2922+
CREATE VIEW rule_v1 AS SELECT * FROM rule_t1;
2923+
CREATE RULE v1_ins AS ON INSERT TO rule_v1
2924+
DO ALSO INSERT INTO rule_t1
2925+
SELECT * FROM (SELECT a + 10 FROM rule_t1 WHERE a = NEW.a) tt;
2926+
CREATE RULE v1_upd AS ON UPDATE TO rule_v1
2927+
DO ALSO UPDATE rule_t1 t
2928+
SET c = tt.a * 10
2929+
FROM (SELECT a FROM rule_t1 WHERE a = OLD.a) tt WHERE t.a = tt.a;
2930+
INSERT INTO rule_v1 VALUES (1, 'a'), (2, 'b');
2931+
UPDATE rule_v1 SET b = upper(b);
2932+
SELECT * FROM rule_t1;
2933+
a | b | c
2934+
----+-----+-----
2935+
1 | A | 10
2936+
2 | B | 20
2937+
11 | XXX | 110
2938+
12 | XXX | 120
2939+
(4 rows)
2940+
2941+
DROP TABLE rule_t1 CASCADE;
2942+
NOTICE: drop cascades to view rule_v1
29182943
--
29192944
-- check alter rename rule
29202945
--

‎src/test/regress/sql/rules.sql

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1053,6 +1053,23 @@ create rule r7 as on delete to rules_src do instead
10531053
-- check display of all rules added above
10541054
\d+ rules_src
10551055

1056+
--
1057+
-- Test implicit LATERAL references to old/new in rules
1058+
--
1059+
CREATE TABLE rule_t1(a int, b text DEFAULT 'xxx', c int);
1060+
CREATE VIEW rule_v1 AS SELECT * FROM rule_t1;
1061+
CREATE RULE v1_ins AS ON INSERT TO rule_v1
1062+
DO ALSO INSERT INTO rule_t1
1063+
SELECT * FROM (SELECT a + 10 FROM rule_t1 WHERE a = NEW.a) tt;
1064+
CREATE RULE v1_upd AS ON UPDATE TO rule_v1
1065+
DO ALSO UPDATE rule_t1 t
1066+
SET c = tt.a * 10
1067+
FROM (SELECT a FROM rule_t1 WHERE a = OLD.a) tt WHERE t.a = tt.a;
1068+
INSERT INTO rule_v1 VALUES (1, 'a'), (2, 'b');
1069+
UPDATE rule_v1 SET b = upper(b);
1070+
SELECT * FROM rule_t1;
1071+
DROP TABLE rule_t1 CASCADE;
1072+
10561073
--
10571074
-- check alter rename rule
10581075
--

0 commit comments

Comments
 (0)
0