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

Commit21e042b

Browse files
committed
Yet further fixes for multi-row VALUES lists for updatable views.
DEFAULT markers appearing in an INSERT on an updatable viewcould be mis-processed if they were in a multi-row VALUES clause.This would lead to strange errors such as "cache lookup failedfor type NNNN", or in older branches even to crashes.The cause is that commit41531e4 tried to re-use rewriteValuesRTE()to remove any SetToDefault nodes (that hadn't previously been replacedby the view's own default values) appearing in "product" queries,that is DO ALSO queries. That's fundamentally wrong because theDO ALSO queries might not even be INSERTs; and even if they are,their targetlists don't necessarily match the view's column list,so that almost all the logic in rewriteValuesRTE() is inapplicable.What we want is a narrow focus on replacing any such nodes with NULLconstants. (That is, in this context we are interpreting the defaultsas being strictly those of the view itself; and we already replacedany that aren't NULL.) We could add still more !force_nulls teststo further lobotomize rewriteValuesRTE(); but it seems cleaner tosplit out this case to a new function, restoring rewriteValuesRTE()to the charter it had before.Per bug #17633 from jiye_sw. Patch by me, but thanks toRichard Guo and Japin Li for initial investigation.Back-patch to all supported branches, as the previous fix was.Discussion:https://postgr.es/m/17633-98cc85e1fa91e905@postgresql.org
1 parent33d979a commit21e042b

File tree

3 files changed

+97
-26
lines changed

3 files changed

+97
-26
lines changed

‎src/backend/rewrite/rewriteHandler.c

Lines changed: 58 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -75,7 +75,8 @@ static TargetEntry *process_matched_tle(TargetEntry *src_tle,
7575
constchar*attrName);
7676
staticNode*get_assignment_input(Node*node);
7777
staticboolrewriteValuesRTE(Query*parsetree,RangeTblEntry*rte,intrti,
78-
Relationtarget_relation,boolforce_nulls);
78+
Relationtarget_relation);
79+
staticvoidrewriteValuesRTEToNulls(Query*parsetree,RangeTblEntry*rte);
7980
staticvoidmarkQueryForLocking(Query*qry,Node*jtnode,
8081
LockClauseStrengthstrength,LockWaitPolicywaitPolicy,
8182
boolpushedDown);
@@ -1262,17 +1263,6 @@ searchForDefault(RangeTblEntry *rte)
12621263
* all DEFAULT items are replaced, and if the target relation doesn't have a
12631264
* default, the value is explicitly set to NULL.
12641265
*
1265-
* Additionally, if force_nulls is true, the target relation's defaults are
1266-
* ignored and all DEFAULT items in the VALUES list are explicitly set to
1267-
* NULL, regardless of the target relation's type. This is used for the
1268-
* product queries generated by DO ALSO rules attached to an auto-updatable
1269-
* view, for which we will have already called this function with force_nulls
1270-
* false. For these product queries, we must then force any remaining DEFAULT
1271-
* items to NULL to provide concrete values for the rule actions.
1272-
* Essentially, this is a mix of the 2 cases above --- the original query is
1273-
* an insert into an auto-updatable view, and the product queries are inserts
1274-
* into a rule-updatable view.
1275-
*
12761266
* Note that we may have subscripted or field assignment targetlist entries,
12771267
* as well as more complex expressions from already-replaced DEFAULT items if
12781268
* we have recursed to here for an auto-updatable view. However, it ought to
@@ -1285,7 +1275,7 @@ searchForDefault(RangeTblEntry *rte)
12851275
*/
12861276
staticbool
12871277
rewriteValuesRTE(Query*parsetree,RangeTblEntry*rte,intrti,
1288-
Relationtarget_relation,boolforce_nulls)
1278+
Relationtarget_relation)
12891279
{
12901280
List*newValues;
12911281
ListCell*lc;
@@ -1294,15 +1284,16 @@ rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte, int rti,
12941284
intnumattrs;
12951285
int*attrnos;
12961286

1287+
/* Steps below are not sensible for non-INSERT queries */
1288+
Assert(parsetree->commandType==CMD_INSERT);
1289+
Assert(rte->rtekind==RTE_VALUES);
1290+
12971291
/*
12981292
* Rebuilding all the lists is a pretty expensive proposition in a big
12991293
* VALUES list, and it's a waste of time if there aren't any DEFAULT
13001294
* placeholders. So first scan to see if there are any.
1301-
*
1302-
* We skip this check if force_nulls is true, because we know that there
1303-
* are DEFAULT items present in that case.
13041295
*/
1305-
if (!force_nulls&& !searchForDefault(rte))
1296+
if (!searchForDefault(rte))
13061297
return true;/* nothing to do */
13071298

13081299
/*
@@ -1336,12 +1327,10 @@ rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte, int rti,
13361327
/*
13371328
* Check if the target relation is an auto-updatable view, in which case
13381329
* unresolved defaults will be left untouched rather than being set to
1339-
* NULL. If force_nulls is true, we always set DEFAULT items to NULL, so
1340-
* skip this check in that case --- it isn't an auto-updatable view.
1330+
* NULL.
13411331
*/
13421332
isAutoUpdatableView= false;
1343-
if (!force_nulls&&
1344-
target_relation->rd_rel->relkind==RELKIND_VIEW&&
1333+
if (target_relation->rd_rel->relkind==RELKIND_VIEW&&
13451334
!view_has_instead_trigger(target_relation,CMD_INSERT))
13461335
{
13471336
List*locks;
@@ -1399,9 +1388,10 @@ rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte, int rti,
13991388

14001389
if (attrno==0)
14011390
elog(ERROR,"cannot set value in column %d to DEFAULT",i);
1391+
Assert(attrno>0&&attrno <=target_relation->rd_att->natts);
14021392
att_tup=TupleDescAttr(target_relation->rd_att,attrno-1);
14031393

1404-
if (!force_nulls&& !att_tup->attisdropped)
1394+
if (!att_tup->attisdropped)
14051395
new_expr=build_column_default(target_relation,attrno);
14061396
else
14071397
new_expr=NULL;/* force a NULL if dropped */
@@ -1451,6 +1441,50 @@ rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte, int rti,
14511441
returnallReplaced;
14521442
}
14531443

1444+
/*
1445+
* Mop up any remaining DEFAULT items in the given VALUES RTE by
1446+
* replacing them with NULL constants.
1447+
*
1448+
* This is used for the product queries generated by DO ALSO rules attached to
1449+
* an auto-updatable view. The action can't depend on the "target relation"
1450+
* since the product query might not have one (it needn't be an INSERT).
1451+
* Essentially, such queries are treated as being attached to a rule-updatable
1452+
* view.
1453+
*/
1454+
staticvoid
1455+
rewriteValuesRTEToNulls(Query*parsetree,RangeTblEntry*rte)
1456+
{
1457+
List*newValues;
1458+
ListCell*lc;
1459+
1460+
Assert(rte->rtekind==RTE_VALUES);
1461+
newValues=NIL;
1462+
foreach(lc,rte->values_lists)
1463+
{
1464+
List*sublist= (List*)lfirst(lc);
1465+
List*newList=NIL;
1466+
ListCell*lc2;
1467+
1468+
foreach(lc2,sublist)
1469+
{
1470+
Node*col= (Node*)lfirst(lc2);
1471+
1472+
if (IsA(col,SetToDefault))
1473+
{
1474+
SetToDefault*def= (SetToDefault*)col;
1475+
1476+
newList=lappend(newList,makeNullConst(def->typeId,
1477+
def->typeMod,
1478+
def->collation));
1479+
}
1480+
else
1481+
newList=lappend(newList,col);
1482+
}
1483+
newValues=lappend(newValues,newList);
1484+
}
1485+
rte->values_lists=newValues;
1486+
}
1487+
14541488

14551489
/*
14561490
* rewriteTargetListUD - rewrite UPDATE/DELETE targetlist as needed
@@ -3663,7 +3697,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
36633697
parsetree->resultRelation);
36643698
/* ... and the VALUES expression lists */
36653699
if (!rewriteValuesRTE(parsetree,values_rte,values_rte_index,
3666-
rt_entry_relation, false))
3700+
rt_entry_relation))
36673701
defaults_remaining= true;
36683702
}
36693703
else
@@ -3743,9 +3777,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
37433777
RangeTblEntry*values_rte=rt_fetch(values_rte_index,
37443778
pt->rtable);
37453779

3746-
rewriteValuesRTE(pt,values_rte,values_rte_index,
3747-
rt_entry_relation,
3748-
true);/* Force remaining defaults to NULL */
3780+
rewriteValuesRTEToNulls(pt,values_rte);
37493781
}
37503782
}
37513783

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

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -433,8 +433,30 @@ EXPLAIN (costs off) DELETE FROM rw_view1 WHERE a=5;
433433
Index Cond: ((a > 0) AND (a = 5))
434434
(3 rows)
435435

436+
-- it's still updatable if we add a DO ALSO rule
437+
CREATE TABLE base_tbl_hist(ts timestamptz default now(), a int, b text);
438+
CREATE RULE base_tbl_log AS ON INSERT TO rw_view1 DO ALSO
439+
INSERT INTO base_tbl_hist(a,b) VALUES(new.a, new.b);
440+
SELECT table_name, is_updatable, is_insertable_into
441+
FROM information_schema.views
442+
WHERE table_name = 'rw_view1';
443+
table_name | is_updatable | is_insertable_into
444+
------------+--------------+--------------------
445+
rw_view1 | YES | YES
446+
(1 row)
447+
448+
-- Check behavior with DEFAULTs (bug #17633)
449+
INSERT INTO rw_view1 VALUES (9, DEFAULT), (10, DEFAULT);
450+
SELECT a, b FROM base_tbl_hist;
451+
a | b
452+
----+---
453+
9 |
454+
10 |
455+
(2 rows)
456+
436457
DROP TABLE base_tbl CASCADE;
437458
NOTICE: drop cascades to view rw_view1
459+
DROP TABLE base_tbl_hist;
438460
-- view on top of view
439461
CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
440462
INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i);

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

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -148,7 +148,24 @@ SELECT * FROM base_tbl;
148148
EXPLAIN (costs off)UPDATE rw_view1SET a=6WHERE a=5;
149149
EXPLAIN (costs off)DELETEFROM rw_view1WHERE a=5;
150150

151+
-- it's still updatable if we add a DO ALSO rule
152+
153+
CREATETABLEbase_tbl_hist(tstimestamptz default now(), aint, btext);
154+
155+
CREATERULEbase_tbl_logASON INSERT TO rw_view1 DO ALSO
156+
INSERT INTO base_tbl_hist(a,b)VALUES(new.a,new.b);
157+
158+
SELECT table_name, is_updatable, is_insertable_into
159+
FROMinformation_schema.views
160+
WHERE table_name='rw_view1';
161+
162+
-- Check behavior with DEFAULTs (bug #17633)
163+
164+
INSERT INTO rw_view1VALUES (9, DEFAULT), (10, DEFAULT);
165+
SELECT a, bFROM base_tbl_hist;
166+
151167
DROPTABLE base_tbl CASCADE;
168+
DROPTABLE base_tbl_hist;
152169

153170
-- view on top of view
154171

0 commit comments

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