Re: bug: virtual generated column can be partition key

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.

So, if you want to force some restriction, you should force it only against
STORED columns, but not VIRTUAL ones.
Of cause, if VIRTUAL column depends on STORED one, it is still should be
forbidden.

Certainly, it is my opinion and I could be mistaken somewhere.
For example, you may say "it is too hard to check dependency of VIRTUAL
column at the moment, so it is simpler to forbid them as well". But then it
should be clearly stated in commit messages and code comments.

[1]
https://www.postgresql.org/docs/17/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE
[2]
https://github.com/postgres/postgres/blob/caa76b91a60681dff0bf193b64d4dcdc1014e036/src/backend/commands/tablecmds.c#L19735-L19741
[3]
https://github.com/postgres/postgres/blob/caa76b91a60681dff0bf193b64d4dcdc1014e036/src/backend/commands/tablecmds.c#L19821-L19828

-- 
regards
Yura Sokolov aka funny-falcon