Generated column and partitioning bug

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)

Responses

Browse pgsql-bugs by date

  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