Re: bug: virtual generated column can be partition key

On Tue, May 6, 2025 at 5:57 PM Yura Sokolov <y.sokolov@postgrespro.ru> wrote:
>
> 21.04.2025 05:30, jian he пишет:
> > hi.
> > While trying to make the virtual generated column be part of the partition key,
> > I found this bug.
> > it also influences the stored generated column, i added a test
> > on generated_stored.sql.
> >
> > CREATE TABLE gtest_part_key (
> >     f1 date NOT NULL, f2 bigint,
> >     f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL)
> >     PARTITION BY RANGE (f3);
> >
> > ERROR:  cannot use generated column in partition key
> > LINE 4:     PARTITION BY RANGE (f3);
> >                                 ^
> > DETAIL:  Column "f3" is a generated column.
> >
> > the following is essentially the same as above, it should also fail.
> >
> > CREATE TABLE gtest_part_key (
> >     f1 date NOT NULL, f2 bigint,
> >     f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL)
> >     PARTITION BY RANGE ((f3));
>
> I don't understand why latter should fail?
>
> Documentation says [1]:
>
> > PostgreSQL allows you to declare that a table is divided into partitions.
> > The table that is divided is referred to as a partitioned table.
> > The declaration includes the partitioning method as described above,
> > plus a list of columns or expressions to be used as the partition key.
>
> Note: "list of columns or EXPRESSIONS"!
>
> In first case you pass list of columns (which contains single column f3). I
> don't get which internal restriction forces it to fail, really, but ok:
> there is restriction on COLUMNS LIST and it must be obeyed.
>
> But in second case you pass EXPRESSION, and I don't think same restriction
> should be applied.
>
> More over, if you look into comments on restriction on GENERATED columns
> [2] [3], you will find this restriction is because of nature of STORED
> generated columns, and it doesn't bound to VIRTUAL ones. Indeed, there is
> suggestion for future to treat GENERATED VIRTUAL columns as expressions.
>

hi.
you already pointed out the problem.
As of now GENERATED VIRTUAL columns are not supported as partition keys,
therefore we need to disallow generated columns being referenced in
the partition key in any representation.


currently in a matser:
CREATE TABLE xx (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED
ALWAYS AS (f2 * 2) virtual) PARTITION BY RANGE (f3);
will yield error, however
CREATE TABLE xx (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED
ALWAYS AS (f2 * 2) virtual) PARTITION BY RANGE ((f3));
will not.
but these two form essentially the same thing.


"PARTITION BY RANGE ((f3));"  and "PARTITION BY RANGE ((whole_row));"
are the corner cases we are trying to catch.

you may also see ComputePartitionAttrs.