Hi,
On 06/07/25 23:24, jian he wrote:
hi.
rebased only.
+--test no table rewrite happen
+ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 4), ADD
COLUMN C int default 11;
+SELECT pa.attnum,pa.attname,attmissingval
+FROM pg_attribute pa
+JOIN pg_attrdef patt ON pa.attrelid = patt.adrelid AND pa.attnum =
patt.adnum
+WHERE pa.attrelid = 'gtest20'::regclass
Not sure if I understand the goal of this query. Is to check if the
table was not rewritten after an update on the virtual generated
column? If that's the case, I've tested this query by updating the
expression from a STORED generated column and it return the same
results, before and after the alter table, so I'm not sure if it's the
best way to test this, or I'm missing something?
Perhaps we could use pg_relation_filenode() and use \gset to store the
value on a variable before the ALTER TABLE execution and check if the
value is the same after the ALTER TABLE SET EXPRESSION is executed.
Please see the attached diff for reference.
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
diff --git a/src/test/regress/expected/generated_virtual.out
b/src/test/regress/expected/generated_virtual.out
index 24fa3ca8cc7..a04e27230c3 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -643,17 +643,13 @@ ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a *
100); -- violates con
ERROR: check constraint "gtest20_b_check" of relation "gtest20" is violated
by some row
ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3); -- ok
--test no table rewrite happen
+SELECT pg_relation_filenode('gtest20') AS gtest20_filenode \gset
ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 4), ADD COLUMN C int
default 11;
-SELECT pa.attnum,pa.attname,attmissingval
-FROM pg_attribute pa
-JOIN pg_attrdef patt ON pa.attrelid = patt.adrelid AND pa.attnum =
patt.adnum
-WHERE pa.attrelid = 'gtest20'::regclass
-ORDER BY pa.attnum;
- attnum | attname | attmissingval
---------+---------+---------------
- 2 | b |
- 3 | c | {11}
-(2 rows)
+SELECT pg_relation_filenode('gtest20') = :gtest20_filenode AS is_same_file;
+ is_same_file
+--------------
+ t
+(1 row)
CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2)
VIRTUAL);
INSERT INTO gtest20a (a) VALUES (10);
diff --git a/src/test/regress/sql/generated_virtual.sql
b/src/test/regress/sql/generated_virtual.sql
index 10e0e65c590..765006af23c 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -320,12 +320,9 @@ INSERT INTO gtest20 (a) VALUES (30); -- violates
constraint
ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 100); -- violates
constraint
ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3); -- ok
--test no table rewrite happen
+SELECT pg_relation_filenode('gtest20') AS gtest20_filenode \gset
ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 4), ADD COLUMN C int
default 11;
-SELECT pa.attnum,pa.attname,attmissingval
-FROM pg_attribute pa
-JOIN pg_attrdef patt ON pa.attrelid = patt.adrelid AND pa.attnum =
patt.adnum
-WHERE pa.attrelid = 'gtest20'::regclass
-ORDER BY pa.attnum;
+SELECT pg_relation_filenode('gtest20') = :gtest20_filenode AS is_same_file;
CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2)
VIRTUAL);
INSERT INTO gtest20a (a) VALUES (10);
