Skip to content

Commit 643a5e9

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 23ddadf commit 643a5e9

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
@@ -17659,6 +17659,8 @@ ComputePartitionAttrs(ParseState *pstate, Relation rel, List *partParams, AttrNu
1765917659
/* Expression */
1766017660
Node *expr = pelem->expr;
1766117661
char partattname[16];
17662+
Bitmapset *expr_attrs = NULL;
17663+
int i;
1766217664

1766317665
Assert(expr != NULL);
1766417666
atttype = exprType(expr);
@@ -17682,9 +17684,55 @@ ComputePartitionAttrs(ParseState *pstate, Relation rel, List *partParams, AttrNu
1768217684
while (IsA(expr, CollateExpr))
1768317685
expr = (Node *) ((CollateExpr *) expr)->arg;
1768417686

17687+
/*
17688+
* Examine all the columns in the partition key expression. When
17689+
* the whole-row reference is present, examine all the columns of
17690+
* the partitioned table.
17691+
*/
17692+
pull_varattnos(expr, 1, &expr_attrs);
17693+
if (bms_is_member(0 - FirstLowInvalidHeapAttributeNumber, expr_attrs))
17694+
{
17695+
expr_attrs = bms_add_range(expr_attrs,
17696+
1 - FirstLowInvalidHeapAttributeNumber,
17697+
RelationGetNumberOfAttributes(rel) - FirstLowInvalidHeapAttributeNumber);
17698+
expr_attrs = bms_del_member(expr_attrs, 0 - FirstLowInvalidHeapAttributeNumber);
17699+
}
17700+
17701+
i = -1;
17702+
while ((i = bms_next_member(expr_attrs, i)) >= 0)
17703+
{
17704+
AttrNumber attno = i + FirstLowInvalidHeapAttributeNumber;
17705+
17706+
Assert(attno != 0);
17707+
17708+
/*
17709+
* Cannot allow system column references, since that would
17710+
* make partition routing impossible: their values won't be
17711+
* known yet when we need to do that.
17712+
*/
17713+
if (attno < 0)
17714+
ereport(ERROR,
17715+
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
17716+
errmsg("partition key expressions cannot contain system column references")));
17717+
17718+
/*
17719+
* Generated columns cannot work: They are computed after
17720+
* BEFORE triggers, but partition routing is done before all
17721+
* triggers.
17722+
*/
17723+
if (TupleDescAttr(RelationGetDescr(rel), attno - 1)->attgenerated)
17724+
ereport(ERROR,
17725+
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
17726+
errmsg("cannot use generated column in partition key"),
17727+
errdetail("Column \"%s\" is a generated column.",
17728+
get_attname(RelationGetRelid(rel), attno, false)),
17729+
parser_errposition(pstate, pelem->location)));
17730+
}
17731+
1768517732
if (IsA(expr, Var) &&
1768617733
((Var *) expr)->varattno > 0)
1768717734
{
17735+
1768817736
/*
1768917737
* User wrote "(column)" or "(column COLLATE something)".
1769017738
* Treat it like simple attribute anyway.
@@ -17693,9 +17741,6 @@ ComputePartitionAttrs(ParseState *pstate, Relation rel, List *partParams, AttrNu
1769317741
}
1769417742
else
1769517743
{
17696-
Bitmapset *expr_attrs = NULL;
17697-
int i;
17698-
1769917744
partattrs[attn] = 0; /* marks the column as expression */
1770017745
*partexprs = lappend(*partexprs, expr);
1770117746

@@ -17705,41 +17750,6 @@ ComputePartitionAttrs(ParseState *pstate, Relation rel, List *partParams, AttrNu
1770517750
* on the EXPR_KIND_ for partition expressions.
1770617751
*/
1770717752

17708-
/*
17709-
* Cannot allow system column references, since that would
17710-
* make partition routing impossible: their values won't be
17711-
* known yet when we need to do that.
17712-
*/
17713-
pull_varattnos(expr, 1, &expr_attrs);
17714-
for (i = FirstLowInvalidHeapAttributeNumber; i < 0; i++)
17715-
{
17716-
if (bms_is_member(i - FirstLowInvalidHeapAttributeNumber,
17717-
expr_attrs))
17718-
ereport(ERROR,
17719-
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
17720-
errmsg("partition key expressions cannot contain system column references")));
17721-
}
17722-
17723-
/*
17724-
* Generated columns cannot work: They are computed after
17725-
* BEFORE triggers, but partition routing is done before all
17726-
* triggers.
17727-
*/
17728-
i = -1;
17729-
while ((i = bms_next_member(expr_attrs, i)) >= 0)
17730-
{
17731-
AttrNumber attno = i + FirstLowInvalidHeapAttributeNumber;
17732-
17733-
if (attno > 0 &&
17734-
TupleDescAttr(RelationGetDescr(rel), attno - 1)->attgenerated)
17735-
ereport(ERROR,
17736-
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
17737-
errmsg("cannot use generated column in partition key"),
17738-
errdetail("Column \"%s\" is a generated column.",
17739-
get_attname(RelationGetRelid(rel), attno, false)),
17740-
parser_errposition(pstate, pelem->location)));
17741-
}
17742-
1774317753
/*
1774417754
* Preprocess the expression before checking for mutability.
1774517755
* 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
@@ -768,11 +768,26 @@ ERROR: cannot use generated column in partition key
768768
LINE 1: ...ENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f3);
769769
^
770770
DETAIL: Column "f3" is a generated column.
771+
CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3));
772+
ERROR: cannot use generated column in partition key
773+
LINE 1: ...ERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3));
774+
^
775+
DETAIL: Column "f3" is a generated column.
771776
CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3 * 3));
772777
ERROR: cannot use generated column in partition key
773778
LINE 1: ...ED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3 * 3));
774779
^
775780
DETAIL: Column "f3" is a generated column.
781+
CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((gtest_parent));
782+
ERROR: cannot use generated column in partition key
783+
LINE 1: ...ED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((gtest_par...
784+
^
785+
DETAIL: Column "f3" is a generated column.
786+
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));
787+
ERROR: cannot use generated column in partition key
788+
LINE 1: ...ED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((gtest_par...
789+
^
790+
DETAIL: Column "f3" is a generated column.
776791
-- ALTER TABLE ... ADD COLUMN
777792
CREATE TABLE gtest25 (a int PRIMARY KEY);
778793
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
@@ -409,7 +409,10 @@ DROP TABLE gtest_parent;
409409

410410
-- generated columns in partition key (not allowed)
411411
CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f3);
412+
CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3));
412413
CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3 * 3));
414+
CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((gtest_parent));
415+
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));
413416

414417
-- ALTER TABLE ... ADD COLUMN
415418
CREATE TABLE gtest25 (a int PRIMARY KEY);

0 commit comments

Comments
 (0)