Hi all, While validating the bootstrap crash fix for the virtual generated column index patch, I reviewed the current regression coverage around this area. During this process, I noticed that the existing regression suite verifies the functional restrictions on virtual generated columns, but there is no dedicated regression test specifically covering index-related restrictions. As this patch touches index creation logic and also exposes a bootstrap-time crash during testing, it may be useful to add explicit regression coverage for these cases. Having a focused test would help ensure that the current restrictions remain stable and that any future changes affecting this area are detected early by the regression framework and may strengthen long-term test coverage. As a small enhancement in this direction, I prepared a regression test that verifies the expected errors for the following scenarios:
1. CREATE INDEX on a virtual generated column 2. Partial index on a virtual generated column 3. PRIMARY KEY on a virtual generated column 4. Expression index referencing a virtual generated column This ensures the current restrictions remain protected by the regression framework and helps detect unintended changes in the future. The attached patch only adds the regression test and expected output. If this finds useful, kindly verify the patch attached herewith and please let me know the thoughts on this. I would be happy to refine or extend the test further. Looking forward to more feedback. Regards, Soumya
From 13893800252ad45b14528028c43bf5a60d644c5f Mon Sep 17 00:00:00 2001 From: Soumya <[email protected]> Date: Wed, 4 Mar 2026 10:31:51 +0530 Subject: [PATCH] Add test for virtual generated column index restrictions Add regression coverage verifying that indexes, partial indexes, primary keys, and expression indexes are rejected when defined on virtual generated columns. Signed-off-by: Soumya <[email protected]> --- .../expected/virtual_generated_index.out | 18 +++++++++++++++++ src/test/regress/parallel_schedule | 11 ++++++++++ .../regress/sql/virtual_generated_index.sql | 20 +++++++++++++++++++ 3 files changed, 49 insertions(+) create mode 100644 src/test/regress/expected/virtual_generated_index.out create mode 100644 src/test/regress/sql/virtual_generated_index.sql diff --git a/src/test/regress/expected/virtual_generated_index.out b/src/test/regress/expected/virtual_generated_index.out new file mode 100644 index 00000000000..9817af37fb6 --- /dev/null +++ b/src/test/regress/expected/virtual_generated_index.out @@ -0,0 +1,18 @@ +-- Test restrictions on virtual generated columns in indexes +CREATE TABLE t1 ( + a int, + b int GENERATED ALWAYS AS (a * 2) VIRTUAL +); +-- Index creation should fail +CREATE INDEX idx_t1_b ON t1 (b); +ERROR: indexes on virtual generated columns are not supported +-- Partial index should fail +CREATE INDEX idx_partial ON t1 (b) WHERE b > 10; +ERROR: indexes on virtual generated columns are not supported +-- Primary key should fail +ALTER TABLE t1 ADD PRIMARY KEY (b); +ERROR: primary keys on virtual generated columns are not supported +-- Expression index should fail +CREATE INDEX idx_expr ON t1 ((b + 1)); +ERROR: indexes on virtual generated columns are not supported +DROP TABLE t1; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 549e9b2d7be..32c072693ca 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -10,6 +10,7 @@ # required setup steps test: test_setup +test: virtual_generated_index # ---------- # The first group of parallel tests @@ -44,11 +45,13 @@ test: copy copyselect copydml copyencoding insert insert_conflict # ---------- test: create_function_c create_misc create_operator create_procedure create_table create_type create_schema test: create_index create_index_spgist create_view index_including index_including_gist +test: virtual_generated_index # ---------- # Another group of parallel tests # ---------- test: create_aggregate create_function_sql create_cast constraints triggers select inherit typed_table vacuum drop_if_exists updatable_views roleattributes create_am hash_func errors infinite_recurse +test: virtual_generated_index # ---------- # sanity_check does a vacuum, affecting the sort order of SELECT * @@ -62,6 +65,7 @@ test: sanity_check # join depends on create_misc # ---------- test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index update delete namespace prepared_xacts +test: virtual_generated_index # ---------- # Another group of parallel tests @@ -77,11 +81,13 @@ test: brin_bloom brin_multi # Another group of parallel tests # ---------- test: create_table_like alter_generic alter_operator misc async dbsize merge misc_functions nls sysviews tsrf tid tidscan tidrangescan collate.utf8 collate.icu.utf8 incremental_sort create_role without_overlaps generated_virtual +test: virtual_generated_index # collate.linux.utf8 and collate.icu.utf8 tests cannot be run in parallel with each other # psql depends on create_am # amutils depends on geometry, create_index_spgist, hash_index, brin test: rules psql psql_crosstab psql_pipeline amutils stats_ext collate.linux.utf8 collate.windows.win1252 +test: virtual_generated_index # ---------- # Run these alone so they don't run out of parallel workers @@ -90,6 +96,7 @@ test: rules psql psql_crosstab psql_pipeline amutils stats_ext collate.linux.utf test: select_parallel test: write_parallel test: vacuum_parallel +test: virtual_generated_index # Run this alone, because concurrent DROP TABLE would make non-superuser # "ANALYZE;" fail with "relation with OID $n does not exist". @@ -103,6 +110,7 @@ test: publication subscription # select_views depends on create_view # ---------- test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock indirect_toast equivclass stats_rewrite +test: virtual_generated_index # ---------- # Another group of parallel tests (JSON related) @@ -116,6 +124,7 @@ test: json jsonb json_encoding jsonpath jsonpath_encoding jsonb_jsonpath sqljson # so keep this parallel group to at most 19 tests # ---------- test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml +test: virtual_generated_index # ---------- # Another group of parallel tests @@ -129,6 +138,7 @@ test: partition_merge partition_split partition_join partition_prune reloptions # any test that runs DDL # oidjoins is read-only, though, and should run late for best coverage test: oidjoins event_trigger +test: virtual_generated_index # event_trigger_login cannot run concurrently with any other tests because # on-login event handling could catch connection of a concurrent test. @@ -140,3 +150,4 @@ test: fast_default # run tablespace test at the end because it drops the tablespace created during # setup that other tests may use. test: tablespace +test: virtual_generated_index diff --git a/src/test/regress/sql/virtual_generated_index.sql b/src/test/regress/sql/virtual_generated_index.sql new file mode 100644 index 00000000000..66de42ecfbe --- /dev/null +++ b/src/test/regress/sql/virtual_generated_index.sql @@ -0,0 +1,20 @@ +-- Test restrictions on virtual generated columns in indexes + +CREATE TABLE t1 ( + a int, + b int GENERATED ALWAYS AS (a * 2) VIRTUAL +); + +-- Index creation should fail +CREATE INDEX idx_t1_b ON t1 (b); + +-- Partial index should fail +CREATE INDEX idx_partial ON t1 (b) WHERE b > 10; + +-- Primary key should fail +ALTER TABLE t1 ADD PRIMARY KEY (b); + +-- Expression index should fail +CREATE INDEX idx_expr ON t1 ((b + 1)); + +DROP TABLE t1; -- 2.34.1
