diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c new file mode 100644 index 41dd670..966cab5 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -69,13 +69,18 @@ static List *rewriteTargetListIU(List *t CmdType commandType, OverridingKind override, Relation target_relation, - int result_rti); + int result_rti, + RangeTblEntry *values_rte, + int values_rte_index, + Bitmapset **unused_values_attrnos); static TargetEntry *process_matched_tle(TargetEntry *src_tle, TargetEntry *prior_tle, const char *attrName); static Node *get_assignment_input(Node *node); +static Bitmapset *findDefaultOnlyColumns(RangeTblEntry *rte); static bool rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte, int rti, - Relation target_relation, bool force_nulls); + Relation target_relation, bool force_nulls, + Bitmapset *unused_cols); static void markQueryForLocking(Query *qry, Node *jtnode, LockClauseStrength strength, LockWaitPolicy waitPolicy, bool pushedDown); @@ -708,13 +713,25 @@ adjustJoinTreeList(Query *parsetree, boo * is incorrect by this light, since child relations might have different * column ordering, but the planner will fix things by re-sorting the tlist * for each child.) + * + * If values_rte is non-NULL (i.e., we are doing a multi-row INSERT using + * values from a VALUES RTE), we populate unused_values_attrnos with the + * attribute numbers of any unused columns from the VALUES RTE. This can + * happen for identity and generated columns whose targetlist entries are + * replaced with generated expressions (if INSERT ... OVERRIDING USER VALUE is + * used, or all the values to be inserted are DEFAULT). This information is + * required by rewriteValuesRTE() to handle any DEFAULT items in the unused + * columns. */ static List * rewriteTargetListIU(List *targetList, CmdType commandType, OverridingKind override, Relation target_relation, - int result_rti) + int result_rti, + RangeTblEntry *values_rte, + int values_rte_index, + Bitmapset **unused_values_attrnos) { TargetEntry **new_tles; List *new_tlist = NIL; @@ -724,6 +741,7 @@ rewriteTargetListIU(List *targetList, next_junk_attrno, numattrs; ListCell *temp; + Bitmapset *default_only_cols = NULL; /* * We process the normal (non-junk) attributes by scanning the input tlist @@ -803,30 +821,102 @@ rewriteTargetListIU(List *targetList, if (commandType == CMD_INSERT) { + int values_attrno = 0; + + /* Source attribute number for values that come from a VALUES RTE */ + if (values_rte && new_tle && IsA(new_tle->expr, Var)) + { + Var *var = (Var *) new_tle->expr; + + if (var->varno == values_rte_index) + values_attrno = var->varattno; + } + + /* + * Can only insert DEFAULT into GENERATED ALWAYS identity columns, + * unless either OVERRIDING USER VALUE or OVERRIDING SYSTEM VALUE + * is specified. + */ if (att_tup->attidentity == ATTRIBUTE_IDENTITY_ALWAYS && !apply_default) { if (override == OVERRIDING_USER_VALUE) apply_default = true; else if (override != OVERRIDING_SYSTEM_VALUE) - ereport(ERROR, - (errcode(ERRCODE_GENERATED_ALWAYS), - errmsg("cannot insert into column \"%s\"", NameStr(att_tup->attname)), - errdetail("Column \"%s\" is an identity column defined as GENERATED ALWAYS.", - NameStr(att_tup->attname)), - errhint("Use OVERRIDING SYSTEM VALUE to override."))); + { + /* + * If this column's values come from a VALUES RTE, test + * whether it contains only SetToDefault items. Since the + * VALUES list might be quite large, we arrange to only + * scan it once. + */ + if (values_attrno != 0) + { + if (default_only_cols == NULL) + default_only_cols = findDefaultOnlyColumns(values_rte); + + if (bms_is_member(values_attrno, default_only_cols)) + apply_default = true; + } + + if (!apply_default) + ereport(ERROR, + (errcode(ERRCODE_GENERATED_ALWAYS), + errmsg("cannot insert into column \"%s\"", NameStr(att_tup->attname)), + errdetail("Column \"%s\" is an identity column defined as GENERATED ALWAYS.", + NameStr(att_tup->attname)), + errhint("Use OVERRIDING SYSTEM VALUE to override."))); + } } + /* + * Can insert any value into GENERATED BY DEFAULT identity + * columns, but we apply the default if OVERRIDING USER VALUE is + * specified. + */ if (att_tup->attidentity == ATTRIBUTE_IDENTITY_BY_DEFAULT && override == OVERRIDING_USER_VALUE) apply_default = true; + /* + * Can only insert DEFAULT into generated columns, regardless of + * any OVERRIDING clauses. + */ if (att_tup->attgenerated && !apply_default) - ereport(ERROR, - (errcode(ERRCODE_SYNTAX_ERROR), - errmsg("cannot insert into column \"%s\"", NameStr(att_tup->attname)), - errdetail("Column \"%s\" is a generated column.", - NameStr(att_tup->attname)))); + { + /* + * If this column's values come from a VALUES RTE, test + * whether it contains only SetToDefault items. + */ + if (values_attrno != 0) + { + if (default_only_cols == NULL) + default_only_cols = findDefaultOnlyColumns(values_rte); + + if (bms_is_member(values_attrno, default_only_cols)) + apply_default = true; + } + + if (!apply_default) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("cannot insert into column \"%s\"", NameStr(att_tup->attname)), + errdetail("Column \"%s\" is a generated column.", + NameStr(att_tup->attname)))); + } + + /* + * For an INSERT from a VALUES RTE, return the attribute numbers + * of any columns that will no longer be used (due to the + * targetlist entry being replaced by a default expression). + */ + if (values_attrno != 0 && apply_default && unused_values_attrnos) + *unused_values_attrnos = bms_add_member(*unused_values_attrnos, + values_attrno); } + /* + * Updates to identity and generated columns follow the same rules as + * above, except that UPDATE doesn't admit OVERRIDING clauses. + */ if (commandType == CMD_UPDATE) { if (att_tup->attidentity == ATTRIBUTE_IDENTITY_ALWAYS && new_tle && !apply_default) @@ -1219,6 +1309,62 @@ searchForDefault(RangeTblEntry *rte) return false; } + +/* + * Search a VALUES RTE for columns that contain only SetToDefault items, + * returning a Bitmapset containing the attribute numbers of any such columns. + */ +static Bitmapset * +findDefaultOnlyColumns(RangeTblEntry *rte) +{ + Bitmapset *default_only_cols = NULL; + ListCell *lc; + + foreach(lc, rte->values_lists) + { + List *sublist = (List *) lfirst(lc); + ListCell *lc2; + int i; + + if (default_only_cols == NULL) + { + /* Populate the initial result bitmap from the first row */ + i = 0; + foreach(lc2, sublist) + { + Node *col = (Node *) lfirst(lc2); + + i++; + if (IsA(col, SetToDefault)) + default_only_cols = bms_add_member(default_only_cols, i); + } + } + else + { + /* Update the result bitmap from this next row */ + i = 0; + foreach(lc2, sublist) + { + Node *col = (Node *) lfirst(lc2); + + i++; + if (!IsA(col, SetToDefault)) + default_only_cols = bms_del_member(default_only_cols, i); + } + } + + /* + * If no column in the rows read so far contains only DEFAULT items, + * we are done. + */ + if (default_only_cols == NULL) + break; + } + + return default_only_cols; +} + + /* * When processing INSERT ... VALUES with a VALUES RTE (ie, multiple VALUES * lists), we have to replace any DEFAULT items in the VALUES lists with @@ -1246,19 +1392,29 @@ searchForDefault(RangeTblEntry *rte) * an insert into an auto-updatable view, and the product queries are inserts * into a rule-updatable view. * + * Finally, if a DEFAULT item is found in a column mentioned in unused_cols, + * it is explicitly set to NULL. This happens for columns in the VALUES RTE + * whose corresponding targetlist entries have already been replaced with the + * relation's default expressions, and any values in those columns of the + * VALUES RTE are no longer used. This can happen for identity and generated + * columns (if INSERT ... OVERRIDING USER VALUE is used, or all the values to + * be inserted are DEFAULT). + * * Note that we may have subscripted or field assignment targetlist entries, * as well as more complex expressions from already-replaced DEFAULT items if * we have recursed to here for an auto-updatable view. However, it ought to - * be impossible for such entries to have DEFAULTs assigned to them --- we - * should only have to replace DEFAULT items for targetlist entries that - * contain simple Vars referencing the VALUES RTE. + * be impossible for such entries to have DEFAULTs assigned to them, except + * for unused columns, as described above --- we should only have to replace + * DEFAULT items for targetlist entries that contain simple Vars referencing + * the VALUES RTE, or which are no longer referred to by the targetlist. * * Returns true if all DEFAULT items were replaced, and false if some were * left untouched. */ static bool rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte, int rti, - Relation target_relation, bool force_nulls) + Relation target_relation, bool force_nulls, + Bitmapset *unused_cols) { List *newValues; ListCell *lc; @@ -1282,8 +1438,8 @@ rewriteValuesRTE(Query *parsetree, Range * Scan the targetlist for entries referring to the VALUES RTE, and note * the target attributes. As noted above, we should only need to do this * for targetlist entries containing simple Vars --- nothing else in the - * VALUES RTE should contain DEFAULT items, and we complain if such a - * thing does occur. + * VALUES RTE should contain DEFAULT items (except possibly for unused + * columns), and we complain if such a thing does occur. */ numattrs = list_length(linitial(rte->values_lists)); attrnos = (int *) palloc0(numattrs * sizeof(int)); @@ -1370,6 +1526,22 @@ rewriteValuesRTE(Query *parsetree, Range Form_pg_attribute att_tup; Node *new_expr; + /* + * If this column isn't used, just replace the DEFAULT with + * NULL (attrno will be 0 in this case because the targetlist + * entry will have been replaced by the default expression). + */ + if (bms_is_member(i, unused_cols)) + { + SetToDefault *def = (SetToDefault *) col; + + newList = lappend(newList, + makeNullConst(def->typeId, + def->typeMod, + def->collation)); + continue; + } + if (attrno == 0) elog(ERROR, "cannot set value in column %d to DEFAULT", i); att_tup = TupleDescAttr(target_relation->rd_att, attrno - 1); @@ -3614,15 +3786,21 @@ RewriteQuery(Query *parsetree, List *rew if (values_rte) { + Bitmapset *unused_values_attrnos = NULL; + /* Process the main targetlist ... */ parsetree->targetList = rewriteTargetListIU(parsetree->targetList, parsetree->commandType, parsetree->override, rt_entry_relation, - parsetree->resultRelation); + parsetree->resultRelation, + values_rte, + values_rte_index, + &unused_values_attrnos); /* ... and the VALUES expression lists */ if (!rewriteValuesRTE(parsetree, values_rte, values_rte_index, - rt_entry_relation, false)) + rt_entry_relation, false, + unused_values_attrnos)) defaults_remaining = true; } else @@ -3633,7 +3811,8 @@ RewriteQuery(Query *parsetree, List *rew parsetree->commandType, parsetree->override, rt_entry_relation, - parsetree->resultRelation); + parsetree->resultRelation, + NULL, 0, NULL); } if (parsetree->onConflict && @@ -3644,7 +3823,8 @@ RewriteQuery(Query *parsetree, List *rew CMD_UPDATE, parsetree->override, rt_entry_relation, - parsetree->resultRelation); + parsetree->resultRelation, + NULL, 0, NULL); } } else if (event == CMD_UPDATE) @@ -3654,7 +3834,8 @@ RewriteQuery(Query *parsetree, List *rew parsetree->commandType, parsetree->override, rt_entry_relation, - parsetree->resultRelation); + parsetree->resultRelation, + NULL, 0, NULL); /* Also populate extraUpdatedCols (for generated columns) */ fill_extraUpdatedCols(rt_entry, rt_entry_relation); @@ -3704,7 +3885,8 @@ RewriteQuery(Query *parsetree, List *rew rewriteValuesRTE(pt, values_rte, values_rte_index, rt_entry_relation, - true); /* Force remaining defaults to NULL */ + true, /* Force remaining defaults to NULL */ + NULL); } } diff --git a/src/test/regress/expected/generated.out b/src/test/regress/expected/generated.out new file mode 100644 index 4b06260..559fafa --- a/src/test/regress/expected/generated.out +++ b/src/test/regress/expected/generated.out @@ -91,17 +91,30 @@ ERROR: for a generated column, GENERATE LINE 1: ...E gtest_err_8 (a int PRIMARY KEY, b int GENERATED BY DEFAULT... ^ INSERT INTO gtest1 VALUES (1); -INSERT INTO gtest1 VALUES (2, DEFAULT); +INSERT INTO gtest1 VALUES (2, DEFAULT); -- ok INSERT INTO gtest1 VALUES (3, 33); -- error ERROR: cannot insert into column "b" DETAIL: Column "b" is a generated column. +INSERT INTO gtest1 VALUES (3, 33), (4, 44); -- error +ERROR: cannot insert into column "b" +DETAIL: Column "b" is a generated column. +INSERT INTO gtest1 VALUES (3, DEFAULT), (4, 44); -- error +ERROR: cannot insert into column "b" +DETAIL: Column "b" is a generated column. +INSERT INTO gtest1 VALUES (3, 33), (4, DEFAULT); -- error +ERROR: cannot insert into column "b" +DETAIL: Column "b" is a generated column. +INSERT INTO gtest1 VALUES (3, DEFAULT), (4, DEFAULT); -- ok SELECT * FROM gtest1 ORDER BY a; a | b ---+--- 1 | 2 2 | 4 -(2 rows) + 3 | 6 + 4 | 8 +(4 rows) +DELETE FROM gtest1 WHERE a >= 3; UPDATE gtest1 SET b = DEFAULT WHERE a = 1; UPDATE gtest1 SET b = 11 WHERE a = 1; -- error ERROR: column "b" can only be updated to DEFAULT @@ -179,9 +192,37 @@ SELECT * FROM gtest1v; 3 | 6 (1 row) -INSERT INTO gtest1v VALUES (4, 8); -- fails +INSERT INTO gtest1v VALUES (4, 8); -- error +ERROR: cannot insert into column "b" +DETAIL: Column "b" is a generated column. +INSERT INTO gtest1v VALUES (5, DEFAULT); -- ok +INSERT INTO gtest1v VALUES (6, 66), (7, 77); -- error ERROR: cannot insert into column "b" DETAIL: Column "b" is a generated column. +INSERT INTO gtest1v VALUES (6, DEFAULT), (7, 77); -- error +ERROR: cannot insert into column "b" +DETAIL: Column "b" is a generated column. +INSERT INTO gtest1v VALUES (6, 66), (7, DEFAULT); -- error +ERROR: cannot insert into column "b" +DETAIL: Column "b" is a generated column. +INSERT INTO gtest1v VALUES (6, DEFAULT), (7, DEFAULT); -- ok +ALTER VIEW gtest1v ALTER COLUMN b SET DEFAULT 100; +INSERT INTO gtest1v VALUES (8, DEFAULT); -- error +ERROR: cannot insert into column "b" +DETAIL: Column "b" is a generated column. +INSERT INTO gtest1v VALUES (8, DEFAULT), (9, DEFAULT); -- error +ERROR: cannot insert into column "b" +DETAIL: Column "b" is a generated column. +SELECT * FROM gtest1v; + a | b +---+---- + 3 | 6 + 5 | 10 + 6 | 12 + 7 | 14 +(4 rows) + +DELETE FROM gtest1v WHERE a >= 5; DROP VIEW gtest1v; -- CTEs WITH foo AS (SELECT * FROM gtest1) SELECT * FROM foo; diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out new file mode 100644 index 2238f89..7e36b36 --- a/src/test/regress/expected/identity.out +++ b/src/test/regress/expected/identity.out @@ -105,6 +105,62 @@ SELECT * FROM itest4; (2 rows) -- VALUES RTEs +CREATE TABLE itest5 (a int generated always as identity, b text); +INSERT INTO itest5 VALUES (1, 'a'); -- error +ERROR: cannot insert into column "a" +DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS. +HINT: Use OVERRIDING SYSTEM VALUE to override. +INSERT INTO itest5 VALUES (DEFAULT, 'a'); -- ok +INSERT INTO itest5 VALUES (2, 'b'), (3, 'c'); -- error +ERROR: cannot insert into column "a" +DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS. +HINT: Use OVERRIDING SYSTEM VALUE to override. +INSERT INTO itest5 VALUES (DEFAULT, 'b'), (3, 'c'); -- error +ERROR: cannot insert into column "a" +DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS. +HINT: Use OVERRIDING SYSTEM VALUE to override. +INSERT INTO itest5 VALUES (2, 'b'), (DEFAULT, 'c'); -- error +ERROR: cannot insert into column "a" +DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS. +HINT: Use OVERRIDING SYSTEM VALUE to override. +INSERT INTO itest5 VALUES (DEFAULT, 'b'), (DEFAULT, 'c'); -- ok +INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (-1, 'aa'); +INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (-2, 'bb'), (-3, 'cc'); +INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (DEFAULT, 'dd'), (-4, 'ee'); +INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (-5, 'ff'), (DEFAULT, 'gg'); +INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (DEFAULT, 'hh'), (DEFAULT, 'ii'); +INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-1, 'aaa'); +INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-2, 'bbb'), (-3, 'ccc'); +INSERT INTO itest5 OVERRIDING USER VALUE VALUES (DEFAULT, 'ddd'), (-4, 'eee'); +INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-5, 'fff'), (DEFAULT, 'ggg'); +INSERT INTO itest5 OVERRIDING USER VALUE VALUES (DEFAULT, 'hhh'), (DEFAULT, 'iii'); +SELECT * FROM itest5; + a | b +----+----- + 1 | a + 2 | b + 3 | c + -1 | aa + -2 | bb + -3 | cc + 4 | dd + -4 | ee + -5 | ff + 5 | gg + 6 | hh + 7 | ii + 8 | aaa + 9 | bbb + 10 | ccc + 11 | ddd + 12 | eee + 13 | fff + 14 | ggg + 15 | hhh + 16 | iii +(21 rows) + +DROP TABLE itest5; INSERT INTO itest3 VALUES (DEFAULT, 'a'); INSERT INTO itest3 VALUES (DEFAULT, 'b'), (DEFAULT, 'c'); SELECT * FROM itest3; diff --git a/src/test/regress/sql/generated.sql b/src/test/regress/sql/generated.sql new file mode 100644 index c86ad34..bd2b0bf --- a/src/test/regress/sql/generated.sql +++ b/src/test/regress/sql/generated.sql @@ -41,10 +41,15 @@ CREATE TABLE gtest_err_7d (a int PRIMARY CREATE TABLE gtest_err_8 (a int PRIMARY KEY, b int GENERATED BY DEFAULT AS (a * 2) STORED); INSERT INTO gtest1 VALUES (1); -INSERT INTO gtest1 VALUES (2, DEFAULT); +INSERT INTO gtest1 VALUES (2, DEFAULT); -- ok INSERT INTO gtest1 VALUES (3, 33); -- error +INSERT INTO gtest1 VALUES (3, 33), (4, 44); -- error +INSERT INTO gtest1 VALUES (3, DEFAULT), (4, 44); -- error +INSERT INTO gtest1 VALUES (3, 33), (4, DEFAULT); -- error +INSERT INTO gtest1 VALUES (3, DEFAULT), (4, DEFAULT); -- ok SELECT * FROM gtest1 ORDER BY a; +DELETE FROM gtest1 WHERE a >= 3; UPDATE gtest1 SET b = DEFAULT WHERE a = 1; UPDATE gtest1 SET b = 11 WHERE a = 1; -- error @@ -75,7 +80,19 @@ SELECT * FROM gtest1 ORDER BY a; -- views CREATE VIEW gtest1v AS SELECT * FROM gtest1; SELECT * FROM gtest1v; -INSERT INTO gtest1v VALUES (4, 8); -- fails +INSERT INTO gtest1v VALUES (4, 8); -- error +INSERT INTO gtest1v VALUES (5, DEFAULT); -- ok +INSERT INTO gtest1v VALUES (6, 66), (7, 77); -- error +INSERT INTO gtest1v VALUES (6, DEFAULT), (7, 77); -- error +INSERT INTO gtest1v VALUES (6, 66), (7, DEFAULT); -- error +INSERT INTO gtest1v VALUES (6, DEFAULT), (7, DEFAULT); -- ok + +ALTER VIEW gtest1v ALTER COLUMN b SET DEFAULT 100; +INSERT INTO gtest1v VALUES (8, DEFAULT); -- error +INSERT INTO gtest1v VALUES (8, DEFAULT), (9, DEFAULT); -- error + +SELECT * FROM gtest1v; +DELETE FROM gtest1v WHERE a >= 5; DROP VIEW gtest1v; -- CTEs diff --git a/src/test/regress/sql/identity.sql b/src/test/regress/sql/identity.sql new file mode 100644 index d4bc29a..cdda186 --- a/src/test/regress/sql/identity.sql +++ b/src/test/regress/sql/identity.sql @@ -56,6 +56,29 @@ SELECT * FROM itest4; -- VALUES RTEs +CREATE TABLE itest5 (a int generated always as identity, b text); +INSERT INTO itest5 VALUES (1, 'a'); -- error +INSERT INTO itest5 VALUES (DEFAULT, 'a'); -- ok +INSERT INTO itest5 VALUES (2, 'b'), (3, 'c'); -- error +INSERT INTO itest5 VALUES (DEFAULT, 'b'), (3, 'c'); -- error +INSERT INTO itest5 VALUES (2, 'b'), (DEFAULT, 'c'); -- error +INSERT INTO itest5 VALUES (DEFAULT, 'b'), (DEFAULT, 'c'); -- ok + +INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (-1, 'aa'); +INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (-2, 'bb'), (-3, 'cc'); +INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (DEFAULT, 'dd'), (-4, 'ee'); +INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (-5, 'ff'), (DEFAULT, 'gg'); +INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (DEFAULT, 'hh'), (DEFAULT, 'ii'); + +INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-1, 'aaa'); +INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-2, 'bbb'), (-3, 'ccc'); +INSERT INTO itest5 OVERRIDING USER VALUE VALUES (DEFAULT, 'ddd'), (-4, 'eee'); +INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-5, 'fff'), (DEFAULT, 'ggg'); +INSERT INTO itest5 OVERRIDING USER VALUE VALUES (DEFAULT, 'hhh'), (DEFAULT, 'iii'); + +SELECT * FROM itest5; +DROP TABLE itest5; + INSERT INTO itest3 VALUES (DEFAULT, 'a'); INSERT INTO itest3 VALUES (DEFAULT, 'b'), (DEFAULT, 'c');