Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit4ec6581

Browse files
committed
Fix handling of init_plans list in inheritance_planner().
Formerly we passed an empty list to each per-child-table invocation ofgrouping_planner, and then merged the results into the global list.However, that fails if there's a CTE attached to the statement, becausecreate_ctescan_plan uses the list to find the plan referenced by a CTEreference; so it was unable to find any CTEs attached to the outer UPDATEor DELETE. But there's no real reason not to use the same list throughoutthe 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 orDELETE. Add some regression test cases, too.
1 parent759d9d6 commit4ec6581

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
DROPTRIGGER y_trigON y;
762762
DROPFUNCTION y_trigger();
763763

764+
-- WITH attached to inherited UPDATE or DELETE
765+
766+
CREATE TEMP TABLE parent ( idint, valtext );
767+
CREATE TEMP TABLE child1 ( ) INHERITS ( parent );
768+
CREATE TEMP TABLE child2 ( ) INHERITS ( parent );
769+
770+
INSERT INTO parentVALUES (1,'p1' );
771+
INSERT INTO child1VALUES (11,'c11' ),(12,'c12' );
772+
INSERT INTO child2VALUES (23,'c21' ),(24,'c22' );
773+
774+
WITH rcteAS (SELECTsum(id)AS totalidFROM parent )
775+
UPDATE parentSET id= id+ totalidFROM rcte;
776+
777+
SELECT*FROM parent;
778+
779+
WITH wcteAS (INSERT INTO child1VALUES (42,'new' ) RETURNING idAS newid )
780+
UPDATE parentSET id= id+ newidFROM wcte;
781+
782+
SELECT*FROM parent;
783+
784+
WITH rcteAS (SELECTmax(id)AS maxidFROM parent )
785+
DELETEFROM parent USING rcteWHERE id= maxid;
786+
787+
SELECT*FROM parent;
788+
789+
WITH wcteAS (INSERT INTO child2VALUES (42,'new2' ) RETURNING idAS newid )
790+
DELETEFROM parent USING wcteWHERE 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)
[8]ページ先頭
©2009-2025 Movatter.jp