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

Commit5530a82

Browse files
committed
Fix handling of auto-updatable views on inherited tables.
An INSERT into such a view should work just like an INSERT into its basetable, ie the insertion should go directly into that table ... not beduplicated into each child table, as was happening before, per bug #8275from Rushabh Lathia. On the other hand, the current behavior forUPDATE/DELETE seems reasonable: the update/delete traverses the childtables, or not, depending on whether the view specifies ONLY or not.Add some regression tests covering this area.Dean Rasheed
1 parent620935a commit5530a82

File tree

3 files changed

+139
-0
lines changed

3 files changed

+139
-0
lines changed

‎src/backend/rewrite/rewriteHandler.c

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2388,6 +2388,13 @@ rewriteTargetView(Query *parsetree, Relation view)
23882388
parsetree->rtable=lappend(parsetree->rtable,new_rte);
23892389
new_rt_index=list_length(parsetree->rtable);
23902390

2391+
/*
2392+
* INSERTs never inherit. For UPDATE/DELETE, we use the view query's
2393+
* inheritance flag for the base relation.
2394+
*/
2395+
if (parsetree->commandType==CMD_INSERT)
2396+
new_rte->inh= false;
2397+
23912398
/*
23922399
* Make a copy of the view's targetlist, adjusting its Vars to reference
23932400
* the new target RTE, ie make their varnos be new_rt_index instead of

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

Lines changed: 100 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1063,3 +1063,103 @@ SELECT * FROM rw_view1;
10631063

10641064
DROP TABLE base_tbl CASCADE;
10651065
NOTICE: drop cascades to view rw_view1
1066+
-- inheritance tests
1067+
CREATE TABLE base_tbl_parent (a int);
1068+
CREATE TABLE base_tbl_child (CHECK (a > 0)) INHERITS (base_tbl_parent);
1069+
INSERT INTO base_tbl_parent SELECT * FROM generate_series(-8, -1);
1070+
INSERT INTO base_tbl_child SELECT * FROM generate_series(1, 8);
1071+
CREATE VIEW rw_view1 AS SELECT * FROM base_tbl_parent;
1072+
CREATE VIEW rw_view2 AS SELECT * FROM ONLY base_tbl_parent;
1073+
SELECT * FROM rw_view1 ORDER BY a;
1074+
a
1075+
----
1076+
-8
1077+
-7
1078+
-6
1079+
-5
1080+
-4
1081+
-3
1082+
-2
1083+
-1
1084+
1
1085+
2
1086+
3
1087+
4
1088+
5
1089+
6
1090+
7
1091+
8
1092+
(16 rows)
1093+
1094+
SELECT * FROM ONLY rw_view1 ORDER BY a;
1095+
a
1096+
----
1097+
-8
1098+
-7
1099+
-6
1100+
-5
1101+
-4
1102+
-3
1103+
-2
1104+
-1
1105+
1
1106+
2
1107+
3
1108+
4
1109+
5
1110+
6
1111+
7
1112+
8
1113+
(16 rows)
1114+
1115+
SELECT * FROM rw_view2 ORDER BY a;
1116+
a
1117+
----
1118+
-8
1119+
-7
1120+
-6
1121+
-5
1122+
-4
1123+
-3
1124+
-2
1125+
-1
1126+
(8 rows)
1127+
1128+
INSERT INTO rw_view1 VALUES (-100), (100);
1129+
INSERT INTO rw_view2 VALUES (-200), (200);
1130+
UPDATE rw_view1 SET a = a*10 WHERE a IN (-1, 1); -- Should produce -10 and 10
1131+
UPDATE ONLY rw_view1 SET a = a*10 WHERE a IN (-2, 2); -- Should produce -20 and 20
1132+
UPDATE rw_view2 SET a = a*10 WHERE a IN (-3, 3); -- Should produce -30 only
1133+
UPDATE ONLY rw_view2 SET a = a*10 WHERE a IN (-4, 4); -- Should produce -40 only
1134+
DELETE FROM rw_view1 WHERE a IN (-5, 5); -- Should delete -5 and 5
1135+
DELETE FROM ONLY rw_view1 WHERE a IN (-6, 6); -- Should delete -6 and 6
1136+
DELETE FROM rw_view2 WHERE a IN (-7, 7); -- Should delete -7 only
1137+
DELETE FROM ONLY rw_view2 WHERE a IN (-8, 8); -- Should delete -8 only
1138+
SELECT * FROM ONLY base_tbl_parent ORDER BY a;
1139+
a
1140+
------
1141+
-200
1142+
-100
1143+
-40
1144+
-30
1145+
-20
1146+
-10
1147+
100
1148+
200
1149+
(8 rows)
1150+
1151+
SELECT * FROM base_tbl_child ORDER BY a;
1152+
a
1153+
----
1154+
3
1155+
4
1156+
7
1157+
8
1158+
10
1159+
20
1160+
(6 rows)
1161+
1162+
DROP TABLE base_tbl_parent, base_tbl_child CASCADE;
1163+
NOTICE: drop cascades to 2 other objects
1164+
DETAIL: drop cascades to view rw_view1
1165+
drop cascades to view rw_view2

‎src/test/regress/sql/updatable_views.sql

Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -509,3 +509,35 @@ UPDATE rw_view1 SET arr[1] = 42, arr[2] = 77 WHERE a = 3;
509509
SELECT*FROM rw_view1;
510510

511511
DROPTABLE base_tbl CASCADE;
512+
513+
-- inheritance tests
514+
515+
CREATETABLEbase_tbl_parent (aint);
516+
CREATETABLEbase_tbl_child (CHECK (a>0)) INHERITS (base_tbl_parent);
517+
INSERT INTO base_tbl_parentSELECT*FROM generate_series(-8,-1);
518+
INSERT INTO base_tbl_childSELECT*FROM generate_series(1,8);
519+
520+
CREATEVIEWrw_view1ASSELECT*FROM base_tbl_parent;
521+
CREATEVIEWrw_view2ASSELECT*FROM ONLY base_tbl_parent;
522+
523+
SELECT*FROM rw_view1ORDER BY a;
524+
SELECT*FROM ONLY rw_view1ORDER BY a;
525+
SELECT*FROM rw_view2ORDER BY a;
526+
527+
INSERT INTO rw_view1VALUES (-100), (100);
528+
INSERT INTO rw_view2VALUES (-200), (200);
529+
530+
UPDATE rw_view1SET a= a*10WHERE aIN (-1,1);-- Should produce -10 and 10
531+
UPDATE ONLY rw_view1SET a= a*10WHERE aIN (-2,2);-- Should produce -20 and 20
532+
UPDATE rw_view2SET a= a*10WHERE aIN (-3,3);-- Should produce -30 only
533+
UPDATE ONLY rw_view2SET a= a*10WHERE aIN (-4,4);-- Should produce -40 only
534+
535+
DELETEFROM rw_view1WHERE aIN (-5,5);-- Should delete -5 and 5
536+
DELETEFROM ONLY rw_view1WHERE aIN (-6,6);-- Should delete -6 and 6
537+
DELETEFROM rw_view2WHERE aIN (-7,7);-- Should delete -7 only
538+
DELETEFROM ONLY rw_view2WHERE aIN (-8,8);-- Should delete -8 only
539+
540+
SELECT*FROM ONLY base_tbl_parentORDER BY a;
541+
SELECT*FROM base_tbl_childORDER BY a;
542+
543+
DROPTABLE base_tbl_parent, base_tbl_child CASCADE;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp