06.05.2025 13:31, jian he пишет: > On Tue, May 6, 2025 at 5:57 PM Yura Sokolov <y.soko...@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.
I don't see why "we need to disallow". There are no fundamental reasons. May be it is better to allow GENERATED VIRTUAL columns? But still forbid GENERATED STORED columns because there are reasons for. > 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. I saw it. And I gave links to comments in this function. This comments clearly state, there is no real reason to forbid GENERATED VIRTUAL columns (in opposite to STORED). They are forbidden just because author had no time/wish to finish their support. -- regards Yura Sokolov aka funny-falcon
