From: | Maxim Gasumyants <m(at)gasumyants(dot)com> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Generated column and partitioning bug |
Date: | 2022-02-04 12:22:35 |
Message-ID: | 95418DF5-D567-4FFC-A77A-DCE9B985692F@gasumyants.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
I am running latest release of PostgreSQL 12.
I have a table, which is partitioned like that:
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Julien Rouhaud | 2022-02-04 12:49:17 | Re: BUG #17394: pg_dump: query returned 0 rows instead of one: |
Previous Message | Dilip Kumar | 2022-02-04 10:14:46 | Re: BUG #17385: "RESET transaction_isolation" inside serializable transaction causes Assert at the transaction end |