Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitf011acd

Browse files
committed
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) correspondingpg_attrdef default expression entry was not populated. So OIDNewHeapcannot be used to call expand_generated_columns_in_expr orbuild_generation_expression. Therefore in ATRewriteTable, we can onlyuse the existing relation to expand the generated expression.Author: jian he <jian.universality@gmail.com>Reviewed-by: Srinath Reddy <srinath2133@gmail.com>Discussion:https://www.postgresql.org/message-id/flat/CACJufxEJ%3DFoajabWXjszo_yrQeKSxdZ87KJqBW373rSbajKGAA%40mail.gmail.com
1 parent716a051 commitf011acd

File tree

5 files changed

+15
-1
lines changed

5 files changed

+15
-1
lines changed

‎src/backend/commands/tablecmds.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6137,7 +6137,7 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
61376137
{
61386138
case CONSTR_CHECK:
61396139
needscan = true;
6140-
con->qualstate = ExecPrepareExpr((Expr *) expand_generated_columns_in_expr(con->qual,newrel ? newrel :oldrel, 1), estate);
6140+
con->qualstate = ExecPrepareExpr((Expr *) expand_generated_columns_in_expr(con->qual, oldrel, 1), estate);
61416141
break;
61426142
case CONSTR_FOREIGN:
61436143
/* Nothing to do here */

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

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -656,6 +656,10 @@ INSERT INTO gtest20a (a) VALUES (10);
656656
INSERT INTO gtest20a (a) VALUES (30);
657657
ALTER TABLE gtest20a ADD CHECK (b < 50); -- fails on existing row
658658
ERROR: check constraint "gtest20a_b_check" of relation "gtest20a" is violated by some row
659+
-- table rewrite cases
660+
ALTER TABLE gtest20a ADD COLUMN c float8 DEFAULT random() CHECK (b < 50); -- fails on existing row
661+
ERROR: check constraint "gtest20a_b_check" of relation "gtest20a" is violated by some row
662+
ALTER TABLE gtest20a ADD COLUMN c float8 DEFAULT random() CHECK (b < 61); -- ok
659663
CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
660664
INSERT INTO gtest20b (a) VALUES (10);
661665
INSERT INTO gtest20b (a) VALUES (30);

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

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -647,6 +647,10 @@ INSERT INTO gtest20a (a) VALUES (10);
647647
INSERT INTO gtest20a (a) VALUES (30);
648648
ALTER TABLE gtest20a ADD CHECK (b < 50); -- fails on existing row
649649
ERROR: check constraint "gtest20a_b_check" of relation "gtest20a" is violated by some row
650+
-- table rewrite cases
651+
ALTER TABLE gtest20a ADD COLUMN c float8 DEFAULT random() CHECK (b < 50); -- fails on existing row
652+
ERROR: check constraint "gtest20a_b_check" of relation "gtest20a" is violated by some row
653+
ALTER TABLE gtest20a ADD COLUMN c float8 DEFAULT random() CHECK (b < 61); -- ok
650654
CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
651655
INSERT INTO gtest20b (a) VALUES (10);
652656
INSERT INTO gtest20b (a) VALUES (30);

‎src/test/regress/sql/generated_stored.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -319,6 +319,9 @@ CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STOR
319319
INSERT INTO gtest20a (a)VALUES (10);
320320
INSERT INTO gtest20a (a)VALUES (30);
321321
ALTERTABLE gtest20a ADDCHECK (b<50);-- fails on existing row
322+
-- table rewrite cases
323+
ALTERTABLE gtest20a ADD COLUMN c float8 DEFAULT random()CHECK (b<50);-- fails on existing row
324+
ALTERTABLE gtest20a ADD COLUMN c float8 DEFAULT random()CHECK (b<61);-- ok
322325

323326
CREATETABLEgtest20b (aintPRIMARY KEY, bint GENERATED ALWAYSAS (a*2) STORED);
324327
INSERT INTO gtest20b (a)VALUES (10);

‎src/test/regress/sql/generated_virtual.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -319,6 +319,9 @@ CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRT
319319
INSERT INTO gtest20a (a)VALUES (10);
320320
INSERT INTO gtest20a (a)VALUES (30);
321321
ALTERTABLE gtest20a ADDCHECK (b<50);-- fails on existing row
322+
-- table rewrite cases
323+
ALTERTABLE gtest20a ADD COLUMN c float8 DEFAULT random()CHECK (b<50);-- fails on existing row
324+
ALTERTABLE gtest20a ADD COLUMN c float8 DEFAULT random()CHECK (b<61);-- ok
322325

323326
CREATETABLEgtest20b (aintPRIMARY KEY, bint GENERATED ALWAYSAS (a*2) VIRTUAL);
324327
INSERT INTO gtest20b (a)VALUES (10);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp