From f011acdd61fc296ec8822ca4edc18e32b6bd2b56 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Tue, 4 Mar 2025 09:18:32 +0100 Subject: [PATCH] Fix ALTER TABLE ADD VIRTUAL GENERATED COLUMN when table rewrite demo: CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL); ALTER TABLE gtest20a ADD COLUMN c float8 DEFAULT RANDOM() CHECK (b < 60); ERROR: no generation expression found for column number 2 of table "pg_temp_17306" In ATRewriteTable, the variable OIDNewHeap (if valid) corresponding pg_attrdef default expression entry was not populated. So OIDNewHeap cannot be used to call expand_generated_columns_in_expr or build_generation_expression. Therefore in ATRewriteTable, we can only use the existing relation to expand the generated expression. Author: jian he Reviewed-by: Srinath Reddy Discussion: https://www.postgresql.org/message-id/flat/CACJufxEJ%3DFoajabWXjszo_yrQeKSxdZ87KJqBW373rSbajKGAA%40mail.gmail.com --- src/backend/commands/tablecmds.c | 2 +- src/test/regress/expected/generated_stored.out | 4 ++++ src/test/regress/expected/generated_virtual.out | 4 ++++ src/test/regress/sql/generated_stored.sql | 3 +++ src/test/regress/sql/generated_virtual.sql | 3 +++ 5 files changed, 15 insertions(+), 1 deletion(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 13156391241..8826ca5c32c 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -6137,7 +6137,7 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap) { case CONSTR_CHECK: needscan = true; - con->qualstate = ExecPrepareExpr((Expr *) expand_generated_columns_in_expr(con->qual, newrel ? newrel : oldrel, 1), estate); + con->qualstate = ExecPrepareExpr((Expr *) expand_generated_columns_in_expr(con->qual, oldrel, 1), estate); break; case CONSTR_FOREIGN: /* Nothing to do here */ diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out index 3ce0dd1831c..8cccd1d7fe9 100644 --- a/src/test/regress/expected/generated_stored.out +++ b/src/test/regress/expected/generated_stored.out @@ -656,6 +656,10 @@ INSERT INTO gtest20a (a) VALUES (10); INSERT INTO gtest20a (a) VALUES (30); ALTER TABLE gtest20a ADD CHECK (b < 50); -- fails on existing row ERROR: check constraint "gtest20a_b_check" of relation "gtest20a" is violated by some row +-- table rewrite cases +ALTER TABLE gtest20a ADD COLUMN c float8 DEFAULT random() CHECK (b < 50); -- fails on existing row +ERROR: check constraint "gtest20a_b_check" of relation "gtest20a" is violated by some row +ALTER TABLE gtest20a ADD COLUMN c float8 DEFAULT random() CHECK (b < 61); -- ok CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED); INSERT INTO gtest20b (a) VALUES (10); INSERT INTO gtest20b (a) VALUES (30); diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out index b339fbcebfa..7ef05f45be7 100644 --- a/src/test/regress/expected/generated_virtual.out +++ b/src/test/regress/expected/generated_virtual.out @@ -647,6 +647,10 @@ INSERT INTO gtest20a (a) VALUES (10); INSERT INTO gtest20a (a) VALUES (30); ALTER TABLE gtest20a ADD CHECK (b < 50); -- fails on existing row ERROR: check constraint "gtest20a_b_check" of relation "gtest20a" is violated by some row +-- table rewrite cases +ALTER TABLE gtest20a ADD COLUMN c float8 DEFAULT random() CHECK (b < 50); -- fails on existing row +ERROR: check constraint "gtest20a_b_check" of relation "gtest20a" is violated by some row +ALTER TABLE gtest20a ADD COLUMN c float8 DEFAULT random() CHECK (b < 61); -- ok CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL); INSERT INTO gtest20b (a) VALUES (10); INSERT INTO gtest20b (a) VALUES (30); diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql index b7749ce355f..50e94e5c673 100644 --- a/src/test/regress/sql/generated_stored.sql +++ b/src/test/regress/sql/generated_stored.sql @@ -319,6 +319,9 @@ CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STOR INSERT INTO gtest20a (a) VALUES (10); INSERT INTO gtest20a (a) VALUES (30); ALTER TABLE gtest20a ADD CHECK (b < 50); -- fails on existing row +-- table rewrite cases +ALTER TABLE gtest20a ADD COLUMN c float8 DEFAULT random() CHECK (b < 50); -- fails on existing row +ALTER TABLE gtest20a ADD COLUMN c float8 DEFAULT random() CHECK (b < 61); -- ok CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED); INSERT INTO gtest20b (a) VALUES (10); diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql index c80630c11a5..dab8c92ef99 100644 --- a/src/test/regress/sql/generated_virtual.sql +++ b/src/test/regress/sql/generated_virtual.sql @@ -319,6 +319,9 @@ CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRT INSERT INTO gtest20a (a) VALUES (10); INSERT INTO gtest20a (a) VALUES (30); ALTER TABLE gtest20a ADD CHECK (b < 50); -- fails on existing row +-- table rewrite cases +ALTER TABLE gtest20a ADD COLUMN c float8 DEFAULT random() CHECK (b < 50); -- fails on existing row +ALTER TABLE gtest20a ADD COLUMN c float8 DEFAULT random() CHECK (b < 61); -- ok CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL); INSERT INTO gtest20b (a) VALUES (10); -- 2.39.5