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

Commitabc510f

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 parentfa5c131 commitabc510f

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);
@@ -1260,17 +1261,6 @@ searchForDefault(RangeTblEntry *rte)
12601261
* all DEFAULT items are replaced, and if the target relation doesn't have a
12611262
* default, the value is explicitly set to NULL.
12621263
*
1263-
* Additionally, if force_nulls is true, the target relation's defaults are
1264-
* ignored and all DEFAULT items in the VALUES list are explicitly set to
1265-
* NULL, regardless of the target relation's type. This is used for the
1266-
* product queries generated by DO ALSO rules attached to an auto-updatable
1267-
* view, for which we will have already called this function with force_nulls
1268-
* false. For these product queries, we must then force any remaining DEFAULT
1269-
* items to NULL to provide concrete values for the rule actions.
1270-
* Essentially, this is a mix of the 2 cases above --- the original query is
1271-
* an insert into an auto-updatable view, and the product queries are inserts
1272-
* into a rule-updatable view.
1273-
*
12741264
* Note that we may have subscripted or field assignment targetlist entries,
12751265
* as well as more complex expressions from already-replaced DEFAULT items if
12761266
* we have recursed to here for an auto-updatable view. However, it ought to
@@ -1283,7 +1273,7 @@ searchForDefault(RangeTblEntry *rte)
12831273
*/
12841274
staticbool
12851275
rewriteValuesRTE(Query*parsetree,RangeTblEntry*rte,intrti,
1286-
Relationtarget_relation,boolforce_nulls)
1276+
Relationtarget_relation)
12871277
{
12881278
List*newValues;
12891279
ListCell*lc;
@@ -1292,15 +1282,16 @@ rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte, int rti,
12921282
intnumattrs;
12931283
int*attrnos;
12941284

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

13061297
/*
@@ -1334,12 +1325,10 @@ rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte, int rti,
13341325
/*
13351326
* Check if the target relation is an auto-updatable view, in which case
13361327
* unresolved defaults will be left untouched rather than being set to
1337-
* NULL. If force_nulls is true, we always set DEFAULT items to NULL, so
1338-
* skip this check in that case --- it isn't an auto-updatable view.
1328+
* NULL.
13391329
*/
13401330
isAutoUpdatableView= false;
1341-
if (!force_nulls&&
1342-
target_relation->rd_rel->relkind==RELKIND_VIEW&&
1331+
if (target_relation->rd_rel->relkind==RELKIND_VIEW&&
13431332
!view_has_instead_trigger(target_relation,CMD_INSERT))
13441333
{
13451334
List*locks;
@@ -1397,9 +1386,10 @@ rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte, int rti,
13971386

13981387
if (attrno==0)
13991388
elog(ERROR,"cannot set value in column %d to DEFAULT",i);
1389+
Assert(attrno>0&&attrno <=target_relation->rd_att->natts);
14001390
att_tup=TupleDescAttr(target_relation->rd_att,attrno-1);
14011391

1402-
if (!force_nulls&& !att_tup->attisdropped)
1392+
if (!att_tup->attisdropped)
14031393
new_expr=build_column_default(target_relation,attrno);
14041394
else
14051395
new_expr=NULL;/* force a NULL if dropped */
@@ -1449,6 +1439,50 @@ rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte, int rti,
14491439
returnallReplaced;
14501440
}
14511441

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

14531487
/*
14541488
* rewriteTargetListUD - rewrite UPDATE/DELETE targetlist as needed
@@ -3659,7 +3693,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
36593693
parsetree->resultRelation);
36603694
/* ... and the VALUES expression lists */
36613695
if (!rewriteValuesRTE(parsetree,values_rte,values_rte_index,
3662-
rt_entry_relation, false))
3696+
rt_entry_relation))
36633697
defaults_remaining= true;
36643698
}
36653699
else
@@ -3739,9 +3773,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
37393773
RangeTblEntry*values_rte=rt_fetch(values_rte_index,
37403774
pt->rtable);
37413775

3742-
rewriteValuesRTE(pt,values_rte,values_rte_index,
3743-
rt_entry_relation,
3744-
true);/* Force remaining defaults to NULL */
3776+
rewriteValuesRTEToNulls(pt,values_rte);
37453777
}
37463778
}
37473779

‎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