diff options
author | Tom Lane <tgl@sss.pgh.pa.us> | 2012-01-28 20:24:49 -0500 |
---|---|---|
committer | Tom Lane <tgl@sss.pgh.pa.us> | 2012-01-28 20:24:49 -0500 |
commit | 1a096957d747a3bc93abe65722c919c8a40a1049 (patch) | |
tree | 53d6b9ed2bf6dc4f08d9c248a5601523ad5e40d8 | |
parent | 0b1e1779533081f9d48c110a5e91ba122fc54461 (diff) | |
download | postgresql-1a096957d747a3bc93abe65722c919c8a40a1049.tar.gz |
Fix handling of init_plans list in inheritance_planner().
Formerly we passed an empty list to each per-child-table invocation of
grouping_planner, and then merged the results into the global list.
However, that fails if there's a CTE attached to the statement, because
create_ctescan_plan uses the list to find the plan referenced by a CTE
reference; so it was unable to find any CTEs attached to the outer UPDATE
or DELETE. But there's no real reason not to use the same list throughout
the process, and doing so is simpler and faster anyway.
Per report from Josh Berkus of "could not find plan for CTE" failures.
Back-patch to 9.1 where we added support for WITH attached to UPDATE or
DELETE. Add some regression test cases, too.
-rw-r--r-- | src/backend/optimizer/plan/planner.c | 3 | ||||
-rw-r--r-- | src/test/regress/expected/with.out | 56 | ||||
-rw-r--r-- | src/test/regress/sql/with.sql | 30 |
3 files changed, 87 insertions, 2 deletions
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index d4d9c58cff..b7e02132ce 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -764,7 +764,6 @@ inheritance_planner(PlannerInfo *root) subroot.parse = (Query *) adjust_appendrel_attrs((Node *) parse, appinfo); - subroot.init_plans = NIL; subroot.hasInheritedTarget = true; /* We needn't modify the child's append_rel_list */ /* There shouldn't be any OJ info to translate, as yet */ @@ -789,7 +788,7 @@ inheritance_planner(PlannerInfo *root) subplans = lappend(subplans, subplan); /* Make sure any initplans from this rel get into the outer list */ - root->init_plans = list_concat(root->init_plans, subroot.init_plans); + root->init_plans = subroot.init_plans; /* Build list of target-relation RT indexes */ resultRelations = lappend_int(resultRelations, appinfo->child_relid); diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out index a1b089921d..148ddcb25e 100644 --- a/src/test/regress/expected/with.out +++ b/src/test/regress/expected/with.out @@ -1800,6 +1800,62 @@ SELECT * FROM y; DROP TRIGGER y_trig ON y; DROP FUNCTION y_trigger(); +-- WITH attached to inherited UPDATE or DELETE +CREATE TEMP TABLE parent ( id int, val text ); +CREATE TEMP TABLE child1 ( ) INHERITS ( parent ); +CREATE TEMP TABLE child2 ( ) INHERITS ( parent ); +INSERT INTO parent VALUES ( 1, 'p1' ); +INSERT INTO child1 VALUES ( 11, 'c11' ),( 12, 'c12' ); +INSERT INTO child2 VALUES ( 23, 'c21' ),( 24, 'c22' ); +WITH rcte AS ( SELECT sum(id) AS totalid FROM parent ) +UPDATE parent SET id = id + totalid FROM rcte; +SELECT * FROM parent; + id | val +----+----- + 72 | p1 + 82 | c11 + 83 | c12 + 94 | c21 + 95 | c22 +(5 rows) + +WITH wcte AS ( INSERT INTO child1 VALUES ( 42, 'new' ) RETURNING id AS newid ) +UPDATE parent SET id = id + newid FROM wcte; +SELECT * FROM parent; + id | val +-----+----- + 114 | p1 + 42 | new + 124 | c11 + 125 | c12 + 136 | c21 + 137 | c22 +(6 rows) + +WITH rcte AS ( SELECT max(id) AS maxid FROM parent ) +DELETE FROM parent USING rcte WHERE id = maxid; +SELECT * FROM parent; + id | val +-----+----- + 114 | p1 + 42 | new + 124 | c11 + 125 | c12 + 136 | c21 +(5 rows) + +WITH wcte AS ( INSERT INTO child2 VALUES ( 42, 'new2' ) RETURNING id AS newid ) +DELETE FROM parent USING wcte WHERE id = newid; +SELECT * FROM parent; + id | val +-----+------ + 114 | p1 + 124 | c11 + 125 | c12 + 136 | c21 + 42 | new2 +(5 rows) + -- error cases -- data-modifying WITH tries to use its own output WITH RECURSIVE t AS ( diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql index bc340e4543..1479422c9c 100644 --- a/src/test/regress/sql/with.sql +++ b/src/test/regress/sql/with.sql @@ -761,6 +761,36 @@ SELECT * FROM y; DROP TRIGGER y_trig ON y; DROP FUNCTION y_trigger(); +-- WITH attached to inherited UPDATE or DELETE + +CREATE TEMP TABLE parent ( id int, val text ); +CREATE TEMP TABLE child1 ( ) INHERITS ( parent ); +CREATE TEMP TABLE child2 ( ) INHERITS ( parent ); + +INSERT INTO parent VALUES ( 1, 'p1' ); +INSERT INTO child1 VALUES ( 11, 'c11' ),( 12, 'c12' ); +INSERT INTO child2 VALUES ( 23, 'c21' ),( 24, 'c22' ); + +WITH rcte AS ( SELECT sum(id) AS totalid FROM parent ) +UPDATE parent SET id = id + totalid FROM rcte; + +SELECT * FROM parent; + +WITH wcte AS ( INSERT INTO child1 VALUES ( 42, 'new' ) RETURNING id AS newid ) +UPDATE parent SET id = id + newid FROM wcte; + +SELECT * FROM parent; + +WITH rcte AS ( SELECT max(id) AS maxid FROM parent ) +DELETE FROM parent USING rcte WHERE id = maxid; + +SELECT * FROM parent; + +WITH wcte AS ( INSERT INTO child2 VALUES ( 42, 'new2' ) RETURNING id AS newid ) +DELETE FROM parent USING wcte WHERE id = newid; + +SELECT * FROM parent; + -- error cases -- data-modifying WITH tries to use its own output |