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

Commit4604928

Browse files
committed
Fix failure for generated column with a not-null domain constraint.
If a GENERATED column is declared to have a domain data type wherethe domain's constraints disallow null values, INSERT commands failedbecause we built a targetlist that included coercing a null constantto the domain's type. The failure occurred even when the generatedvalue would have been perfectly OK. This is adjacent to the issuesfixed in0da39aa, but we didn't notice for lack of testing a domainwith such a constraint.We aren't going to use the result of the targetlist entry for thegenerated column --- ExecComputeStoredGenerated will overwrite it.So it's not really necessary that it have the exact datatype ofthe generated column. This patch fixes the problem by changingthe targetlist entry to be a null Const of the domain's base type,which should be sufficiently legal. (We do have to tweakExecCheckPlanOutput to accept the situation, though.)This has been broken since we implemented generated columns.However, this patch only applies easily as far back as v14, partlybecause I (tgl) only carried0da39aa back that far, but mostlybecause v14 significantly refactored the handling of INSERT/UPDATEtargetlists. Given the lack of field complaints and the shortremaining support lifetime of v13, I judge the cost-benefit rationot good for devising a version that would work in v13.Reported-by: jian he <jian.universality@gmail.com>Author: jian he <jian.universality@gmail.com>Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>Discussion:https://postgr.es/m/CACJufxG59tip2+9h=rEv-ykOFjt0cbsPVchhi0RTij8bABBA0Q@mail.gmail.comBackpatch-through: 14
1 parent1b47a11 commit4604928

File tree

4 files changed

+80
-27
lines changed

4 files changed

+80
-27
lines changed

‎src/backend/executor/nodeModifyTable.c

Lines changed: 32 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -129,33 +129,53 @@ ExecCheckPlanOutput(Relation resultRel, List *targetList)
129129
attr=TupleDescAttr(resultDesc,attno);
130130
attno++;
131131

132-
if (!attr->attisdropped)
132+
/*
133+
* Special cases here should match planner's expand_insert_targetlist.
134+
*/
135+
if (attr->attisdropped)
133136
{
134-
/* Normal case: demand type match */
135-
if (exprType((Node*)tle->expr)!=attr->atttypid)
137+
/*
138+
* For a dropped column, we can't check atttypid (it's likely 0).
139+
* In any case the planner has most likely inserted an INT4 null.
140+
* What we insist on is just *some* NULL constant.
141+
*/
142+
if (!IsA(tle->expr,Const)||
143+
!((Const*)tle->expr)->constisnull)
136144
ereport(ERROR,
137145
(errcode(ERRCODE_DATATYPE_MISMATCH),
138146
errmsg("table row type and query-specified row type do not match"),
139-
errdetail("Table has type %s at ordinal position %d, but query expects %s.",
140-
format_type_be(attr->atttypid),
141-
attno,
142-
format_type_be(exprType((Node*)tle->expr)))));
147+
errdetail("Query provides a value for a dropped column at ordinal position %d.",
148+
attno)));
143149
}
144-
else
150+
elseif (attr->attgenerated)
145151
{
146152
/*
147-
* For a dropped column, we can't check atttypid (it's likely 0).
148-
* In any case the planner has most likely inserted an INT4 null.
149-
* What we insist on is just *some* NULL constant.
153+
* For a generated column, the planner will have inserted a null
154+
* of the column's base type (to avoid possibly failing on domain
155+
* not-null constraints). It doesn't seem worth insisting on that
156+
* exact type though, since a null value is type-independent. As
157+
* above, just insist on *some* NULL constant.
150158
*/
151159
if (!IsA(tle->expr,Const)||
152160
!((Const*)tle->expr)->constisnull)
153161
ereport(ERROR,
154162
(errcode(ERRCODE_DATATYPE_MISMATCH),
155163
errmsg("table row type and query-specified row type do not match"),
156-
errdetail("Query provides a value for adropped column at ordinal position %d.",
164+
errdetail("Query provides a value for agenerated column at ordinal position %d.",
157165
attno)));
158166
}
167+
else
168+
{
169+
/* Normal case: demand type match */
170+
if (exprType((Node*)tle->expr)!=attr->atttypid)
171+
ereport(ERROR,
172+
(errcode(ERRCODE_DATATYPE_MISMATCH),
173+
errmsg("table row type and query-specified row type do not match"),
174+
errdetail("Table has type %s at ordinal position %d, but query expects %s.",
175+
format_type_be(attr->atttypid),
176+
attno,
177+
format_type_be(exprType((Node*)tle->expr)))));
178+
}
159179
}
160180
if (attno!=resultDesc->natts)
161181
ereport(ERROR,

‎src/backend/optimizer/prep/preptlist.c

Lines changed: 38 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -44,6 +44,7 @@
4444
#include"optimizer/tlist.h"
4545
#include"parser/parse_coerce.h"
4646
#include"parser/parsetree.h"
47+
#include"utils/lsyscache.h"
4748
#include"utils/rel.h"
4849

4950
staticList*expand_insert_targetlist(PlannerInfo*root,List*tlist,
@@ -334,9 +335,8 @@ expand_insert_targetlist(PlannerInfo *root, List *tlist, Relation rel)
334335
*
335336
* INSERTs should insert NULL in this case. (We assume the
336337
* rewriter would have inserted any available non-NULL default
337-
* value.) Also, if the column isn't dropped, apply any domain
338-
* constraints that might exist --- this is to catch domain NOT
339-
* NULL.
338+
* value.) Also, normally we must apply any domain constraints
339+
* that might exist --- this is to catch domain NOT NULL.
340340
*
341341
* When generating a NULL constant for a dropped column, we label
342342
* it INT4 (any other guaranteed-to-exist datatype would do as
@@ -346,21 +346,17 @@ expand_insert_targetlist(PlannerInfo *root, List *tlist, Relation rel)
346346
* representation is datatype-independent. This could perhaps
347347
* confuse code comparing the finished plan to the target
348348
* relation, however.
349+
*
350+
* Another exception is that if the column is generated, the value
351+
* we produce here will be ignored, and we don't want to risk
352+
* throwing an error. So in that case we *don't* want to apply
353+
* domain constraints, so we must produce a NULL of the base type.
354+
* Again, code comparing the finished plan to the target relation
355+
* must account for this.
349356
*/
350357
Node*new_expr;
351358

352-
if (!att_tup->attisdropped)
353-
{
354-
new_expr=coerce_null_to_domain(att_tup->atttypid,
355-
att_tup->atttypmod,
356-
att_tup->attcollation,
357-
att_tup->attlen,
358-
att_tup->attbyval);
359-
/* Must run expression preprocessing on any non-const nodes */
360-
if (!IsA(new_expr,Const))
361-
new_expr=eval_const_expressions(root,new_expr);
362-
}
363-
else
359+
if (att_tup->attisdropped)
364360
{
365361
/* Insert NULL for dropped column */
366362
new_expr= (Node*)makeConst(INT4OID,
@@ -371,6 +367,33 @@ expand_insert_targetlist(PlannerInfo *root, List *tlist, Relation rel)
371367
true,/* isnull */
372368
true/* byval */ );
373369
}
370+
elseif (att_tup->attgenerated)
371+
{
372+
/* Generated column, insert a NULL of the base type */
373+
OidbaseTypeId=att_tup->atttypid;
374+
int32baseTypeMod=att_tup->atttypmod;
375+
376+
baseTypeId=getBaseTypeAndTypmod(baseTypeId,&baseTypeMod);
377+
new_expr= (Node*)makeConst(baseTypeId,
378+
baseTypeMod,
379+
att_tup->attcollation,
380+
att_tup->attlen,
381+
(Datum)0,
382+
true,/* isnull */
383+
att_tup->attbyval);
384+
}
385+
else
386+
{
387+
/* Normal column, insert a NULL of the column datatype */
388+
new_expr=coerce_null_to_domain(att_tup->atttypid,
389+
att_tup->atttypmod,
390+
att_tup->attcollation,
391+
att_tup->attlen,
392+
att_tup->attbyval);
393+
/* Must run expression preprocessing on any non-const nodes */
394+
if (!IsA(new_expr,Const))
395+
new_expr=eval_const_expressions(root,new_expr);
396+
}
374397

375398
new_tle=makeTargetEntry((Expr*)new_expr,
376399
attrno,

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

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -685,6 +685,11 @@ CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a *
685685
INSERT INTO gtest24 (a) VALUES (4); -- ok
686686
INSERT INTO gtest24 (a) VALUES (6); -- error
687687
ERROR: value for domain gtestdomain1 violates check constraint "gtestdomain1_check"
688+
CREATE DOMAIN gtestdomainnn AS int CHECK (VALUE IS NOT NULL);
689+
CREATE TABLE gtest24nn (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) STORED);
690+
INSERT INTO gtest24nn (a) VALUES (4); -- ok
691+
INSERT INTO gtest24nn (a) VALUES (NULL); -- error
692+
ERROR: value for domain gtestdomainnn violates check constraint "gtestdomainnn_check"
688693
-- typed tables (currently not supported)
689694
CREATE TYPE gtest_type AS (f1 integer, f2 text, f3 bigint);
690695
CREATE TABLE gtest28 OF gtest_type (f1 WITH OPTIONS GENERATED ALWAYS AS (f2 *2) STORED);

‎src/test/regress/sql/generated.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -361,6 +361,11 @@ CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a *
361361
INSERT INTO gtest24 (a)VALUES (4);-- ok
362362
INSERT INTO gtest24 (a)VALUES (6);-- error
363363

364+
CREATEDOMAINgtestdomainnnASintCHECK (VALUEIS NOT NULL);
365+
CREATETABLEgtest24nn (aint, b gtestdomainnn GENERATED ALWAYSAS (a*2) STORED);
366+
INSERT INTO gtest24nn (a)VALUES (4);-- ok
367+
INSERT INTO gtest24nn (a)VALUES (NULL);-- error
368+
364369
-- typed tables (currently not supported)
365370
CREATETYPEgtest_typeAS (f1integer, f2text, f3bigint);
366371
CREATETABLEgtest28 OF gtest_type (f1 WITH OPTIONS GENERATED ALWAYSAS (f2*2) STORED);

0 commit comments

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