hi.
The attached patch is to implement $subject.
demo:
CREATE TABLE t(f1 bigint, f2 bigint GENERATED ALWAYS AS (f1 * 2)
VIRTUAL) PARTITION BY RANGE (f2);
it will works just fine as
CREATE TABLE t(f1 bigint, f2 bigint GENERATED ALWAYS AS (f1 * 2)
VIRTUAL) PARTITION BY RANGE ((f1 *2 );
under the hood.
but the partition key can not be an expression on top of a virtual
generated column.
so the following is not allowed:
CREATE TABLE t(f1 bigint, f2 bigint GENERATED ALWAYS AS (f1 * 2)
VIRTUAL) PARTITION BY RANGE ((f2+1));
The virtual generated column expression for each partition must match with
the partitioned table, since it is used as a partition key. Otherwise, the
partition bound would be dynamically evaluated.
so the following table gtest_part_key1_0 can not attach to the partition tree.
CREATE TABLE gtest_part_key1 (f1 date, f2 bigint, f3 bigint GENERATED
ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE ((f3)); --ok
CREATE TABLE gtest_part_key1_0(f3 bigint GENERATED ALWAYS AS (f2 * 3)
VIRTUAL, f2 bigint, f1 date);
ALTER TABLE gtest_part_key1 ATTACH PARTITION gtest_part_key1_0 FOR
VALUES FROM (20) TO (30); --error
cross partition update tests added.
A virtual generated column entry in the pg_partitioned_table catalog is marked
as non-zero partattrs and a non-null partexprs, which is abnormal. Normally,
either partattrs is non-zero or partexprs is null.
we should mention this in the doc/src/sgml/catalogs.sgml
