hi.
While trying to make the virtual generated column be part of the partition key,
I found this bug.
it also influences the stored generated column, i added a test
on generated_stored.sql.
CREATE TABLE gtest_part_key (
f1 date NOT NULL, f2 bigint,
f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL)
PARTITION BY RANGE (f3);
ERROR: cannot use generated column in partition key
LINE 4: PARTITION BY RANGE (f3);
^
DETAIL: Column "f3" is a generated column.
the following is essentially the same as above, it should also fail.
CREATE TABLE gtest_part_key (
f1 date NOT NULL, f2 bigint,
f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL)
PARTITION BY RANGE ((f3));
From 10f8c6a1c59a31c2ba6d77d69fdc740e094d9cd3 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 21 Apr 2025 10:28:26 +0800
Subject: [PATCH v1 1/1] virtual generated column can be partition key
CREATE TABLE gtest_part_key (
f1 date NOT NULL, f2 bigint,
f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL)
PARTITION BY RANGE (f3);
ERROR: cannot use generated column in partition key
LINE 4: PARTITION BY RANGE (f3);
^
DETAIL: Column "f3" is a generated column.
the following is essentially the same as above, it should also fail.
CREATE TABLE gtest_part_key (
f1 date NOT NULL, f2 bigint,
f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL)
PARTITION BY RANGE ((f3));
---
src/backend/commands/tablecmds.c | 12 +++++++++++-
src/test/regress/expected/generated_stored.out | 5 +++++
src/test/regress/expected/generated_virtual.out | 5 +++++
src/test/regress/sql/generated_stored.sql | 1 +
src/test/regress/sql/generated_virtual.sql | 1 +
5 files changed, 23 insertions(+), 1 deletion(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 265b1c397fb..4b96cd73ba4 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -19794,11 +19794,21 @@ ComputePartitionAttrs(ParseState *pstate, Relation rel, List *partParams, AttrNu
if (IsA(expr, Var) &&
((Var *) expr)->varattno > 0)
{
+ Var *var = (Var *) expr;
+
+ if (TupleDescAttr(RelationGetDescr(rel), var->varattno)->attgenerated)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("cannot use generated column in partition key"),
+ errdetail("Column \"%s\" is a generated column.",
+ get_attname(RelationGetRelid(rel), var->varattno, false)),
+ parser_errposition(pstate, pelem->location));
+
/*
* User wrote "(column)" or "(column COLLATE something)".
* Treat it like simple attribute anyway.
*/
- partattrs[attn] = ((Var *) expr)->varattno;
+ partattrs[attn] = var->varattno;
}
else
{
diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out
index 16de30ab191..72f39f2f0c1 100644
--- a/src/test/regress/expected/generated_stored.out
+++ b/src/test/regress/expected/generated_stored.out
@@ -1074,6 +1074,11 @@ ERROR: cannot use generated column in partition key
LINE 1: ...ENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f3);
^
DETAIL: Column "f3" is a generated column.
+CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3));
+ERROR: cannot use generated column in partition key
+LINE 1: ...ERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3));
+ ^
+DETAIL: Column "f3" is a generated column.
CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3 * 3));
ERROR: cannot use generated column in partition key
LINE 1: ...ED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3 * 3));
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index 6300e7c1d96..2a6dc84b8ca 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -1027,6 +1027,11 @@ ERROR: cannot use generated column in partition key
LINE 1: ...NERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE (f3);
^
DETAIL: Column "f3" is a generated column.
+CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE ((f3));
+ERROR: cannot use generated column in partition key
+LINE 1: ...RATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE ((f3));
+ ^
+DETAIL: Column "f3" is a generated column.
CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE ((f3 * 3));
ERROR: cannot use generated column in partition key
LINE 1: ...D ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE ((f3 * 3));
diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql
index 4ec155f2da9..c9427c2d4d8 100644
--- a/src/test/regress/sql/generated_stored.sql
+++ b/src/test/regress/sql/generated_stored.sql
@@ -500,6 +500,7 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
-- generated columns in partition key (not allowed)
CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f3);
+CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3));
CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3 * 3));
-- ALTER TABLE ... ADD COLUMN
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index b4eedeee2fb..652057bd707 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -534,6 +534,7 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
-- generated columns in partition key (not allowed)
CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE (f3);
+CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE ((f3));
CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE ((f3 * 3));
-- ALTER TABLE ... ADD COLUMN
--
2.34.1
