virtual generated column as partition key

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

Browse pgsql-hackers by date

  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