=== Applying patches on top of PostgreSQL commit ID 23792d7381583d04c4bbd0a8507566401ec013d3 === /etc/rc.d/jail: WARNING: Per-jail configuration via jail_* variables is obsolete. Please consider migrating to /etc/jail.conf. Sun Nov 16 07:48:20 UTC 2025 On branch cf/5720 nothing to commit, working tree clean === using 'git am' to apply patch ./v1-0001-virtual-generated-column-as-partition-key.patch === Applying: virtual generated column as partition key Using index info to reconstruct a base tree... M src/backend/commands/tablecmds.c M src/backend/executor/execPartition.c M src/backend/partitioning/partbounds.c M src/backend/utils/cache/relcache.c M src/test/regress/expected/generated_stored.out M src/test/regress/expected/generated_virtual.out M src/test/regress/sql/generated_stored.sql M src/test/regress/sql/generated_virtual.sql Falling back to patching base and 3-way merge... Auto-merging src/test/regress/sql/generated_virtual.sql CONFLICT (content): Merge conflict in src/test/regress/sql/generated_virtual.sql Auto-merging src/test/regress/sql/generated_stored.sql Auto-merging src/test/regress/expected/generated_virtual.out CONFLICT (content): Merge conflict in src/test/regress/expected/generated_virtual.out Auto-merging src/test/regress/expected/generated_stored.out CONFLICT (content): Merge conflict in src/test/regress/expected/generated_stored.out Auto-merging src/backend/utils/cache/relcache.c Auto-merging src/backend/partitioning/partbounds.c Auto-merging src/backend/executor/execPartition.c Auto-merging src/backend/commands/tablecmds.c CONFLICT (content): Merge conflict in src/backend/commands/tablecmds.c error: Failed to merge in the changes. hint: Use 'git am --show-current-patch=diff' to see the failed patch Patch failed at 0001 virtual generated column as partition key When you have resolved this problem, run "git am --continue". If you prefer to skip this patch, run "git am --skip" instead. To restore the original branch and stop patching, run "git am --abort". Unstaged changes after reset: M src/backend/commands/tablecmds.c M src/backend/executor/execPartition.c M src/backend/partitioning/partbounds.c M src/backend/utils/cache/partcache.c M src/backend/utils/cache/relcache.c M src/include/utils/relcache.h M src/test/regress/expected/generated_stored.out M src/test/regress/expected/generated_virtual.out M src/test/regress/sql/generated_virtual.sql === using patch(1) to apply patch ./v1-0001-virtual-generated-column-as-partition-key.patch === patching file src/backend/commands/tablecmds.c Hunk #1 succeeded at 708 (offset 1 line). Hunk #2 succeeded at 8635 (offset 27 lines). Hunk #3 succeeded at 19813 (offset 67 lines). Hunk #4 FAILED at 19914. Hunk #5 succeeded at 20579 (offset 75 lines). 1 out of 5 hunks FAILED -- saving rejects to file src/backend/commands/tablecmds.c.rej patching file src/backend/executor/execPartition.c Hunk #1 succeeded at 1325 (offset 4 lines). patching file src/backend/partitioning/partbounds.c Hunk #2 succeeded at 4026 (offset 1 line). Hunk #3 succeeded at 4083 (offset 1 line). Hunk #4 succeeded at 4355 (offset 1 line). Hunk #5 succeeded at 4378 (offset 1 line). Hunk #6 succeeded at 4410 (offset 1 line). Hunk #7 succeeded at 4490 (offset 1 line). Hunk #8 succeeded at 4610 (offset 1 line). Hunk #9 succeeded at 4632 (offset 1 line). Hunk #10 succeeded at 4640 (offset 1 line). Hunk #11 succeeded at 4677 (offset 1 line). Hunk #12 succeeded at 4688 (offset 1 line). patching file src/backend/utils/cache/partcache.c patching file src/backend/utils/cache/relcache.c Hunk #1 succeeded at 6033 (offset 3 lines). patching file src/include/utils/relcache.h patching file src/test/regress/expected/generated_stored.out Hunk #1 FAILED at 1070. 1 out of 1 hunk FAILED -- saving rejects to file src/test/regress/expected/generated_stored.out.rej patching file src/test/regress/expected/generated_virtual.out Hunk #1 FAILED at 1021. 1 out of 1 hunk FAILED -- saving rejects to file src/test/regress/expected/generated_virtual.out.rej patching file src/test/regress/sql/generated_stored.sql Hunk #1 FAILED at 500. 1 out of 1 hunk FAILED -- saving rejects to file src/test/regress/sql/generated_stored.sql.rej patching file src/test/regress/sql/generated_virtual.sql Hunk #1 FAILED at 532. 1 out of 1 hunk FAILED -- saving rejects to file src/test/regress/sql/generated_virtual.sql.rej Unstaged changes after reset: M src/backend/commands/tablecmds.c M src/backend/executor/execPartition.c M src/backend/partitioning/partbounds.c M src/backend/utils/cache/partcache.c M src/backend/utils/cache/relcache.c M src/include/utils/relcache.h Removing src/backend/commands/tablecmds.c.rej Removing src/test/regress/expected/generated_stored.out.rej Removing src/test/regress/expected/generated_virtual.out.rej Removing src/test/regress/sql/generated_stored.sql.rej Removing src/test/regress/sql/generated_virtual.sql.rej === using 'git apply' to apply patch ./v1-0001-virtual-generated-column-as-partition-key.patch === Applied patch to 'src/backend/commands/tablecmds.c' with conflicts. Applied patch to 'src/backend/executor/execPartition.c' cleanly. Applied patch to 'src/backend/partitioning/partbounds.c' cleanly. Applied patch to 'src/backend/utils/cache/partcache.c' cleanly. Applied patch to 'src/backend/utils/cache/relcache.c' cleanly. Applied patch to 'src/include/utils/relcache.h' cleanly. Applied patch to 'src/test/regress/expected/generated_stored.out' with conflicts. Applied patch to 'src/test/regress/expected/generated_virtual.out' with conflicts. Applied patch to 'src/test/regress/sql/generated_stored.sql' cleanly. Applied patch to 'src/test/regress/sql/generated_virtual.sql' with conflicts. U src/backend/commands/tablecmds.c U src/test/regress/expected/generated_stored.out U src/test/regress/expected/generated_virtual.out U src/test/regress/sql/generated_virtual.sql diff --cc src/backend/commands/tablecmds.c index 23ebaa3f230,9d05d83b5e3..00000000000 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@@ -19832,13 -19941,9 +20008,17 @@@ ComputePartitionAttrs(ParseState *pstat } else { ++<<<<<<< ours + /* Expression */ + Node *expr = pelem->expr; + char partattname[16]; + Bitmapset *expr_attrs = NULL; + int i; ++======= + Node *expr; ++>>>>>>> theirs - Assert(expr != NULL); + expr = pelem->expr; atttype = exprType(expr); attcollation = exprCollation(expr); @@@ -19860,110 -19954,36 +20029,104 @@@ while (IsA(expr, CollateExpr)) expr = (Node *) ((CollateExpr *) expr)->arg; + /* + * Examine all the columns in the partition key expression. When + * the whole-row reference is present, examine all the columns of + * the partitioned table. + */ + pull_varattnos(expr, 1, &expr_attrs); + if (bms_is_member(0 - FirstLowInvalidHeapAttributeNumber, expr_attrs)) + { ++<<<<<<< ours + expr_attrs = bms_add_range(expr_attrs, + 1 - FirstLowInvalidHeapAttributeNumber, + RelationGetNumberOfAttributes(rel) - FirstLowInvalidHeapAttributeNumber); + expr_attrs = bms_del_member(expr_attrs, 0 - FirstLowInvalidHeapAttributeNumber); + } + + i = -1; + while ((i = bms_next_member(expr_attrs, i)) >= 0) + { + AttrNumber attno = i + FirstLowInvalidHeapAttributeNumber; + + Assert(attno != 0); + + /* + * Cannot allow system column references, since that would + * make partition routing impossible: their values won't be + * known yet when we need to do that. + */ + if (attno < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("partition key expressions cannot contain system column references"))); + + /* + * Stored generated columns cannot work: They are computed + * after BEFORE triggers, but partition routing is done before + * all triggers. Virtual generated columns could probably + * work, but it would require more work elsewhere (for example + * SET EXPRESSION would need to check whether the column is + * used in partition keys). Seems safer to prohibit for now. + */ + if (TupleDescAttr(RelationGetDescr(rel), attno - 1)->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), attno, false)), + parser_errposition(pstate, pelem->location))); + } + if (IsA(expr, Var) && ((Var *) expr)->varattno > 0) { + + /* + * User wrote "(column)" or "(column COLLATE something)". + * Treat it like simple attribute anyway. + */ + partattrs[attn] = ((Var *) expr)->varattno; + } + else + { + partattrs[attn] = 0; /* marks the column as expression */ + *partexprs = lappend(*partexprs, expr); + + /* + * transformPartitionSpec() should have already rejected + * subqueries, aggregates, window functions, and SRFs, based + * on the EXPR_KIND_ for partition expressions. + */ ++======= + Var *var = (Var *) expr; - /* - * Preprocess the expression before checking for mutability. - * This is essential for the reasons described in - * contain_mutable_functions_after_planning. However, we call - * expression_planner for ourselves rather than using that - * function, because if constant-folding reduces the - * expression to a constant, we'd like to know that so we can - * complain below. - * - * Like contain_mutable_functions_after_planning, assume that - * expression_planner won't scribble on its input, so this - * won't affect the partexprs entry we saved above. - */ - expr = (Node *) expression_planner((Expr *) expr); - - /* - * Partition expressions cannot contain mutable functions, - * because a given row must always map to the same partition - * as long as there is no change in the partition boundary - * structure. - */ - if (contain_mutable_functions(expr)) + if (TupleDescAttr(RelationGetDescr(rel), var->varattno - 1)->attgenerated == ATTRIBUTE_GENERATED_STORED) ereport(ERROR, - (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), - errmsg("functions in partition key expression must be marked IMMUTABLE"))); + errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("cannot use stored generated column in partition key"), + errdetail("Column \"%s\" is a generated column.", + get_attname(RelationGetRelid(rel), var->varattno, false)), + parser_errposition(pstate, pelem->location)); + + if (TupleDescAttr(RelationGetDescr(rel), var->varattno - 1)->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL) + { + Node *defexpr; + + defexpr = build_generation_expression(rel, var->varattno); + + ComputePartitionExprs(pstate, rel, attn, partattrs, partexprs, pelem, defexpr); + } ++>>>>>>> theirs /* - * While it is not exactly *wrong* for a partition expression - * to be a constant, it seems better to reject such keys. - */ - if (IsA(expr, Const)) - ereport(ERROR, - (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), - errmsg("cannot use constant expression as partition key"))); + * User wrote "(column)" or "(column COLLATE something)". + * Treat it like simple attribute anyway. + */ + partattrs[attn] = var->varattno; } + else + ComputePartitionExprs(pstate, rel, attn, partattrs, partexprs, pelem, pelem->expr); } /* diff --cc src/test/regress/expected/generated_stored.out index 8b7a71d8f0c,3a013b4cd49..00000000000 --- a/src/test/regress/expected/generated_stored.out +++ b/src/test/regress/expected/generated_stored.out @@@ -1076,30 -1070,20 +1076,39 @@@ SELECT tableoid::regclass, * FROM gtest -- we leave these tables around for purposes of testing dump/reload/upgrade -- 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); - ERROR: cannot use generated column in partition key + ERROR: cannot use stored generated column in partition key LINE 1: ...ENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f3); ^ + DETAIL: Column "f3" is a stored 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 stored 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)); +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 + ERROR: cannot use stored generated column in partition key LINE 1: ...ED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3 * 3)); ^ ++<<<<<<< ours +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 ((gtest_part_key)); +ERROR: cannot use generated column in partition key +LINE 1: ...ED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((gtest_par... + ^ +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 ((gtest_part_key is not null)); +ERROR: cannot use generated column in partition key +LINE 1: ...ED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((gtest_par... + ^ +DETAIL: Column "f3" is a generated column. ++======= + DETAIL: Column "f3" is a stored generated column. ++>>>>>>> theirs -- ALTER TABLE ... ADD COLUMN CREATE TABLE gtest25 (a int PRIMARY KEY); INSERT INTO gtest25 VALUES (3), (4); diff --cc src/test/regress/expected/generated_virtual.out index dde325e46c6,625f810611f..00000000000 --- a/src/test/regress/expected/generated_virtual.out +++ b/src/test/regress/expected/generated_virtual.out @@@ -1036,32 -1021,112 +1036,141 @@@ SELECT tableoid::regclass, * FROM gtest (3 rows) -- we leave these tables around for purposes of testing dump/reload/upgrade ++<<<<<<< ours +-- 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); +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)); + ^ +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 ((gtest_part_key)); +ERROR: cannot use generated column in partition key +LINE 1: ...D ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE ((gtest_par... + ^ +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 ((gtest_part_key is not null)); +ERROR: cannot use generated column in partition key +LINE 1: ...D ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE ((gtest_par... + ^ +DETAIL: Column "f3" is a generated column. ++======= + -- tests for virtual generated columns in partition key + CREATE TABLE gtest_part_key1 (f1 date, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE ((f3 * 3)); --error + ERROR: partition key expression cannot use virtual generated column + LINE 1: ...D ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE ((f3 * 3)); + ^ + HINT: Only plain virtual generated column reference can be used in partition key + CREATE TABLE gtest_part_key1 (f1 date, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE ((f3)); --ok + ALTER TABLE gtest_part_key1 ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2); --error + ERROR: cannot alter column "f3" because it is part of the partition key of relation "gtest_part_key1" + ALTER TABLE gtest_part_key1 ALTER COLUMN f2 set data type int; --error + ERROR: cannot alter type of a column used by a generated column + DETAIL: Column "f2" is used by generated column "f3". + ALTER TABLE gtest_part_key1 ALTER COLUMN f3 set data type int; --error + ERROR: cannot alter column "f3" because it is part of the partition key of relation "gtest_part_key1" + LINE 1: ALTER TABLE gtest_part_key1 ALTER COLUMN f3 set data type in... + ^ + CREATE TABLE gtest_part_key1_0(f2 bigint, f1 date, f3 bigint GENERATED ALWAYS AS (f2 * 3) VIRTUAL); + ALTER TABLE gtest_part_key1 ATTACH PARTITION gtest_part_key1_0 FOR VALUES FROM (20) TO (30); --error + ERROR: cannot attach table "gtest_part_key1_0" as a partition because it has with different generation expression + ALTER TABLE gtest_part_key1_0 ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2); + ALTER TABLE gtest_part_key1 ATTACH PARTITION gtest_part_key1_0 FOR VALUES FROM (20) TO (30); --now ok + CREATE TABLE gtest_part_key1_1 PARTITION OF gtest_part_key1 FOR VALUES FROM (30) TO (50); + CREATE TABLE gtest_part_key1_2 PARTITION OF gtest_part_key1 FOR VALUES FROM (50) TO (100); + \d+ gtest_part_key1 + Partitioned table "generated_virtual_tests.gtest_part_key1" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description + --------+--------+-----------+----------+------------------------------+---------+--------------+------------- + f1 | date | | | | plain | | + f2 | bigint | | | | plain | | + f3 | bigint | | | generated always as (f2 * 2) | plain | | + Partition key: RANGE (f3) + Partitions: gtest_part_key1_0 FOR VALUES FROM ('20') TO ('30'), + gtest_part_key1_1 FOR VALUES FROM ('30') TO ('50'), + gtest_part_key1_2 FOR VALUES FROM ('50') TO ('100') + + CREATE OR REPLACE FUNCTION gtest_trigger_info() RETURNS trigger + LANGUAGE plpgsql + AS $$ + BEGIN + RAISE INFO 'TG_WHEN: % TG_RELNAME: % trigger name: % tg_op: %', TG_WHEN, TG_relname, TG_NAME, tg_op; + IF tg_op IN ('DELETE') THEN + RAISE INFO 'old = %', OLD; + RETURN OLD; + ELSIF tg_op IN ('INSERT') THEN + RAISE INFO 'new = %', NEW; + RETURN NEW; + ELSIF tg_op IN ('UPDATE') THEN + RAISE INFO 'old = %d; new = %', OLD, NEW; + RETURN NEW; + ELSE + RETURN NEW; + END IF; + END + $$; + CREATE TRIGGER gkey1_0 BEFORE INSERT OR UPDATE ON gtest_part_key1_0 + FOR EACH ROW + EXECUTE PROCEDURE gtest_trigger_info(); + CREATE TRIGGER gkey1_1 BEFORE INSERT OR UPDATE ON gtest_part_key1_1 + FOR EACH ROW + EXECUTE PROCEDURE gtest_trigger_info(); + CREATE TRIGGER gkey1_2 BEFORE INSERT OR UPDATE ON gtest_part_key1_2 + FOR EACH ROW + EXECUTE PROCEDURE gtest_trigger_info(); + INSERT INTO gtest_part_key1(f2, f3) VALUES (9, default); --error + ERROR: no partition of relation "gtest_part_key1" found for row + DETAIL: Partition key of the failing row contains (f3) = (18). + INSERT INTO gtest_part_key1(f2, f3) VALUES (10, default) returning tableoid::regclass, *; --ok + INFO: TG_WHEN: BEFORE TG_RELNAME: gtest_part_key1_0 trigger name: gkey1_0 tg_op: INSERT + INFO: new = (10,,) + tableoid | f1 | f2 | f3 + -------------------+----+----+---- + gtest_part_key1_0 | | 10 | 20 + (1 row) + + INSERT INTO gtest_part_key1_0(f2, f3) VALUES (12, default) returning tableoid::regclass, *; --ok + INFO: TG_WHEN: BEFORE TG_RELNAME: gtest_part_key1_0 trigger name: gkey1_0 tg_op: INSERT + INFO: new = (12,,) + tableoid | f2 | f1 | f3 + -------------------+----+----+---- + gtest_part_key1_0 | 12 | | 24 + (1 row) + + MERGE INTO gtest_part_key1 + USING (VALUES (10, 100), (12, 25), (14, 30)) AS s(sid, delta) + ON gtest_part_key1.f2 = s.sid + WHEN MATCHED AND f2 = 12 THEN UPDATE SET f2 = 20 + WHEN MATCHED AND f2 = 10 THEN UPDATE SET f2 = 30 + WHEN NOT MATCHED THEN INSERT(f2) VALUES (s.sid) + RETURNING merge_action(), tableoid::regclass, old.f2, old.f3, new.f2, new.f3; + INFO: TG_WHEN: BEFORE TG_RELNAME: gtest_part_key1_0 trigger name: gkey1_0 tg_op: UPDATE + INFO: old = (10,,)d; new = (30,,) + INFO: TG_WHEN: BEFORE TG_RELNAME: gtest_part_key1_2 trigger name: gkey1_2 tg_op: INSERT + INFO: new = (,30,) + INFO: TG_WHEN: BEFORE TG_RELNAME: gtest_part_key1_0 trigger name: gkey1_0 tg_op: UPDATE + INFO: old = (12,,)d; new = (20,,) + INFO: TG_WHEN: BEFORE TG_RELNAME: gtest_part_key1_1 trigger name: gkey1_1 tg_op: INSERT + INFO: new = (,20,) + INFO: TG_WHEN: BEFORE TG_RELNAME: gtest_part_key1_0 trigger name: gkey1_0 tg_op: INSERT + INFO: new = (14,,) + merge_action | tableoid | f2 | f3 | f2 | f3 + --------------+-------------------+----+----+----+---- + UPDATE | gtest_part_key1_2 | 10 | 20 | 30 | 60 + UPDATE | gtest_part_key1_1 | 12 | 24 | 20 | 40 + INSERT | gtest_part_key1_0 | | | 14 | 28 + (3 rows) + ++>>>>>>> theirs -- ALTER TABLE ... ADD COLUMN CREATE TABLE gtest25 (a int PRIMARY KEY); INSERT INTO gtest25 VALUES (3), (4); diff --cc src/test/regress/sql/generated_virtual.sql index 2911439776c,f1d6b1c325f..00000000000 --- a/src/test/regress/sql/generated_virtual.sql +++ b/src/test/regress/sql/generated_virtual.sql @@@ -545,12 -532,67 +545,76 @@@ ALTER TABLE gtest_parent ALTER COLUMN f SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3; -- we leave these tables around for purposes of testing dump/reload/upgrade ++<<<<<<< ours +-- 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)); +CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE ((gtest_part_key)); +CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE ((gtest_part_key is not null)); ++======= + -- tests for virtual generated columns in partition key + CREATE TABLE gtest_part_key1 (f1 date, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE ((f3 * 3)); --error + CREATE TABLE gtest_part_key1 (f1 date, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE ((f3)); --ok + ALTER TABLE gtest_part_key1 ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2); --error + ALTER TABLE gtest_part_key1 ALTER COLUMN f2 set data type int; --error + ALTER TABLE gtest_part_key1 ALTER COLUMN f3 set data type int; --error + + CREATE TABLE gtest_part_key1_0(f2 bigint, f1 date, f3 bigint GENERATED ALWAYS AS (f2 * 3) VIRTUAL); + ALTER TABLE gtest_part_key1 ATTACH PARTITION gtest_part_key1_0 FOR VALUES FROM (20) TO (30); --error + + ALTER TABLE gtest_part_key1_0 ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2); + ALTER TABLE gtest_part_key1 ATTACH PARTITION gtest_part_key1_0 FOR VALUES FROM (20) TO (30); --now ok + + CREATE TABLE gtest_part_key1_1 PARTITION OF gtest_part_key1 FOR VALUES FROM (30) TO (50); + CREATE TABLE gtest_part_key1_2 PARTITION OF gtest_part_key1 FOR VALUES FROM (50) TO (100); + + \d+ gtest_part_key1 + + CREATE OR REPLACE FUNCTION gtest_trigger_info() RETURNS trigger + LANGUAGE plpgsql + AS $$ + BEGIN + RAISE INFO 'TG_WHEN: % TG_RELNAME: % trigger name: % tg_op: %', TG_WHEN, TG_relname, TG_NAME, tg_op; + IF tg_op IN ('DELETE') THEN + RAISE INFO 'old = %', OLD; + RETURN OLD; + ELSIF tg_op IN ('INSERT') THEN + RAISE INFO 'new = %', NEW; + RETURN NEW; + ELSIF tg_op IN ('UPDATE') THEN + RAISE INFO 'old = %d; new = %', OLD, NEW; + RETURN NEW; + ELSE + RETURN NEW; + END IF; + END + $$; + + CREATE TRIGGER gkey1_0 BEFORE INSERT OR UPDATE ON gtest_part_key1_0 + FOR EACH ROW + EXECUTE PROCEDURE gtest_trigger_info(); + + CREATE TRIGGER gkey1_1 BEFORE INSERT OR UPDATE ON gtest_part_key1_1 + FOR EACH ROW + EXECUTE PROCEDURE gtest_trigger_info(); + + CREATE TRIGGER gkey1_2 BEFORE INSERT OR UPDATE ON gtest_part_key1_2 + FOR EACH ROW + EXECUTE PROCEDURE gtest_trigger_info(); + + INSERT INTO gtest_part_key1(f2, f3) VALUES (9, default); --error + INSERT INTO gtest_part_key1(f2, f3) VALUES (10, default) returning tableoid::regclass, *; --ok + INSERT INTO gtest_part_key1_0(f2, f3) VALUES (12, default) returning tableoid::regclass, *; --ok + + MERGE INTO gtest_part_key1 + USING (VALUES (10, 100), (12, 25), (14, 30)) AS s(sid, delta) + ON gtest_part_key1.f2 = s.sid + WHEN MATCHED AND f2 = 12 THEN UPDATE SET f2 = 20 + WHEN MATCHED AND f2 = 10 THEN UPDATE SET f2 = 30 + WHEN NOT MATCHED THEN INSERT(f2) VALUES (s.sid) + RETURNING merge_action(), tableoid::regclass, old.f2, old.f3, new.f2, new.f3; ++>>>>>>> theirs -- ALTER TABLE ... ADD COLUMN CREATE TABLE gtest25 (a int PRIMARY KEY);