From: | jian he <jian(dot)universality(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | virtual generated column as partition key |
Date: | 2025-04-23 11:59:33 |
Message-ID: | CACJufxF9RV2_iHBAG0AfCyt5c-1zJkh_Jc7F1tZfk3m3N+E=qA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
v1-0001-virtual-generated-column-as-partition-key.patch | text/x-patch | 37.7 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Matheus Alcantara | 2025-04-23 12:18:48 | Re: extension_control_path and "directory" |
Previous Message | jian he | 2025-04-23 11:36:52 | Re: bug: virtual generated column can be partition key |