Generated column and partitioning bug - Mailing list pgsql-bugs
From | Maxim Gasumyants |
---|---|
Subject | Generated column and partitioning bug |
Date | |
Msg-id | 95418DF5-D567-4FFC-A77A-DCE9B985692F@gasumyants.com Whole thread Raw |
Responses |
Re: Generated column and partitioning bug
|
List | pgsql-bugs |
I am running latest release of PostgreSQL 12.
p=# \d products_product_offers;
Partitioned table "public.products_product_offers"
Column | Type | Collation | Nullable | Default
------------------+-----------------------------+-----------+----------+------------------------------------------------------------------------------------------------------------------------------
----------------------
id | bigint | | not null |
shop_id | integer | | not null |
dimensions | jsonb | | |
purchasingprice | numeric(10,2) | | | NULL::numeric
volume | numeric | | | generated always as (((dimensions ->> 'x'::text)::numeric) * ((dimensions ->> 'y'::text)::numeric) * ((dimensions ->> 'z'::te
xt)::numeric)) stored
virtual_shop_id | integer | | |
Partition key: LIST (shop_id)
When I am adding partition, volume is generated from another column (which causes problem with inserting anything into this table):
p=# \d products_product_offers_shop_185456;
Table "public.products_product_offers_shop_185456"
Column | Type | Collation | Nullable | Default
------------------+-----------------------------+-----------+----------+------------------------------------------------------------------------------------------------------------------------------
-------------------------------------
id | bigint | | not null |
shop_id | integer | | not null |
dimensions | jsonb | | |
purchasingprice | numeric(10,2) | | | NULL::numeric
volume | numeric | | | generated always as (((purchasingprice ->> 'x'::text)::numeric) * ((purchasingprice ->> 'y'::text)::numeric) * ((purchasingpr
ice ->> 'z'::text)::numeric)) stored
virtual_shop_id | integer | | |
Partition of: products_product_offers FOR VALUES IN (185456)
If I am adding column once more:
p=# ALTER TABLE products_product_offers
p-# ADD COLUMN volumecalculated decimal GENERATED ALWAYS AS ((dimensions->>'x')::decimal * (dimensions->>'y')::decimal * (dimensions ->>'z')::decimal) STORED;
ALTER TABLE
It is ok:
p=# \d products_product_offers_shop_185456;
Table "public.products_product_offers_shop_185456"
Column | Type | Collation | Nullable | Default
------------------+-----------------------------+-----------+----------+------------------------------------------------------------------------------------------------------------------------------
-------------------------------------
id | bigint | | not null |
shop_id | integer | | not null |
dimensions | jsonb | | |
purchasingprice | numeric(10,2) | | | NULL::numeric
volume | numeric | | | generated always as (((purchasingprice ->> 'x'::text)::numeric) * ((purchasingprice ->> 'y'::text)::numeric) * ((purchasingpr
ice ->> 'z'::text)::numeric)) stored
virtual_shop_id | integer | | |
volumecalculated | numeric | | | generated always as (((dimensions ->> 'x'::text)::numeric) * ((dimensions ->> 'y'::text)::numeric) * ((dimensions ->> 'z'::te
xt)::numeric)) stored
Seems like it is inheriting wrong from parent table if I create partitions regular way:
p=# ALTER TABLE products_product_offers DETACH PARTITION products_product_offers_shop_185456;
ALTER TABLE
p=# DROP TABLE products_product_offers_shop_185456;
DROP TABLE
p=# CREATE TABLE products_product_offers_shop_185456 PARTITION OF products_product_offers FOR VALUES IN (185456);
CREATE TABLE
p=# \d products_product_offers_shop_185456;
Table "public.products_product_offers_shop_185456"
Column | Type | Collation | Nullable | Default
------------------+-----------------------------+-----------+----------+------------------------------------------------------------------------------------------------------------------------------
-------------------------------------
id | bigint | | not null |
shop_id | integer | | not null |
dimensions | jsonb | | |
purchasingprice | numeric(10,2) | | | NULL::numeric
volume | numeric | | | generated always as (((purchasingprice ->> 'x'::text)::numeric) * ((purchasingprice ->> 'y'::text)::numeric) * ((purchasingpr
ice ->> 'z'::text)::numeric)) stored
virtual_shop_id | integer | | |
volumecalculated | numeric | | | generated always as (((purchasingprice ->> 'x'::text)::numeric) * ((purchasingprice ->> 'y'::text)::numeric) * ((purchasingpr
ice ->> 'z'::text)::numeric)) stored
Partition of: products_product_offers FOR VALUES IN (185456)
pgsql-bugs by date: