Hi,
I noticed that the tests for virtual and stored generated columns
contain the following comment;
-- keep these tests aligned with generated_stored.sql (or
generated_virtual.sql)
However, it seems that some additional tests for virtual generated columns
disrupted that alignment, as they were not added to generated_stored.sql.
I've attached a patch to restore the alignment.
In addition, I moved the test confirming that user-defined types are not allowed
in virtual generated columns to the generated_virtual.sql-specific section,
since this test is not necessary for stored generated columns.
Regards,
Yugo Nagata
--
Yugo Nagata <nag...@sraoss.co.jp>
>From 31fb12af49ed15b044b4d0c57f9a3fe37b1c1a72 Mon Sep 17 00:00:00 2001
From: Yugo Nagata <nag...@sraoss.co.jp>
Date: Fri, 8 Aug 2025 11:34:11 +0900
Subject: [PATCH] Align tests for stored and virtual generated columns
These tests were intended to be aligned with each other, but additional
tests for virtual generated columns disrupted that alignment.
The test confirming that user-defined types are not allowed in virtual
generated columns has also been moved to the generated_virtual.sql-specific
section.
---
.../regress/expected/generated_stored.out | 70 ++++++++++++++++++-
.../regress/expected/generated_virtual.out | 14 ++--
src/test/regress/sql/generated_stored.sql | 51 +++++++++++++-
src/test/regress/sql/generated_virtual.sql | 8 +--
4 files changed, 128 insertions(+), 15 deletions(-)
diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out
index adac2cedfb2..6c70353cd97 100644
--- a/src/test/regress/expected/generated_stored.out
+++ b/src/test/regress/expected/generated_stored.out
@@ -679,14 +679,67 @@ INSERT INTO gtest21a (a) VALUES (1); -- ok
INSERT INTO gtest21a (a) VALUES (0); -- violates constraint
ERROR: null value in column "b" of relation "gtest21a" violates not-null constraint
DETAIL: Failing row contains (0, null).
-CREATE TABLE gtest21b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED);
+-- also check with table constraint syntax
+CREATE TABLE gtest21ax (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED, CONSTRAINT cc NOT NULL b);
+INSERT INTO gtest21ax (a) VALUES (0); -- violates constraint
+ERROR: null value in column "b" of relation "gtest21ax" violates not-null constraint
+DETAIL: Failing row contains (0, null).
+INSERT INTO gtest21ax (a) VALUES (1); --ok
+-- SET EXPRESSION supports not null constraint
+ALTER TABLE gtest21ax ALTER COLUMN b SET EXPRESSION AS (nullif(a, 1)); --error
+ERROR: column "b" of relation "gtest21ax" contains null values
+DROP TABLE gtest21ax;
+CREATE TABLE gtest21ax (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED);
+ALTER TABLE gtest21ax ADD CONSTRAINT cc NOT NULL b;
+INSERT INTO gtest21ax (a) VALUES (0); -- violates constraint
+ERROR: null value in column "b" of relation "gtest21ax" violates not-null constraint
+DETAIL: Failing row contains (0, null).
+DROP TABLE gtest21ax;
+CREATE TABLE gtest21b (a int, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED);
ALTER TABLE gtest21b ALTER COLUMN b SET NOT NULL;
INSERT INTO gtest21b (a) VALUES (1); -- ok
-INSERT INTO gtest21b (a) VALUES (0); -- violates constraint
+INSERT INTO gtest21b (a) VALUES (2), (0); -- violates constraint
ERROR: null value in column "b" of relation "gtest21b" violates not-null constraint
DETAIL: Failing row contains (0, null).
+INSERT INTO gtest21b (a) VALUES (NULL); -- error
+ERROR: null value in column "b" of relation "gtest21b" violates not-null constraint
+DETAIL: Failing row contains (null, null).
ALTER TABLE gtest21b ALTER COLUMN b DROP NOT NULL;
INSERT INTO gtest21b (a) VALUES (0); -- ok now
+-- not-null constraint with partitioned table
+CREATE TABLE gtestnn_parent (
+ f1 int,
+ f2 bigint,
+ f3 bigint GENERATED ALWAYS AS (nullif(f1, 1) + nullif(f2, 10)) STORED NOT NULL
+) PARTITION BY RANGE (f1);
+CREATE TABLE gtestnn_child PARTITION OF gtestnn_parent FOR VALUES FROM (1) TO (5);
+CREATE TABLE gtestnn_childdef PARTITION OF gtestnn_parent default;
+-- check the error messages
+INSERT INTO gtestnn_parent VALUES (2, 2, default), (3, 5, default), (14, 12, default); -- ok
+INSERT INTO gtestnn_parent VALUES (1, 2, default); -- error
+ERROR: null value in column "f3" of relation "gtestnn_child" violates not-null constraint
+DETAIL: Failing row contains (1, 2, null).
+INSERT INTO gtestnn_parent VALUES (2, 10, default); -- error
+ERROR: null value in column "f3" of relation "gtestnn_child" violates not-null constraint
+DETAIL: Failing row contains (2, 10, null).
+ALTER TABLE gtestnn_parent ALTER COLUMN f3 SET EXPRESSION AS (nullif(f1, 2) + nullif(f2, 11)); -- error
+ERROR: column "f3" of relation "gtestnn_child" contains null values
+INSERT INTO gtestnn_parent VALUES (10, 11, default); -- ok
+SELECT * FROM gtestnn_parent ORDER BY f1, f2, f3;
+ f1 | f2 | f3
+----+----+----
+ 2 | 2 | 4
+ 3 | 5 | 8
+ 10 | 11 | 21
+ 14 | 12 | 26
+(4 rows)
+
+-- test ALTER TABLE ADD COLUMN
+ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 14) + nullif(f2, 10)) STORED; -- error
+ERROR: column "c" of relation "gtestnn_childdef" contains null values
+ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 13) + nullif(f2, 5)) STORED; -- error
+ERROR: column "c" of relation "gtestnn_child" contains null values
+ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 4) + nullif(f2, 6)) STORED; -- ok
-- index constraints
CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) STORED UNIQUE);
INSERT INTO gtest22a VALUES (2);
@@ -847,6 +900,10 @@ CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS A
INSERT INTO gtest24r (a) VALUES (4); -- ok
INSERT INTO gtest24r (a) VALUES (6); -- error
ERROR: value for domain gtestdomain1 violates check constraint "gtestdomain1_check"
+CREATE TABLE gtest24at (a int PRIMARY KEY);
+ALTER TABLE gtest24at ADD COLUMN b gtestdomain1 GENERATED ALWAYS AS (a * 2) STORED; -- ok
+CREATE TABLE gtest24ata (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
+ALTER TABLE gtest24ata ALTER COLUMN b TYPE gtestdomain1; -- ok
CREATE DOMAIN gtestdomainnn AS int CHECK (VALUE IS NOT NULL);
CREATE TABLE gtest24nn (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) STORED);
INSERT INTO gtest24nn (a) VALUES (4); -- ok
@@ -1154,6 +1211,15 @@ DETAIL: Column "x" is a generated column.
ALTER TABLE gtest27 ALTER COLUMN x DROP DEFAULT; -- error
ERROR: column "x" of relation "gtest27" is a generated column
HINT: Use ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION instead.
+-- test not-null checking during table rewrite
+INSERT INTO gtest27 (a, b) VALUES (NULL, NULL);
+ALTER TABLE gtest27
+ DROP COLUMN x,
+ ALTER COLUMN a TYPE bigint,
+ ALTER COLUMN b TYPE bigint,
+ ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) STORED NOT NULL; -- error
+ERROR: column "x" of relation "gtest27" contains null values
+DELETE FROM gtest27 WHERE a IS NULL AND b IS NULL;
-- It's possible to alter the column types this way:
ALTER TABLE gtest27
DROP COLUMN x,
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index aca6347babe..f959141d7c1 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -552,7 +552,7 @@ CREATE TYPE double_int as (a int, b int);
CREATE TABLE gtest4 (
a int,
b double_int GENERATED ALWAYS AS ((a * 2, a * 3)) VIRTUAL
-);
+); -- fails, user-defined type
ERROR: virtual generated column "b" cannot have a user-defined type
DETAIL: Virtual generated columns that make use of user-defined types are not yet supported.
--INSERT INTO gtest4 VALUES (1), (6);
@@ -808,12 +808,6 @@ CREATE TABLE gtest24nn (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) VIRTU
ERROR: virtual generated column "b" cannot have a domain type
--INSERT INTO gtest24nn (a) VALUES (4); -- ok
--INSERT INTO gtest24nn (a) VALUES (NULL); -- error
--- using user-defined type not yet supported
-CREATE TABLE gtest24xxx (a gtestdomain1, b gtestdomain1, c int GENERATED ALWAYS AS (greatest(a, b)) VIRTUAL); -- error
-ERROR: generation expression uses user-defined type
-LINE 1: ...main1, b gtestdomain1, c int GENERATED ALWAYS AS (greatest(a...
- ^
-DETAIL: Virtual generated columns that make use of user-defined types are not yet supported.
-- typed tables (currently not supported)
CREATE TYPE gtest_type AS (f1 integer, f2 text, f3 bigint);
CREATE TABLE gtest28 OF gtest_type (f1 WITH OPTIONS GENERATED ALWAYS AS (f2 *2) VIRTUAL);
@@ -1484,6 +1478,12 @@ SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT
--
-- these tests are specific to generated_virtual.sql
--
+-- using user-defined type not yet supported
+CREATE TABLE gtest24xxx (a gtestdomain1, b gtestdomain1, c int GENERATED ALWAYS AS (greatest(a, b)) VIRTUAL); -- error
+ERROR: generation expression uses user-defined type
+LINE 1: ...main1, b gtestdomain1, c int GENERATED ALWAYS AS (greatest(a...
+ ^
+DETAIL: Virtual generated columns that make use of user-defined types are not yet supported.
create table gtest32 (
a int primary key,
b int generated always as (a * 2),
diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql
index f56fde8d4e5..e98aef4d3f9 100644
--- a/src/test/regress/sql/generated_stored.sql
+++ b/src/test/regress/sql/generated_stored.sql
@@ -340,13 +340,47 @@ CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0
INSERT INTO gtest21a (a) VALUES (1); -- ok
INSERT INTO gtest21a (a) VALUES (0); -- violates constraint
-CREATE TABLE gtest21b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED);
+-- also check with table constraint syntax
+CREATE TABLE gtest21ax (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED, CONSTRAINT cc NOT NULL b);
+INSERT INTO gtest21ax (a) VALUES (0); -- violates constraint
+INSERT INTO gtest21ax (a) VALUES (1); --ok
+-- SET EXPRESSION supports not null constraint
+ALTER TABLE gtest21ax ALTER COLUMN b SET EXPRESSION AS (nullif(a, 1)); --error
+DROP TABLE gtest21ax;
+
+CREATE TABLE gtest21ax (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED);
+ALTER TABLE gtest21ax ADD CONSTRAINT cc NOT NULL b;
+INSERT INTO gtest21ax (a) VALUES (0); -- violates constraint
+DROP TABLE gtest21ax;
+
+CREATE TABLE gtest21b (a int, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED);
ALTER TABLE gtest21b ALTER COLUMN b SET NOT NULL;
INSERT INTO gtest21b (a) VALUES (1); -- ok
-INSERT INTO gtest21b (a) VALUES (0); -- violates constraint
+INSERT INTO gtest21b (a) VALUES (2), (0); -- violates constraint
+INSERT INTO gtest21b (a) VALUES (NULL); -- error
ALTER TABLE gtest21b ALTER COLUMN b DROP NOT NULL;
INSERT INTO gtest21b (a) VALUES (0); -- ok now
+-- not-null constraint with partitioned table
+CREATE TABLE gtestnn_parent (
+ f1 int,
+ f2 bigint,
+ f3 bigint GENERATED ALWAYS AS (nullif(f1, 1) + nullif(f2, 10)) STORED NOT NULL
+) PARTITION BY RANGE (f1);
+CREATE TABLE gtestnn_child PARTITION OF gtestnn_parent FOR VALUES FROM (1) TO (5);
+CREATE TABLE gtestnn_childdef PARTITION OF gtestnn_parent default;
+-- check the error messages
+INSERT INTO gtestnn_parent VALUES (2, 2, default), (3, 5, default), (14, 12, default); -- ok
+INSERT INTO gtestnn_parent VALUES (1, 2, default); -- error
+INSERT INTO gtestnn_parent VALUES (2, 10, default); -- error
+ALTER TABLE gtestnn_parent ALTER COLUMN f3 SET EXPRESSION AS (nullif(f1, 2) + nullif(f2, 11)); -- error
+INSERT INTO gtestnn_parent VALUES (10, 11, default); -- ok
+SELECT * FROM gtestnn_parent ORDER BY f1, f2, f3;
+-- test ALTER TABLE ADD COLUMN
+ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 14) + nullif(f2, 10)) STORED; -- error
+ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 13) + nullif(f2, 5)) STORED; -- error
+ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 4) + nullif(f2, 6)) STORED; -- ok
+
-- index constraints
CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) STORED UNIQUE);
INSERT INTO gtest22a VALUES (2);
@@ -419,6 +453,11 @@ CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS A
INSERT INTO gtest24r (a) VALUES (4); -- ok
INSERT INTO gtest24r (a) VALUES (6); -- error
+CREATE TABLE gtest24at (a int PRIMARY KEY);
+ALTER TABLE gtest24at ADD COLUMN b gtestdomain1 GENERATED ALWAYS AS (a * 2) STORED; -- ok
+CREATE TABLE gtest24ata (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
+ALTER TABLE gtest24ata ALTER COLUMN b TYPE gtestdomain1; -- ok
+
CREATE DOMAIN gtestdomainnn AS int CHECK (VALUE IS NOT NULL);
CREATE TABLE gtest24nn (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) STORED);
INSERT INTO gtest24nn (a) VALUES (4); -- ok
@@ -530,6 +569,14 @@ ALTER TABLE gtest27 ALTER COLUMN x TYPE numeric;
SELECT * FROM gtest27;
ALTER TABLE gtest27 ALTER COLUMN x TYPE boolean USING x <> 0; -- error
ALTER TABLE gtest27 ALTER COLUMN x DROP DEFAULT; -- error
+-- test not-null checking during table rewrite
+INSERT INTO gtest27 (a, b) VALUES (NULL, NULL);
+ALTER TABLE gtest27
+ DROP COLUMN x,
+ ALTER COLUMN a TYPE bigint,
+ ALTER COLUMN b TYPE bigint,
+ ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) STORED NOT NULL; -- error
+DELETE FROM gtest27 WHERE a IS NULL AND b IS NULL;
-- It's possible to alter the column types this way:
ALTER TABLE gtest27
DROP COLUMN x,
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index ba19bc4c701..01ed9b69b71 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -252,7 +252,7 @@ CREATE TYPE double_int as (a int, b int);
CREATE TABLE gtest4 (
a int,
b double_int GENERATED ALWAYS AS ((a * 2, a * 3)) VIRTUAL
-);
+); -- fails, user-defined type
--INSERT INTO gtest4 VALUES (1), (6);
--SELECT * FROM gtest4;
@@ -464,9 +464,6 @@ CREATE TABLE gtest24nn (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) VIRTU
--INSERT INTO gtest24nn (a) VALUES (4); -- ok
--INSERT INTO gtest24nn (a) VALUES (NULL); -- error
--- using user-defined type not yet supported
-CREATE TABLE gtest24xxx (a gtestdomain1, b gtestdomain1, c int GENERATED ALWAYS AS (greatest(a, b)) VIRTUAL); -- error
-
-- typed tables (currently not supported)
CREATE TYPE gtest_type AS (f1 integer, f2 text, f3 bigint);
CREATE TABLE gtest28 OF gtest_type (f1 WITH OPTIONS GENERATED ALWAYS AS (f2 *2) VIRTUAL);
@@ -806,6 +803,9 @@ SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT
-- these tests are specific to generated_virtual.sql
--
+-- using user-defined type not yet supported
+CREATE TABLE gtest24xxx (a gtestdomain1, b gtestdomain1, c int GENERATED ALWAYS AS (greatest(a, b)) VIRTUAL); -- error
+
create table gtest32 (
a int primary key,
b int generated always as (a * 2),
--
2.43.0
