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

Commit1795897

Browse files
committed
Allow a multi-row INSERT to specify DEFAULTs for a generated column.
One can say "INSERT INTO tab(generated_col) VALUES (DEFAULT)" and notdraw an error. But the equivalent case for a multi-row VALUES listalways threw an error, even if one properly said DEFAULT in each row.Fix that. While here, improve the test cases for nearby logic aboutOVERRIDING SYSTEM/USER values.Dean RasheedDiscussion:https://postgr.es/m/9q0sgcr416t.fsf@gmx.us
1 parent9fe649e commit1795897

File tree

5 files changed

+357
-32
lines changed

5 files changed

+357
-32
lines changed

‎src/backend/rewrite/rewriteHandler.c

Lines changed: 215 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -69,13 +69,18 @@ static List *rewriteTargetListIU(List *targetList,
6969
CmdTypecommandType,
7070
OverridingKindoverride,
7171
Relationtarget_relation,
72-
intresult_rti);
72+
intresult_rti,
73+
RangeTblEntry*values_rte,
74+
intvalues_rte_index,
75+
Bitmapset**unused_values_attrnos);
7376
staticTargetEntry*process_matched_tle(TargetEntry*src_tle,
7477
TargetEntry*prior_tle,
7578
constchar*attrName);
7679
staticNode*get_assignment_input(Node*node);
80+
staticBitmapset*findDefaultOnlyColumns(RangeTblEntry*rte);
7781
staticboolrewriteValuesRTE(Query*parsetree,RangeTblEntry*rte,intrti,
78-
Relationtarget_relation,boolforce_nulls);
82+
Relationtarget_relation,boolforce_nulls,
83+
Bitmapset*unused_cols);
7984
staticvoidmarkQueryForLocking(Query*qry,Node*jtnode,
8085
LockClauseStrengthstrength,LockWaitPolicywaitPolicy,
8186
boolpushedDown);
@@ -708,13 +713,25 @@ adjustJoinTreeList(Query *parsetree, bool removert, int rt_index)
708713
* is incorrect by this light, since child relations might have different
709714
* column ordering, but the planner will fix things by re-sorting the tlist
710715
* for each child.)
716+
*
717+
* If values_rte is non-NULL (i.e., we are doing a multi-row INSERT using
718+
* values from a VALUES RTE), we populate *unused_values_attrnos with the
719+
* attribute numbers of any unused columns from the VALUES RTE. This can
720+
* happen for identity and generated columns whose targetlist entries are
721+
* replaced with generated expressions (if INSERT ... OVERRIDING USER VALUE is
722+
* used, or all the values to be inserted are DEFAULT). This information is
723+
* required by rewriteValuesRTE() to handle any DEFAULT items in the unused
724+
* columns. The caller must have initialized *unused_values_attrnos to NULL.
711725
*/
712726
staticList*
713727
rewriteTargetListIU(List*targetList,
714728
CmdTypecommandType,
715729
OverridingKindoverride,
716730
Relationtarget_relation,
717-
intresult_rti)
731+
intresult_rti,
732+
RangeTblEntry*values_rte,
733+
intvalues_rte_index,
734+
Bitmapset**unused_values_attrnos)
718735
{
719736
TargetEntry**new_tles;
720737
List*new_tlist=NIL;
@@ -724,6 +741,7 @@ rewriteTargetListIU(List *targetList,
724741
next_junk_attrno,
725742
numattrs;
726743
ListCell*temp;
744+
Bitmapset*default_only_cols=NULL;
727745

728746
/*
729747
* We process the normal (non-junk) attributes by scanning the input tlist
@@ -803,30 +821,106 @@ rewriteTargetListIU(List *targetList,
803821

804822
if (commandType==CMD_INSERT)
805823
{
824+
intvalues_attrno=0;
825+
826+
/* Source attribute number for values that come from a VALUES RTE */
827+
if (values_rte&&new_tle&&IsA(new_tle->expr,Var))
828+
{
829+
Var*var= (Var*)new_tle->expr;
830+
831+
if (var->varno==values_rte_index)
832+
values_attrno=var->varattno;
833+
}
834+
835+
/*
836+
* Can only insert DEFAULT into GENERATED ALWAYS identity columns,
837+
* unless either OVERRIDING USER VALUE or OVERRIDING SYSTEM VALUE
838+
* is specified.
839+
*/
806840
if (att_tup->attidentity==ATTRIBUTE_IDENTITY_ALWAYS&& !apply_default)
807841
{
808842
if (override==OVERRIDING_USER_VALUE)
809843
apply_default= true;
810844
elseif (override!=OVERRIDING_SYSTEM_VALUE)
811-
ereport(ERROR,
812-
(errcode(ERRCODE_GENERATED_ALWAYS),
813-
errmsg("cannot insert into column \"%s\"",NameStr(att_tup->attname)),
814-
errdetail("Column \"%s\" is an identity column defined as GENERATED ALWAYS.",
815-
NameStr(att_tup->attname)),
816-
errhint("Use OVERRIDING SYSTEM VALUE to override.")));
845+
{
846+
/*
847+
* If this column's values come from a VALUES RTE, test
848+
* whether it contains only SetToDefault items. Since the
849+
* VALUES list might be quite large, we arrange to only
850+
* scan it once.
851+
*/
852+
if (values_attrno!=0)
853+
{
854+
if (default_only_cols==NULL)
855+
default_only_cols=findDefaultOnlyColumns(values_rte);
856+
857+
if (bms_is_member(values_attrno,default_only_cols))
858+
apply_default= true;
859+
}
860+
861+
if (!apply_default)
862+
ereport(ERROR,
863+
(errcode(ERRCODE_GENERATED_ALWAYS),
864+
errmsg("cannot insert into column \"%s\"",
865+
NameStr(att_tup->attname)),
866+
errdetail("Column \"%s\" is an identity column defined as GENERATED ALWAYS.",
867+
NameStr(att_tup->attname)),
868+
errhint("Use OVERRIDING SYSTEM VALUE to override.")));
869+
}
817870
}
818871

819-
if (att_tup->attidentity==ATTRIBUTE_IDENTITY_BY_DEFAULT&&override==OVERRIDING_USER_VALUE)
872+
/*
873+
* Although inserting into a GENERATED BY DEFAULT identity column
874+
* is allowed, apply the default if OVERRIDING USER VALUE is
875+
* specified.
876+
*/
877+
if (att_tup->attidentity==ATTRIBUTE_IDENTITY_BY_DEFAULT&&
878+
override==OVERRIDING_USER_VALUE)
820879
apply_default= true;
821880

881+
/*
882+
* Can only insert DEFAULT into generated columns, regardless of
883+
* any OVERRIDING clauses.
884+
*/
822885
if (att_tup->attgenerated&& !apply_default)
823-
ereport(ERROR,
824-
(errcode(ERRCODE_SYNTAX_ERROR),
825-
errmsg("cannot insert into column \"%s\"",NameStr(att_tup->attname)),
826-
errdetail("Column \"%s\" is a generated column.",
827-
NameStr(att_tup->attname))));
886+
{
887+
/*
888+
* If this column's values come from a VALUES RTE, test
889+
* whether it contains only SetToDefault items, as above.
890+
*/
891+
if (values_attrno!=0)
892+
{
893+
if (default_only_cols==NULL)
894+
default_only_cols=findDefaultOnlyColumns(values_rte);
895+
896+
if (bms_is_member(values_attrno,default_only_cols))
897+
apply_default= true;
898+
}
899+
900+
if (!apply_default)
901+
ereport(ERROR,
902+
(errcode(ERRCODE_SYNTAX_ERROR),
903+
errmsg("cannot insert into column \"%s\"",
904+
NameStr(att_tup->attname)),
905+
errdetail("Column \"%s\" is a generated column.",
906+
NameStr(att_tup->attname))));
907+
}
908+
909+
/*
910+
* For an INSERT from a VALUES RTE, return the attribute numbers
911+
* of any VALUES columns that will no longer be used (due to the
912+
* targetlist entry being replaced by a default expression).
913+
*/
914+
if (values_attrno!=0&&apply_default&&unused_values_attrnos)
915+
*unused_values_attrnos=bms_add_member(*unused_values_attrnos,
916+
values_attrno);
828917
}
829918

919+
/*
920+
* Updates to identity and generated columns follow the same rules as
921+
* above, except that UPDATE doesn't admit OVERRIDING clauses. Also,
922+
* the source can't be a VALUES RTE, so we needn't consider that.
923+
*/
830924
if (commandType==CMD_UPDATE)
831925
{
832926
if (att_tup->attidentity==ATTRIBUTE_IDENTITY_ALWAYS&&new_tle&& !apply_default)
@@ -1219,6 +1313,62 @@ searchForDefault(RangeTblEntry *rte)
12191313
return false;
12201314
}
12211315

1316+
1317+
/*
1318+
* Search a VALUES RTE for columns that contain only SetToDefault items,
1319+
* returning a Bitmapset containing the attribute numbers of any such columns.
1320+
*/
1321+
staticBitmapset*
1322+
findDefaultOnlyColumns(RangeTblEntry*rte)
1323+
{
1324+
Bitmapset*default_only_cols=NULL;
1325+
ListCell*lc;
1326+
1327+
foreach(lc,rte->values_lists)
1328+
{
1329+
List*sublist= (List*)lfirst(lc);
1330+
ListCell*lc2;
1331+
inti;
1332+
1333+
if (default_only_cols==NULL)
1334+
{
1335+
/* Populate the initial result bitmap from the first row */
1336+
i=0;
1337+
foreach(lc2,sublist)
1338+
{
1339+
Node*col= (Node*)lfirst(lc2);
1340+
1341+
i++;
1342+
if (IsA(col,SetToDefault))
1343+
default_only_cols=bms_add_member(default_only_cols,i);
1344+
}
1345+
}
1346+
else
1347+
{
1348+
/* Update the result bitmap from this next row */
1349+
i=0;
1350+
foreach(lc2,sublist)
1351+
{
1352+
Node*col= (Node*)lfirst(lc2);
1353+
1354+
i++;
1355+
if (!IsA(col,SetToDefault))
1356+
default_only_cols=bms_del_member(default_only_cols,i);
1357+
}
1358+
}
1359+
1360+
/*
1361+
* If no column in the rows read so far contains only DEFAULT items,
1362+
* we are done.
1363+
*/
1364+
if (bms_is_empty(default_only_cols))
1365+
break;
1366+
}
1367+
1368+
returndefault_only_cols;
1369+
}
1370+
1371+
12221372
/*
12231373
* When processing INSERT ... VALUES with a VALUES RTE (ie, multiple VALUES
12241374
* lists), we have to replace any DEFAULT items in the VALUES lists with
@@ -1246,19 +1396,31 @@ searchForDefault(RangeTblEntry *rte)
12461396
* an insert into an auto-updatable view, and the product queries are inserts
12471397
* into a rule-updatable view.
12481398
*
1399+
* Finally, if a DEFAULT item is found in a column mentioned in unused_cols,
1400+
* it is explicitly set to NULL. This happens for columns in the VALUES RTE
1401+
* whose corresponding targetlist entries have already been replaced with the
1402+
* relation's default expressions, so that any values in those columns of the
1403+
* VALUES RTE are no longer used. This can happen for identity and generated
1404+
* columns (if INSERT ... OVERRIDING USER VALUE is used, or all the values to
1405+
* be inserted are DEFAULT). In principle we could replace all entries in
1406+
* such a column with NULL, whether DEFAULT or not; but it doesn't seem worth
1407+
* the trouble.
1408+
*
12491409
* Note that we may have subscripted or field assignment targetlist entries,
12501410
* as well as more complex expressions from already-replaced DEFAULT items if
12511411
* we have recursed to here for an auto-updatable view. However, it ought to
1252-
* be impossible for such entries to have DEFAULTs assigned to them --- we
1253-
* should only have to replace DEFAULT items for targetlist entries that
1254-
* contain simple Vars referencing the VALUES RTE.
1412+
* be impossible for such entries to have DEFAULTs assigned to them, except
1413+
* for unused columns, as described above --- we should only have to replace
1414+
* DEFAULT items for targetlist entries that contain simple Vars referencing
1415+
* the VALUES RTE, or which are no longer referred to by the targetlist.
12551416
*
12561417
* Returns true if all DEFAULT items were replaced, and false if some were
12571418
* left untouched.
12581419
*/
12591420
staticbool
12601421
rewriteValuesRTE(Query*parsetree,RangeTblEntry*rte,intrti,
1261-
Relationtarget_relation,boolforce_nulls)
1422+
Relationtarget_relation,boolforce_nulls,
1423+
Bitmapset*unused_cols)
12621424
{
12631425
List*newValues;
12641426
ListCell*lc;
@@ -1282,8 +1444,8 @@ rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte, int rti,
12821444
* Scan the targetlist for entries referring to the VALUES RTE, and note
12831445
* the target attributes. As noted above, we should only need to do this
12841446
* for targetlist entries containing simple Vars --- nothing else in the
1285-
* VALUES RTE should contain DEFAULT items, and we complain if such a
1286-
* thing does occur.
1447+
* VALUES RTE should contain DEFAULT items (except possibly for unused
1448+
*columns), and we complain if such athing does occur.
12871449
*/
12881450
numattrs=list_length(linitial(rte->values_lists));
12891451
attrnos= (int*)palloc0(numattrs*sizeof(int));
@@ -1370,6 +1532,22 @@ rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte, int rti,
13701532
Form_pg_attributeatt_tup;
13711533
Node*new_expr;
13721534

1535+
/*
1536+
* If this column isn't used, just replace the DEFAULT with
1537+
* NULL (attrno will be 0 in this case because the targetlist
1538+
* entry will have been replaced by the default expression).
1539+
*/
1540+
if (bms_is_member(i,unused_cols))
1541+
{
1542+
SetToDefault*def= (SetToDefault*)col;
1543+
1544+
newList=lappend(newList,
1545+
makeNullConst(def->typeId,
1546+
def->typeMod,
1547+
def->collation));
1548+
continue;
1549+
}
1550+
13731551
if (attrno==0)
13741552
elog(ERROR,"cannot set value in column %d to DEFAULT",i);
13751553
att_tup=TupleDescAttr(target_relation->rd_att,attrno-1);
@@ -3614,15 +3792,21 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
36143792

36153793
if (values_rte)
36163794
{
3795+
Bitmapset*unused_values_attrnos=NULL;
3796+
36173797
/* Process the main targetlist ... */
36183798
parsetree->targetList=rewriteTargetListIU(parsetree->targetList,
36193799
parsetree->commandType,
36203800
parsetree->override,
36213801
rt_entry_relation,
3622-
parsetree->resultRelation);
3802+
parsetree->resultRelation,
3803+
values_rte,
3804+
values_rte_index,
3805+
&unused_values_attrnos);
36233806
/* ... and the VALUES expression lists */
36243807
if (!rewriteValuesRTE(parsetree,values_rte,values_rte_index,
3625-
rt_entry_relation, false))
3808+
rt_entry_relation, false,
3809+
unused_values_attrnos))
36263810
defaults_remaining= true;
36273811
}
36283812
else
@@ -3633,7 +3817,8 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
36333817
parsetree->commandType,
36343818
parsetree->override,
36353819
rt_entry_relation,
3636-
parsetree->resultRelation);
3820+
parsetree->resultRelation,
3821+
NULL,0,NULL);
36373822
}
36383823

36393824
if (parsetree->onConflict&&
@@ -3644,7 +3829,8 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
36443829
CMD_UPDATE,
36453830
parsetree->override,
36463831
rt_entry_relation,
3647-
parsetree->resultRelation);
3832+
parsetree->resultRelation,
3833+
NULL,0,NULL);
36483834
}
36493835
}
36503836
elseif (event==CMD_UPDATE)
@@ -3654,7 +3840,8 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
36543840
parsetree->commandType,
36553841
parsetree->override,
36563842
rt_entry_relation,
3657-
parsetree->resultRelation);
3843+
parsetree->resultRelation,
3844+
NULL,0,NULL);
36583845

36593846
/* Also populate extraUpdatedCols (for generated columns) */
36603847
fill_extraUpdatedCols(rt_entry,rt_entry_relation);
@@ -3704,7 +3891,8 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
37043891

37053892
rewriteValuesRTE(pt,values_rte,values_rte_index,
37063893
rt_entry_relation,
3707-
true);/* Force remaining defaults to NULL */
3894+
true,/* Force remaining defaults to NULL */
3895+
NULL);
37083896
}
37093897
}
37103898

0 commit comments

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