Skip to content

Commit 7f239c7

Browse files
peterejianhe-funashutosh-bapat
committed
Tighten check for generated column in partition key expression
A generated column may end up being part of the partition key expression, if it's specified as an expression e.g. "(<generated column name>)" or if the partition key expression contains a whole-row reference, even though we do not allow a generated column to be part of partition key expression. Fix this hole. Co-authored-by: jian he <jian.universality@gmail.com> Co-authored-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> Reviewed-by: Fujii Masao <masao.fujii@oss.nttdata.com> Discussion: https://www.postgresql.org/message-id/flat/CACJufxF%3DWDGthXSAQr9thYUsfx_1_t9E6N8tE3B8EqXcVoVfQw%40mail.gmail.com
1 parent 3c7b479 commit 7f239c7

File tree

3 files changed

+66
-38
lines changed

3 files changed

+66
-38
lines changed

‎src/backend/commands/tablecmds.c‎

Lines changed: 48 additions & 38 deletions
Original file line numberDiff line numberDiff line change
@@ -16548,6 +16548,8 @@ ComputePartitionAttrs(ParseState *pstate, Relation rel, List *partParams, AttrNu
1654816548
/* Expression */
1654916549
Node *expr = pelem->expr;
1655016550
char partattname[16];
16551+
Bitmapset *expr_attrs = NULL;
16552+
int i;
1655116553

1655216554
Assert(expr != NULL);
1655316555
atttype = exprType(expr);
@@ -16571,9 +16573,55 @@ ComputePartitionAttrs(ParseState *pstate, Relation rel, List *partParams, AttrNu
1657116573
while (IsA(expr, CollateExpr))
1657216574
expr = (Node *) ((CollateExpr *) expr)->arg;
1657316575

16576+
/*
16577+
* Examine all the columns in the partition key expression. When
16578+
* the whole-row reference is present, examine all the columns of
16579+
* the partitioned table.
16580+
*/
16581+
pull_varattnos(expr, 1, &expr_attrs);
16582+
if (bms_is_member(0 - FirstLowInvalidHeapAttributeNumber, expr_attrs))
16583+
{
16584+
expr_attrs = bms_add_range(expr_attrs,
16585+
1 - FirstLowInvalidHeapAttributeNumber,
16586+
RelationGetNumberOfAttributes(rel) - FirstLowInvalidHeapAttributeNumber);
16587+
expr_attrs = bms_del_member(expr_attrs, 0 - FirstLowInvalidHeapAttributeNumber);
16588+
}
16589+
16590+
i = -1;
16591+
while ((i = bms_next_member(expr_attrs, i)) >= 0)
16592+
{
16593+
AttrNumber attno = i + FirstLowInvalidHeapAttributeNumber;
16594+
16595+
Assert(attno != 0);
16596+
16597+
/*
16598+
* Cannot allow system column references, since that would
16599+
* make partition routing impossible: their values won't be
16600+
* known yet when we need to do that.
16601+
*/
16602+
if (attno < 0)
16603+
ereport(ERROR,
16604+
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
16605+
errmsg("partition key expressions cannot contain system column references")));
16606+
16607+
/*
16608+
* Generated columns cannot work: They are computed after
16609+
* BEFORE triggers, but partition routing is done before all
16610+
* triggers.
16611+
*/
16612+
if (TupleDescAttr(RelationGetDescr(rel), attno - 1)->attgenerated)
16613+
ereport(ERROR,
16614+
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
16615+
errmsg("cannot use generated column in partition key"),
16616+
errdetail("Column \"%s\" is a generated column.",
16617+
get_attname(RelationGetRelid(rel), attno, false)),
16618+
parser_errposition(pstate, pelem->location)));
16619+
}
16620+
1657416621
if (IsA(expr, Var) &&
1657516622
((Var *) expr)->varattno > 0)
1657616623
{
16624+
1657716625
/*
1657816626
* User wrote "(column)" or "(column COLLATE something)".
1657916627
* Treat it like simple attribute anyway.
@@ -16582,9 +16630,6 @@ ComputePartitionAttrs(ParseState *pstate, Relation rel, List *partParams, AttrNu
1658216630
}
1658316631
else
1658416632
{
16585-
Bitmapset *expr_attrs = NULL;
16586-
int i;
16587-
1658816633
partattrs[attn] = 0; /* marks the column as expression */
1658916634
*partexprs = lappend(*partexprs, expr);
1659016635

@@ -16594,41 +16639,6 @@ ComputePartitionAttrs(ParseState *pstate, Relation rel, List *partParams, AttrNu
1659416639
* on the EXPR_KIND_ for partition expressions.
1659516640
*/
1659616641

16597-
/*
16598-
* Cannot allow system column references, since that would
16599-
* make partition routing impossible: their values won't be
16600-
* known yet when we need to do that.
16601-
*/
16602-
pull_varattnos(expr, 1, &expr_attrs);
16603-
for (i = FirstLowInvalidHeapAttributeNumber; i < 0; i++)
16604-
{
16605-
if (bms_is_member(i - FirstLowInvalidHeapAttributeNumber,
16606-
expr_attrs))
16607-
ereport(ERROR,
16608-
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
16609-
errmsg("partition key expressions cannot contain system column references")));
16610-
}
16611-
16612-
/*
16613-
* Generated columns cannot work: They are computed after
16614-
* BEFORE triggers, but partition routing is done before all
16615-
* triggers.
16616-
*/
16617-
i = -1;
16618-
while ((i = bms_next_member(expr_attrs, i)) >= 0)
16619-
{
16620-
AttrNumber attno = i + FirstLowInvalidHeapAttributeNumber;
16621-
16622-
if (attno > 0 &&
16623-
TupleDescAttr(RelationGetDescr(rel), attno - 1)->attgenerated)
16624-
ereport(ERROR,
16625-
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
16626-
errmsg("cannot use generated column in partition key"),
16627-
errdetail("Column \"%s\" is a generated column.",
16628-
get_attname(RelationGetRelid(rel), attno, false)),
16629-
parser_errposition(pstate, pelem->location)));
16630-
}
16631-
1663216642
/*
1663316643
* Preprocess the expression before checking for mutability.
1663416644
* This is essential for the reasons described in

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

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -693,11 +693,26 @@ ERROR: cannot use generated column in partition key
693693
LINE 1: ...ENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f3);
694694
^
695695
DETAIL: Column "f3" is a generated column.
696+
CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3));
697+
ERROR: cannot use generated column in partition key
698+
LINE 1: ...ERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3));
699+
^
700+
DETAIL: Column "f3" is a generated column.
696701
CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3 * 3));
697702
ERROR: cannot use generated column in partition key
698703
LINE 1: ...ED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3 * 3));
699704
^
700705
DETAIL: Column "f3" is a generated column.
706+
CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((gtest_parent));
707+
ERROR: cannot use generated column in partition key
708+
LINE 1: ...ED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((gtest_par...
709+
^
710+
DETAIL: Column "f3" is a generated column.
711+
CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((gtest_parent is not null));
712+
ERROR: cannot use generated column in partition key
713+
LINE 1: ...ED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((gtest_par...
714+
^
715+
DETAIL: Column "f3" is a generated column.
701716
-- ALTER TABLE ... ADD COLUMN
702717
CREATE TABLE gtest25 (a int PRIMARY KEY);
703718
INSERT INTO gtest25 VALUES (3), (4);

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

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -370,7 +370,10 @@ DROP TABLE gtest_parent;
370370

371371
-- generated columns in partition key (not allowed)
372372
CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f3);
373+
CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3));
373374
CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3 * 3));
375+
CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((gtest_parent));
376+
CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((gtest_parent is not null));
374377

375378
-- ALTER TABLE ... ADD COLUMN
376379
CREATE TABLE gtest25 (a int PRIMARY KEY);

0 commit comments

Comments
 (0)