public inbox for [email protected]
help / color / mirror / Atom feed[PATCH] Align tests for stored and virtual generated columns
4+ messages / 3 participants
[nested] [flat]
* [PATCH] Align tests for stored and virtual generated columns
@ 2025-08-08 02:34 Yugo Nagata <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Yugo Nagata @ 2025-08-08 02:34 UTC (permalink / raw)
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
--Multipart=_Fri__8_Aug_2025_11_51_42_+0900_CP97CmrUzTBhq9M8--
^ permalink raw reply [nested|flat] 4+ messages in thread
* Align tests for stored and virtual generated columns
@ 2025-08-08 02:51 Yugo Nagata <[email protected]>
2025-09-30 18:01 ` Re: Align tests for stored and virtual generated columns Paul A Jungwirth <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Yugo Nagata @ 2025-08-08 02:51 UTC (permalink / raw)
To: pgsql-hackers
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 <[email protected]>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Align tests for stored and virtual generated columns
2025-08-08 02:51 Align tests for stored and virtual generated columns Yugo Nagata <[email protected]>
@ 2025-09-30 18:01 ` Paul A Jungwirth <[email protected]>
2026-03-27 15:08 ` Re: Align tests for stored and virtual generated columns Peter Eisentraut <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Paul A Jungwirth @ 2025-09-30 18:01 UTC (permalink / raw)
To: Yugo Nagata <[email protected]>; Surya Poondla <[email protected]>; Mutaamba Maasha <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Thu, Aug 7, 2025 at 7:52 PM Yugo Nagata <[email protected]> wrote:
> 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.
Hello,
Thanks for this patch! Mutaama Maasha, Surya Poondla, and I reviewed
it together. Here are our thoughts:
We agree we should try to keep these tests in sync, so if they are
diverging we should bring them back into line.
The patch still applies. Tests pass.
Going through the tests made me curious about trying to break virtual
columns. I couldn't come up with anything, although one scenario that
doesn't seem be tested is changing the collation of a column used by a
generated column. For instance:
```
-- English vs Turkish upper/lower i:
create table t2 ( x text COLLATE "en-x-icu", y text COLLATE "tr-x-icu" );
insert into t2 values ('i', 'i'), ('I', 'I');
select upper(x), ascii(upper(x)), lower(x), ascii(lower(x)), upper(y),
ascii(upper(y)), lower(y), ascii(lower(y)) from t2;
create table t3 (
x text collate "en-x-icu",
lx text collate "en-x-icu" generated always as (lower(x)),
ux text collate "en-x-icu" generated always as (upper(x)),
y text collate "tr-x-icu",
ly text collate "tr-x-icu" generated always as (lower(y)),
uy text collate "tr-x-icu" generated always as (upper(y))
);
insert into t3 (x, y) values ('i', 'i'), ('I', 'I');
alter table t3 add constraint x check (ascii(lx) < 128 and ascii(ux) < 128);
alter table t3 alter column x type text collate "tr-x-icu";
ERROR: cannot alter type of a column used by a generated column
DETAIL: Column "x" is used by generated column "lx".
```
Perhaps we could add a test like that? (We do have a test for changing
the *type* of a column used by a generated column though.)
Is there a way we can make it easier to compare the two test scripts
for differences? Could we write a meta-test that compares them for
differences (in the spirit of `opr_sanity.sql`)? I experimented with
using psql variables to limit `STORED` vs `VIRTUAL` to only the top of
each SQL file. Then I could easily diff the two files and see how
diverged they were. Attached is a patch to do this and the results of
my diff (after applying the author's patch). It seems like there are
still a few trivial discrepancies that we could clean up.
To call out one less-trivial discrepancy:
```
--- sql/generated_stored.sql 2025-09-21 19:52:14.554930323 -0700
+++ sql/generated_virtual.sql 2025-09-21 19:52:21.447016340 -0700
...
-INSERT INTO gtest12 VALUES (3, 30), (4, 40); -- currently not
allowed because of function permissions, should
arguably be allowed
-SELECT a, c FROM gtest12; -- allowed (does not actually invoke the function)
+--INSERT INTO gtest12 VALUES (3, 30), (4, 40); -- allowed (does not
actually invoke the function)
+--SELECT a, c FROM gtest12; -- currently not allowed because of
function permissions, should arguably be allowed
```
Why are the VIRTUAL tests commented out? The explanatory comments
suggest they should have opposite results from the STORED tests, which
makes sense, but shouldn't we be running them?
Similarly we noticed that the test for expansion of virtual generated
columns is not applied to stored columns. Is there a reason why not?
We found a couple places where this patch adds new test tables whose
numbering is out of sequence compared to the rest of the file. For
instance:
> @@ -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
Why add gtest24xxx in between gtest28b and gtest32? Maybe it should be
gtest30 or 31?
Also here:
> +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;
Should gtestnn_parent have a number? It is between gtest21b and
gtest22a. Perhaps gtest21nn_parent? This is a tougher choice since 21
and 22 are taken.
Yours,
--
Paul ~{:-)
[email protected]
Attachments:
[text/x-patch] diff_generated_vs_stored.diff (15.0K, 2-diff_generated_vs_stored.diff)
download | inline diff:
--- sql/generated_stored.sql 2025-09-21 19:52:14.554930323 -0700
+++ sql/generated_virtual.sql 2025-09-21 19:52:21.447016340 -0700
@@ -1,19 +1,19 @@
--- keep these tests aligned with generated_virtual.sql
+-- keep these tests aligned with generated_stored.sql
-- We can use psql variables to keep the diff to a minimum.
-\set Generated 'STORED'
-\set Other 'VIRTUAL'
+\set Generated 'VIRTUAL'
+\set Other 'STORED'
-CREATE SCHEMA generated_stored_tests;
-GRANT USAGE ON SCHEMA generated_stored_tests TO PUBLIC;
-SET search_path = generated_stored_tests;
+CREATE SCHEMA generated_virtual_tests;
+GRANT USAGE ON SCHEMA generated_virtual_tests TO PUBLIC;
+SET search_path = generated_virtual_tests;
CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55) :Generated);
CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
-SELECT table_name, column_name, column_default, is_nullable, is_generated, generation_expression FROM information_schema.columns WHERE table_schema = 'generated_stored_tests' ORDER BY 1, 2;
+SELECT table_name, column_name, column_default, is_nullable, is_generated, generation_expression FROM information_schema.columns WHERE table_schema = 'generated_virtual_tests' ORDER BY 1, 2;
-SELECT table_name, column_name, dependent_column FROM information_schema.column_column_usage WHERE table_schema = 'generated_stored_tests' ORDER BY 1, 2, 3;
+SELECT table_name, column_name, dependent_column FROM information_schema.column_column_usage WHERE table_schema = 'generated_virtual_tests' ORDER BY 1, 2, 3;
\d gtest1
@@ -74,7 +74,7 @@
SELECT a, b, b * 2 AS b2 FROM gtest1 ORDER BY a;
SELECT a, b FROM gtest1 WHERE b = 4 ORDER BY a;
--- test that overflow error happens on write
+-- test that overflow error happens on read
INSERT INTO gtest1 VALUES (2000000000);
SELECT * FROM gtest1;
DELETE FROM gtest1 WHERE a = 2000000000;
@@ -192,7 +192,7 @@
TABLE gtestc;
DROP TABLE gtestp CASCADE;
--- test stored update
+-- test update
CREATE TABLE gtest3 (a int, b int GENERATED ALWAYS AS (a * 3) :Generated);
INSERT INTO gtest3 (a) VALUES (1), (2), (3), (NULL);
SELECT * FROM gtest3 ORDER BY a;
@@ -255,11 +255,11 @@
CREATE TABLE gtest4 (
a int,
b double_int GENERATED ALWAYS AS ((a * 2, a * 3)) :Generated
-);
-INSERT INTO gtest4 VALUES (1), (6);
-SELECT * FROM gtest4;
+); -- fails, user-defined type
+--INSERT INTO gtest4 VALUES (1), (6);
+--SELECT * FROM gtest4;
-DROP TABLE gtest4;
+--DROP TABLE gtest4;
DROP TYPE double_int;
-- using tableoid is allowed
@@ -293,20 +293,21 @@
CREATE FUNCTION gf1(a int) RETURNS int AS $$ SELECT a * 3 $$ IMMUTABLE LANGUAGE SQL;
REVOKE ALL ON FUNCTION gf1(int) FROM PUBLIC;
-CREATE TABLE gtest12 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (gf1(b)) :Generated);
-INSERT INTO gtest12 VALUES (1, 10), (2, 20);
-GRANT SELECT (a, c), INSERT ON gtest12 TO regress_user11;
+CREATE TABLE gtest12 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (gf1(b)) :Generated); -- fails, user-defined function
+--INSERT INTO gtest12 VALUES (1, 10), (2, 20);
+--GRANT SELECT (a, c), INSERT ON gtest12 TO regress_user11;
SET ROLE regress_user11;
SELECT a, b FROM gtest11; -- not allowed
SELECT a, c FROM gtest11; -- allowed
SELECT gf1(10); -- not allowed
-INSERT INTO gtest12 VALUES (3, 30), (4, 40); -- currently not allowed because of function permissions, should arguably be allowed
-SELECT a, c FROM gtest12; -- allowed (does not actually invoke the function)
+--INSERT INTO gtest12 VALUES (3, 30), (4, 40); -- allowed (does not actually invoke the function)
+--SELECT a, c FROM gtest12; -- currently not allowed because of function permissions, should arguably be allowed
RESET ROLE;
-DROP FUNCTION gf1(int); -- fail
-DROP TABLE gtest11, gtest12;
+--DROP FUNCTION gf1(int); -- fail
+DROP TABLE gtest11;
+--DROP TABLE gtest12;
DROP FUNCTION gf1(int);
DROP USER regress_user11;
@@ -315,8 +316,8 @@
INSERT INTO gtest20 (a) VALUES (10); -- ok
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
+ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 100); -- violates constraint (currently not supported)
+ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3); -- ok (currently not supported)
CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
INSERT INTO gtest20a (a) VALUES (10);
@@ -386,85 +387,85 @@
-- index constraints
CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) :Generated UNIQUE);
-INSERT INTO gtest22a VALUES (2);
-INSERT INTO gtest22a VALUES (3);
-INSERT INTO gtest22a VALUES (4);
+--INSERT INTO gtest22a VALUES (2);
+--INSERT INTO gtest22a VALUES (3);
+--INSERT INTO gtest22a VALUES (4);
CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a / 2) :Generated, PRIMARY KEY (a, b));
-INSERT INTO gtest22b VALUES (2);
-INSERT INTO gtest22b VALUES (2);
+--INSERT INTO gtest22b VALUES (2);
+--INSERT INTO gtest22b VALUES (2);
-- indexes
CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) :Generated);
-CREATE INDEX gtest22c_b_idx ON gtest22c (b);
-CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3));
-CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0;
-\d gtest22c
-
-INSERT INTO gtest22c VALUES (1), (2), (3);
-SET enable_seqscan TO off;
-SET enable_bitmapscan TO off;
-EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 4;
-SELECT * FROM gtest22c WHERE b = 4;
-EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6;
-SELECT * FROM gtest22c WHERE b * 3 = 6;
-EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
-SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
-
-ALTER TABLE gtest22c ALTER COLUMN b SET EXPRESSION AS (a * 4);
-ANALYZE gtest22c;
-EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 8;
-SELECT * FROM gtest22c WHERE b = 8;
-EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 12;
-SELECT * FROM gtest22c WHERE b * 3 = 12;
-EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
-SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
-RESET enable_seqscan;
-RESET enable_bitmapscan;
+--CREATE INDEX gtest22c_b_idx ON gtest22c (b);
+--CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3));
+--CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0;
+--\d gtest22c
+
+--INSERT INTO gtest22c VALUES (1), (2), (3);
+--SET enable_seqscan TO off;
+--SET enable_bitmapscan TO off;
+--EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 4;
+--SELECT * FROM gtest22c WHERE b = 4;
+--EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6;
+--SELECT * FROM gtest22c WHERE b * 3 = 6;
+--EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+--SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+
+--ALTER TABLE gtest22c ALTER COLUMN b SET EXPRESSION AS (a * 4);
+--ANALYZE gtest22c;
+--EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 8;
+--SELECT * FROM gtest22c WHERE b = 8;
+--EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 12;
+--SELECT * FROM gtest22c WHERE b * 3 = 12;
+--EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+--SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+--RESET enable_seqscan;
+--RESET enable_bitmapscan;
-- foreign keys
CREATE TABLE gtest23a (x int PRIMARY KEY, y int);
-INSERT INTO gtest23a VALUES (1, 11), (2, 22), (3, 33);
+--INSERT INTO gtest23a VALUES (1, 11), (2, 22), (3, 33);
CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated REFERENCES gtest23a (x) ON UPDATE CASCADE); -- error
CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated REFERENCES gtest23a (x) ON DELETE SET NULL); -- error
CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated REFERENCES gtest23a (x));
-\d gtest23b
+--\d gtest23b
-INSERT INTO gtest23b VALUES (1); -- ok
-INSERT INTO gtest23b VALUES (5); -- error
-ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 5); -- error
-ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 1); -- ok
+--INSERT INTO gtest23b VALUES (1); -- ok
+--INSERT INTO gtest23b VALUES (5); -- error
+--ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 5); -- error
+--ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 1); -- ok
-DROP TABLE gtest23b;
-DROP TABLE gtest23a;
+--DROP TABLE gtest23b;
+--DROP TABLE gtest23a;
CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) :Generated, PRIMARY KEY (y));
-INSERT INTO gtest23p VALUES (1), (2), (3);
+--INSERT INTO gtest23p VALUES (1), (2), (3);
CREATE TABLE gtest23q (a int PRIMARY KEY, b int REFERENCES gtest23p (y));
-INSERT INTO gtest23q VALUES (1, 2); -- ok
-INSERT INTO gtest23q VALUES (2, 5); -- error
+--INSERT INTO gtest23q VALUES (1, 2); -- ok
+--INSERT INTO gtest23q VALUES (2, 5); -- error
-- domains
CREATE DOMAIN gtestdomain1 AS int CHECK (VALUE < 10);
CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a * 2) :Generated);
-INSERT INTO gtest24 (a) VALUES (4); -- ok
-INSERT INTO gtest24 (a) VALUES (6); -- error
+--INSERT INTO gtest24 (a) VALUES (4); -- ok
+--INSERT INTO gtest24 (a) VALUES (6); -- error
CREATE TYPE gtestdomain1range AS range (subtype = gtestdomain1);
CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS AS (gtestdomain1range(a, a + 5)) :Generated);
-INSERT INTO gtest24r (a) VALUES (4); -- ok
-INSERT INTO gtest24r (a) VALUES (6); -- error
+--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) :Generated; -- ok
+ALTER TABLE gtest24at ADD COLUMN b gtestdomain1 GENERATED ALWAYS AS (a * 2) :Generated; -- error
CREATE TABLE gtest24ata (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
-ALTER TABLE gtest24ata ALTER COLUMN b TYPE gtestdomain1; -- ok
+ALTER TABLE gtest24ata ALTER COLUMN b TYPE gtestdomain1; -- error
CREATE DOMAIN gtestdomainnn AS int CHECK (VALUE IS NOT NULL);
CREATE TABLE gtest24nn (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) :Generated);
-INSERT INTO gtest24nn (a) VALUES (4); -- ok
-INSERT INTO gtest24nn (a) VALUES (NULL); -- error
+--INSERT INTO gtest24nn (a) VALUES (4); -- ok
+--INSERT INTO gtest24nn (a) VALUES (NULL); -- error
-- typed tables (currently not supported)
CREATE TYPE gtest_type AS (f1 integer, f2 text, f3 bigint);
@@ -517,7 +518,7 @@
INSERT INTO gtest_parent (f1, f2) VALUES ('2016-08-15', 3);
SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
SELECT tableoid::regclass, * FROM gtest_child ORDER BY 1, 2, 3;
-SELECT tableoid::regclass, * FROM gtest_child2 ORDER BY 1, 2, 3;
+SELECT tableoid::regclass, * FROM gtest_child2 ORDER BY 1, 2, 3; -- uses child's generation expression, not parent's
SELECT tableoid::regclass, * FROM gtest_child3 ORDER BY 1, 2, 3;
UPDATE gtest_parent SET f1 = f1 + 60 WHERE f2 = 1;
SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
@@ -611,15 +612,15 @@
SELECT * FROM gtest29;
\d gtest29
-ALTER TABLE gtest29 ALTER COLUMN b DROP EXPRESSION;
+ALTER TABLE gtest29 ALTER COLUMN b DROP EXPRESSION; -- not supported
INSERT INTO gtest29 (a) VALUES (5);
INSERT INTO gtest29 (a, b) VALUES (6, 66);
SELECT * FROM gtest29;
\d gtest29
-- check that dependencies between columns have also been removed
-ALTER TABLE gtest29 DROP COLUMN a; -- should not drop b
-\d gtest29
+--ALTER TABLE gtest29 DROP COLUMN a; -- should not drop b
+--\d gtest29
-- with inheritance
CREATE TABLE gtest30 (
@@ -646,7 +647,7 @@
CREATE TABLE gtest31_2 (x int, y gtest31_1);
ALTER TABLE gtest31_1 ALTER COLUMN b TYPE varchar; -- fails
--- bug #18970: these cases are unsupported, but make sure they fail cleanly
+-- bug #18970
ALTER TABLE gtest31_2 ADD CONSTRAINT cc CHECK ((y).b IS NOT NULL);
ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello1');
ALTER TABLE gtest31_2 DROP CONSTRAINT cc;
@@ -797,3 +798,91 @@
-- sanity check of system catalog
SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT IN ('', 's', 'v');
+
+
+--
+-- test the expansion of virtual generated columns
+--
+-- 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)) :Generated); -- error
+
+create table gtest32 (
+ a int primary key,
+ b int generated always as (a * 2),
+ c int generated always as (10 + 10),
+ d int generated always as (coalesce(a, 100)),
+ e int
+);
+
+insert into gtest32 values (1), (2);
+analyze gtest32;
+
+-- Ensure that nullingrel bits are propagated into the generation expressions
+explain (costs off)
+select sum(t2.b) over (partition by t2.a),
+ sum(t2.c) over (partition by t2.a),
+ sum(t2.d) over (partition by t2.a)
+from gtest32 as t1 left join gtest32 as t2 on (t1.a = t2.a)
+order by t1.a;
+
+select sum(t2.b) over (partition by t2.a),
+ sum(t2.c) over (partition by t2.a),
+ sum(t2.d) over (partition by t2.a)
+from gtest32 as t1 left join gtest32 as t2 on (t1.a = t2.a)
+order by t1.a;
+
+-- Ensure that outer-join removal functions correctly after the propagation of nullingrel bits
+explain (costs off)
+select t1.a from gtest32 t1 left join gtest32 t2 on t1.a = t2.a
+where coalesce(t2.b, 1) = 2;
+
+select t1.a from gtest32 t1 left join gtest32 t2 on t1.a = t2.a
+where coalesce(t2.b, 1) = 2;
+
+explain (costs off)
+select t1.a from gtest32 t1 left join gtest32 t2 on t1.a = t2.a
+where coalesce(t2.b, 1) = 2 or t1.a is null;
+
+select t1.a from gtest32 t1 left join gtest32 t2 on t1.a = t2.a
+where coalesce(t2.b, 1) = 2 or t1.a is null;
+
+-- Ensure that the generation expressions are wrapped into PHVs if needed
+explain (verbose, costs off)
+select t2.* from gtest32 t1 left join gtest32 t2 on false;
+select t2.* from gtest32 t1 left join gtest32 t2 on false;
+
+explain (verbose, costs off)
+select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
+select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
+
+-- Ensure that the virtual generated columns in ALTER COLUMN TYPE USING expression are expanded
+alter table gtest32 alter column e type bigint using b;
+
+-- Ensure that virtual generated column references within SubLinks that should
+-- be transformed into joins can get expanded
+explain (costs off)
+select 1 from gtest32 t1 where exists
+ (select 1 from gtest32 t2 where t1.a > t2.a and t2.b = 2);
+
+select 1 from gtest32 t1 where exists
+ (select 1 from gtest32 t2 where t1.a > t2.a and t2.b = 2);
+
+drop table gtest32;
+
+-- Ensure that virtual generated columns in constraint expressions are expanded
+create table gtest33 (a int, b int generated always as (a * 2) virtual not null, check (b > 10));
+set constraint_exclusion to on;
+
+-- should get a dummy Result, not a seq scan
+explain (costs off)
+select * from gtest33 where b < 10;
+
+-- should get a dummy Result, not a seq scan
+explain (costs off)
+select * from gtest33 where b is null;
+
+reset constraint_exclusion;
+drop table gtest33;
[application/octet-stream] 0001-Try-to-limit-STORED-vs-VIRTUAL-diff-to-just-the-firs.patch.nocfbot (136.0K, 3-0001-Try-to-limit-STORED-vs-VIRTUAL-diff-to-just-the-firs.patch.nocfbot)
download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Align tests for stored and virtual generated columns
2025-08-08 02:51 Align tests for stored and virtual generated columns Yugo Nagata <[email protected]>
2025-09-30 18:01 ` Re: Align tests for stored and virtual generated columns Paul A Jungwirth <[email protected]>
@ 2026-03-27 15:08 ` Peter Eisentraut <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Peter Eisentraut @ 2026-03-27 15:08 UTC (permalink / raw)
To: Paul A Jungwirth <[email protected]>; Yugo Nagata <[email protected]>; Surya Poondla <[email protected]>; Mutaamba Maasha <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On 30.09.25 20:01, Paul A Jungwirth wrote:
> Going through the tests made me curious about trying to break virtual
> columns. I couldn't come up with anything, although one scenario that
> doesn't seem be tested is changing the collation of a column used by a
> generated column. For instance:
>
> ```
> -- English vs Turkish upper/lower i:
> create table t2 ( x text COLLATE "en-x-icu", y text COLLATE "tr-x-icu" );
> insert into t2 values ('i', 'i'), ('I', 'I');
> select upper(x), ascii(upper(x)), lower(x), ascii(lower(x)), upper(y),
> ascii(upper(y)), lower(y), ascii(lower(y)) from t2;
>
> create table t3 (
> x text collate "en-x-icu",
> lx text collate "en-x-icu" generated always as (lower(x)),
> ux text collate "en-x-icu" generated always as (upper(x)),
> y text collate "tr-x-icu",
> ly text collate "tr-x-icu" generated always as (lower(y)),
> uy text collate "tr-x-icu" generated always as (upper(y))
> );
> insert into t3 (x, y) values ('i', 'i'), ('I', 'I');
> alter table t3 add constraint x check (ascii(lx) < 128 and ascii(ux) < 128);
> alter table t3 alter column x type text collate "tr-x-icu";
> ERROR: cannot alter type of a column used by a generated column
> DETAIL: Column "x" is used by generated column "lx".
> ```
>
> Perhaps we could add a test like that? (We do have a test for changing
> the *type* of a column used by a generated column though.)
The example you show produces the error
ERROR: cannot alter type of a column used by a generated column
which suggests that it is internally the same thing, so it seems another
test wouldn't add any new coverage.
> Is there a way we can make it easier to compare the two test scripts
> for differences? Could we write a meta-test that compares them for
> differences (in the spirit of `opr_sanity.sql`)? I experimented with
> using psql variables to limit `STORED` vs `VIRTUAL` to only the top of
> each SQL file. Then I could easily diff the two files and see how
> diverged they were. Attached is a patch to do this and the results of
> my diff (after applying the author's patch). It seems like there are
> still a few trivial discrepancies that we could clean up.
I thought about something like that initially, too, but then decided
against it because it would make each test individually harder to
understand and manage.
> To call out one less-trivial discrepancy:
>
> ```
> --- sql/generated_stored.sql 2025-09-21 19:52:14.554930323 -0700
> +++ sql/generated_virtual.sql 2025-09-21 19:52:21.447016340 -0700
> ...
> -INSERT INTO gtest12 VALUES (3, 30), (4, 40); -- currently not
> allowed because of function permissions, should
> arguably be allowed
> -SELECT a, c FROM gtest12; -- allowed (does not actually invoke the function)
> +--INSERT INTO gtest12 VALUES (3, 30), (4, 40); -- allowed (does not
> actually invoke the function)
> +--SELECT a, c FROM gtest12; -- currently not allowed because of
> function permissions, should arguably be allowed
> ```
>
> Why are the VIRTUAL tests commented out? The explanatory comments
> suggest they should have opposite results from the STORED tests, which
> makes sense, but shouldn't we be running them?
My recollection is that some of these tests are commented out because
running them would produce some behavior that would affect subsequent
tests (for example, adding or removing rows that they shouldn't), and so
you would have to do some extra work to undo some of that to make this
all work smoothly. (Also, in some cases it would be a waste of time to
run large blocks of tests for unsupported features, so all but the first
few test statements are commented out.)
> Similarly we noticed that the test for expansion of virtual generated
> columns is not applied to stored columns. Is there a reason why not?
I guess this tests something that doesn't really apply to stored
columns. But maybe this could be double checked.
> We found a couple places where this patch adds new test tables whose
> numbering is out of sequence compared to the rest of the file.
Yeah, this whole test numbering turned into a disaster pretty early on.
At this point, I consider these test files to be a monument against
doing that again. In later feature work, I started to name test tables
by random numbers. ;-)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-03-27 15:08 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-08-08 02:34 [PATCH] Align tests for stored and virtual generated columns Yugo Nagata <[email protected]>
2025-08-08 02:51 Align tests for stored and virtual generated columns Yugo Nagata <[email protected]>
2025-09-30 18:01 ` Paul A Jungwirth <[email protected]>
2026-03-27 15:08 ` Peter Eisentraut <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox