🌐 AI搜索 & 代理 主页
Skip to content

Commit 4ec6581

Browse files
tglsfdctglsfdc
committed
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.
1 parent 759d9d6 commit 4ec6581

File tree

3 files changed

+87
-3
lines changed

3 files changed

+87
-3
lines changed

‎src/backend/optimizer/plan/planner.c‎

Lines changed: 1 addition & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -835,8 +835,6 @@ inheritance_planner(PlannerInfo *root)
835835
Assert(subroot.join_info_list == NIL);
836836
/* and we haven't created PlaceHolderInfos, either */
837837
Assert(subroot.placeholder_list == NIL);
838-
/* build a separate list of initplans for each child */
839-
subroot.init_plans = NIL;
840838
/* hack to mark target relation as an inheritance partition */
841839
subroot.hasInheritedTarget = true;
842840

@@ -883,7 +881,7 @@ inheritance_planner(PlannerInfo *root)
883881
save_rel_array = subroot.simple_rel_array;
884882

885883
/* Make sure any initplans from this rel get into the outer list */
886-
root->init_plans = list_concat(root->init_plans, subroot.init_plans);
884+
root->init_plans = subroot.init_plans;
887885

888886
/* Build list of target-relation RT indexes */
889887
resultRelations = lappend_int(resultRelations, appinfo->child_relid);

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

Lines changed: 56 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1800,6 +1800,62 @@ SELECT * FROM y;
18001800

18011801
DROP TRIGGER y_trig ON y;
18021802
DROP FUNCTION y_trigger();
1803+
-- WITH attached to inherited UPDATE or DELETE
1804+
CREATE TEMP TABLE parent ( id int, val text );
1805+
CREATE TEMP TABLE child1 ( ) INHERITS ( parent );
1806+
CREATE TEMP TABLE child2 ( ) INHERITS ( parent );
1807+
INSERT INTO parent VALUES ( 1, 'p1' );
1808+
INSERT INTO child1 VALUES ( 11, 'c11' ),( 12, 'c12' );
1809+
INSERT INTO child2 VALUES ( 23, 'c21' ),( 24, 'c22' );
1810+
WITH rcte AS ( SELECT sum(id) AS totalid FROM parent )
1811+
UPDATE parent SET id = id + totalid FROM rcte;
1812+
SELECT * FROM parent;
1813+
id | val
1814+
----+-----
1815+
72 | p1
1816+
82 | c11
1817+
83 | c12
1818+
94 | c21
1819+
95 | c22
1820+
(5 rows)
1821+
1822+
WITH wcte AS ( INSERT INTO child1 VALUES ( 42, 'new' ) RETURNING id AS newid )
1823+
UPDATE parent SET id = id + newid FROM wcte;
1824+
SELECT * FROM parent;
1825+
id | val
1826+
-----+-----
1827+
114 | p1
1828+
42 | new
1829+
124 | c11
1830+
125 | c12
1831+
136 | c21
1832+
137 | c22
1833+
(6 rows)
1834+
1835+
WITH rcte AS ( SELECT max(id) AS maxid FROM parent )
1836+
DELETE FROM parent USING rcte WHERE id = maxid;
1837+
SELECT * FROM parent;
1838+
id | val
1839+
-----+-----
1840+
114 | p1
1841+
42 | new
1842+
124 | c11
1843+
125 | c12
1844+
136 | c21
1845+
(5 rows)
1846+
1847+
WITH wcte AS ( INSERT INTO child2 VALUES ( 42, 'new2' ) RETURNING id AS newid )
1848+
DELETE FROM parent USING wcte WHERE id = newid;
1849+
SELECT * FROM parent;
1850+
id | val
1851+
-----+------
1852+
114 | p1
1853+
124 | c11
1854+
125 | c12
1855+
136 | c21
1856+
42 | new2
1857+
(5 rows)
1858+
18031859
-- error cases
18041860
-- data-modifying WITH tries to use its own output
18051861
WITH RECURSIVE t AS (

‎src/test/regress/sql/with.sql‎

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -761,6 +761,36 @@ SELECT * FROM y;
761761
DROP TRIGGER y_trig ON y;
762762
DROP FUNCTION y_trigger();
763763

764+
-- WITH attached to inherited UPDATE or DELETE
765+
766+
CREATE TEMP TABLE parent ( id int, val text );
767+
CREATE TEMP TABLE child1 ( ) INHERITS ( parent );
768+
CREATE TEMP TABLE child2 ( ) INHERITS ( parent );
769+
770+
INSERT INTO parent VALUES ( 1, 'p1' );
771+
INSERT INTO child1 VALUES ( 11, 'c11' ),( 12, 'c12' );
772+
INSERT INTO child2 VALUES ( 23, 'c21' ),( 24, 'c22' );
773+
774+
WITH rcte AS ( SELECT sum(id) AS totalid FROM parent )
775+
UPDATE parent SET id = id + totalid FROM rcte;
776+
777+
SELECT * FROM parent;
778+
779+
WITH wcte AS ( INSERT INTO child1 VALUES ( 42, 'new' ) RETURNING id AS newid )
780+
UPDATE parent SET id = id + newid FROM wcte;
781+
782+
SELECT * FROM parent;
783+
784+
WITH rcte AS ( SELECT max(id) AS maxid FROM parent )
785+
DELETE FROM parent USING rcte WHERE id = maxid;
786+
787+
SELECT * FROM parent;
788+
789+
WITH wcte AS ( INSERT INTO child2 VALUES ( 42, 'new2' ) RETURNING id AS newid )
790+
DELETE FROM parent USING wcte WHERE id = newid;
791+
792+
SELECT * FROM parent;
793+
764794
-- error cases
765795

766796
-- data-modifying WITH tries to use its own output

0 commit comments

Comments
 (0)