Re: bug: virtual generated column can be partition key - Mailing list pgsql-hackers

On 2025/04/21 11:30, jian he wrote:
> hi.
> While trying to make the virtual generated column be part of the partition key,
> I found this bug.

I haven't looked at the patch in detail yet, but when I applied it
and ran the regression tests with RELCACHE_FORCE_RELEASE and
CATCACHE_FORCE_RELEASE enabled, the tests failed with the following diff:

----------------------------
========= Contents of ./src/test/regress/regression.diffs
diff -U3 /home/runner/work/postgresql/postgresql/src/test/regress/expected/create_table.out
/home/runner/work/postgresql/postgresql/src/test/regress/results/create_table.out
--- /home/runner/work/postgresql/postgresql/src/test/regress/expected/create_table.out    2025-04-21 07:32:03.731119788
+0000
+++ /home/runner/work/postgresql/postgresql/src/test/regress/results/create_table.out    2025-04-21 07:38:31.358134750
+0000
@@ -810,8 +810,13 @@
  LINE 1: ...TITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (c);
                                                                      ^
  CREATE TABLE part_c PARTITION OF parted (b WITH OPTIONS NOT NULL DEFAULT 0) FOR VALUES IN ('c') PARTITION BY RANGE
((b));
+ERROR:  cannot use generated column in partition key
+LINE 1: ...ULL DEFAULT 0) FOR VALUES IN ('c') PARTITION BY RANGE ((b));
+                                                                  ^
+DETAIL:  Column "b" is a generated column.
  -- create a level-2 partition
  CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
+ERROR:  relation "part_c" does not exist
  -- check that NOT NULL and default value are inherited correctly
  create table parted_notnull_inh_test (a int default 1, b int not null default 0) partition by list (a);
  create table parted_notnull_inh_test1 partition of parted_notnull_inh_test (a not null, b default 1) for values in
(1);
@@ -871,30 +876,8 @@
  
  -- Both partition bound and partition key in describe output
  \d+ part_c
-                             Partitioned table "public.part_c"
- Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- a      | text    |           |          |         | extended |              |
- b      | integer |           | not null | 0       | plain    |              |
-Partition of: parted FOR VALUES IN ('c')
-Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text))
-Partition key: RANGE (b)
-Not-null constraints:
-    "part_c_b_not_null" NOT NULL "b" (local, inherited)
-Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10)
-
  -- a level-2 partition's constraint will include the parent's expressions
  \d+ part_c_1_10
-                                Table "public.part_c_1_10"
- Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- a      | text    |           |          |         | extended |              |
- b      | integer |           | not null | 0       | plain    |              |
-Partition of: part_c FOR VALUES FROM (1) TO (10)
-Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text) AND (b IS NOT NULL) AND (b >= 1) AND (b < 10))
-Not-null constraints:
-    "part_c_b_not_null" NOT NULL "b" (inherited)
-
  -- Show partition count in the parent's describe output
  -- Tempted to include \d+ output listing partitions with bound info but
  -- output could vary depending on the order in which partition oids are
@@ -906,7 +889,7 @@
   a      | text    |           |          |
   b      | integer |           | not null | 0
  Partition key: LIST (a)
-Number of partitions: 3 (Use \d+ to list them.)
+Number of partitions: 2 (Use \d+ to list them.)
  
  \d hash_parted
        Partitioned table "public.hash_parted"
----------------------------

Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION