Generated column and partitioning bug
| От | Maxim Gasumyants |
|---|---|
| Тема | Generated column and partitioning bug |
| Дата | |
| Msg-id | 95418DF5-D567-4FFC-A77A-DCE9B985692F@gasumyants.com обсуждение исходный текст |
| Ответы |
Re: Generated column and partitioning bug
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| Список | 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 по дате отправления:
Предыдущее
От: Dilip KumarДата:
Сообщение: Re: BUG #17385: "RESET transaction_isolation" inside serializable transaction causes Assert at the transaction end
Следующее
От: Julien RouhaudДата:
Сообщение: Re: BUG #17394: pg_dump: query returned 0 rows instead of one: