Fix handling of auto-updatable views on inherited tables.

An INSERT into such a view should work just like an INSERT into its base
table, ie the insertion should go directly into that table ... not be
duplicated into each child table, as was happening before, per bug #8275
from Rushabh Lathia.  On the other hand, the current behavior for
UPDATE/DELETE seems reasonable: the update/delete traverses the child
tables, or not, depending on whether the view specifies ONLY or not.
Add some regression tests covering this area.

Dean Rasheed

3 files changed, 139 insertions, 0 deletions

diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index d4b97081950..d909de3a539 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -2389,6 +2389,13 @@ rewriteTargetView(Query *parsetree, Relation view)
 	new_rt_index = list_length(parsetree->rtable);
 
 	/*
+	 * INSERTs never inherit.  For UPDATE/DELETE, we use the view query's
+	 * inheritance flag for the base relation.
+	 */
+	if (parsetree->commandType == CMD_INSERT)
+		new_rte->inh = false;
+
+	/*
 	 * Make a copy of the view's targetlist, adjusting its Vars to reference
 	 * the new target RTE, ie make their varnos be new_rt_index instead of
 	 * base_rt_index.  There can be no Vars for other rels in the tlist, so
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index ecb61e0ff32..136310331fe 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -1063,3 +1063,103 @@ SELECT * FROM rw_view1;
 
 DROP TABLE base_tbl CASCADE;
 NOTICE:  drop cascades to view rw_view1
+-- inheritance tests
+CREATE TABLE base_tbl_parent (a int);
+CREATE TABLE base_tbl_child (CHECK (a > 0)) INHERITS (base_tbl_parent);
+INSERT INTO base_tbl_parent SELECT * FROM generate_series(-8, -1);
+INSERT INTO base_tbl_child SELECT * FROM generate_series(1, 8);
+CREATE VIEW rw_view1 AS SELECT * FROM base_tbl_parent;
+CREATE VIEW rw_view2 AS SELECT * FROM ONLY base_tbl_parent;
+SELECT * FROM rw_view1 ORDER BY a;
+ a  
+----
+ -8
+ -7
+ -6
+ -5
+ -4
+ -3
+ -2
+ -1
+  1
+  2
+  3
+  4
+  5
+  6
+  7
+  8
+(16 rows)
+
+SELECT * FROM ONLY rw_view1 ORDER BY a;
+ a  
+----
+ -8
+ -7
+ -6
+ -5
+ -4
+ -3
+ -2
+ -1
+  1
+  2
+  3
+  4
+  5
+  6
+  7
+  8
+(16 rows)
+
+SELECT * FROM rw_view2 ORDER BY a;
+ a  
+----
+ -8
+ -7
+ -6
+ -5
+ -4
+ -3
+ -2
+ -1
+(8 rows)
+
+INSERT INTO rw_view1 VALUES (-100), (100);
+INSERT INTO rw_view2 VALUES (-200), (200);
+UPDATE rw_view1 SET a = a*10 WHERE a IN (-1, 1); -- Should produce -10 and 10
+UPDATE ONLY rw_view1 SET a = a*10 WHERE a IN (-2, 2); -- Should produce -20 and 20
+UPDATE rw_view2 SET a = a*10 WHERE a IN (-3, 3); -- Should produce -30 only
+UPDATE ONLY rw_view2 SET a = a*10 WHERE a IN (-4, 4); -- Should produce -40 only
+DELETE FROM rw_view1 WHERE a IN (-5, 5); -- Should delete -5 and 5
+DELETE FROM ONLY rw_view1 WHERE a IN (-6, 6); -- Should delete -6 and 6
+DELETE FROM rw_view2 WHERE a IN (-7, 7); -- Should delete -7 only
+DELETE FROM ONLY rw_view2 WHERE a IN (-8, 8); -- Should delete -8 only
+SELECT * FROM ONLY base_tbl_parent ORDER BY a;
+  a   
+------
+ -200
+ -100
+  -40
+  -30
+  -20
+  -10
+  100
+  200
+(8 rows)
+
+SELECT * FROM base_tbl_child ORDER BY a;
+ a  
+----
+  3
+  4
+  7
+  8
+ 10
+ 20
+(6 rows)
+
+DROP TABLE base_tbl_parent, base_tbl_child CASCADE;
+NOTICE:  drop cascades to 2 other objects
+DETAIL:  drop cascades to view rw_view1
+drop cascades to view rw_view2
diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql
index 49dfedd3a6b..c8a1c628d55 100644
--- a/src/test/regress/sql/updatable_views.sql
+++ b/src/test/regress/sql/updatable_views.sql
@@ -509,3 +509,35 @@ UPDATE rw_view1 SET arr[1] = 42, arr[2] = 77 WHERE a = 3;
 SELECT * FROM rw_view1;
 
 DROP TABLE base_tbl CASCADE;
+
+-- inheritance tests
+
+CREATE TABLE base_tbl_parent (a int);
+CREATE TABLE base_tbl_child (CHECK (a > 0)) INHERITS (base_tbl_parent);
+INSERT INTO base_tbl_parent SELECT * FROM generate_series(-8, -1);
+INSERT INTO base_tbl_child SELECT * FROM generate_series(1, 8);
+
+CREATE VIEW rw_view1 AS SELECT * FROM base_tbl_parent;
+CREATE VIEW rw_view2 AS SELECT * FROM ONLY base_tbl_parent;
+
+SELECT * FROM rw_view1 ORDER BY a;
+SELECT * FROM ONLY rw_view1 ORDER BY a;
+SELECT * FROM rw_view2 ORDER BY a;
+
+INSERT INTO rw_view1 VALUES (-100), (100);
+INSERT INTO rw_view2 VALUES (-200), (200);
+
+UPDATE rw_view1 SET a = a*10 WHERE a IN (-1, 1); -- Should produce -10 and 10
+UPDATE ONLY rw_view1 SET a = a*10 WHERE a IN (-2, 2); -- Should produce -20 and 20
+UPDATE rw_view2 SET a = a*10 WHERE a IN (-3, 3); -- Should produce -30 only
+UPDATE ONLY rw_view2 SET a = a*10 WHERE a IN (-4, 4); -- Should produce -40 only
+
+DELETE FROM rw_view1 WHERE a IN (-5, 5); -- Should delete -5 and 5
+DELETE FROM ONLY rw_view1 WHERE a IN (-6, 6); -- Should delete -6 and 6
+DELETE FROM rw_view2 WHERE a IN (-7, 7); -- Should delete -7 only
+DELETE FROM ONLY rw_view2 WHERE a IN (-8, 8); -- Should delete -8 only
+
+SELECT * FROM ONLY base_tbl_parent ORDER BY a;
+SELECT * FROM base_tbl_child ORDER BY a;
+
+DROP TABLE base_tbl_parent, base_tbl_child CASCADE;