Обсуждение: Generated column and partitioning bug
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)
Maxim Gasumyants <m@gasumyants.com> writes:
> I am running latest release of PostgreSQL 12.
Are you certain it's the latest? This looks suspiciously like
some Var-numbering bugs we fixed a year or so ago.
I failed to reproduce any problem in 12.9 on the basis of the
info you supplied. I suppose that there might be some additional
conditions needed, like a dropped column in the parent table,
but experimentation didn't find it.
It might be useful to show the results of
select attname, attnum, attisdropped from pg_attribute
where attrelid = 'products_product_offers'::regclass and attnum > 0;
Also, if you can make a self-contained SQL script that exhibits
the problem for you, that would be very useful.
regards, tom lane
Hi, Tom!
I will check version once more - I wrote that it latest version based on the thing, that yum does not pull any updates. Here is the command for main table and partitioned table:
p-# where attrelid = 'products_product_offers'::regclass and attnum > 0;
attname | attnum | attisdropped
-------------------------------+--------+--------------
id | 1 | f
shop_id | 2 | f
........pg.dropped.3........ | 3 | t
parent_id | 4 | f
........pg.dropped.5........ | 5 | t
currency_id | 6 | f
article | 7 | f
name | 8 | f
type | 9 | f
image | 10 | f
barcodes | 11 | f
sku | 12 | f
dimensions | 13 | f
weight | 14 | f
purchasingprice | 15 | f
price | 16 | f
state | 17 | f
created | 18 | f
updated | 19 | f
extid | 20 | f
raw | 21 | f
eav | 22 | f
doc | 23 | f
docupdated | 24 | f
parent_shop_id | 25 | f
items | 26 | f
docextended | 27 | f
........pg.dropped.28........ | 28 | t
inventoryupdated | 29 | f
........pg.dropped.30........ | 30 | t
........pg.dropped.31........ | 31 | t
virtual_shop_id | 32 | f
volume | 33 | f
(33 rows)
p=# select attname, attnum, attisdropped from pg_attribute
where attrelid = 'products_product_offers_shop_185456'::regclass and attnum > 0;
attname | attnum | attisdropped
-------------------------------+--------+--------------
id | 1 | f
shop_id | 2 | f
parent_id | 3 | f
currency_id | 4 | f
article | 5 | f
name | 6 | f
type | 7 | f
image | 8 | f
barcodes | 9 | f
sku | 10 | f
dimensions | 11 | f
weight | 12 | f
purchasingprice | 13 | f
price | 14 | f
state | 15 | f
created | 16 | f
updated | 17 | f
extid | 18 | f
raw | 19 | f
eav | 20 | f
doc | 21 | f
docupdated | 22 | f
parent_shop_id | 23 | f
items | 24 | f
docextended | 25 | f
inventoryupdated | 26 | f
........pg.dropped.27........ | 27 | t
virtual_shop_id | 28 | f
volume | 29 | f
(29 rows)
4 февр. 2022 г., в 20:04, Tom Lane <tgl@sss.pgh.pa.us> написал(а):Maxim Gasumyants <m@gasumyants.com> writes:I am running latest release of PostgreSQL 12.
Are you certain it's the latest? This looks suspiciously like
some Var-numbering bugs we fixed a year or so ago.
I failed to reproduce any problem in 12.9 on the basis of the
info you supplied. I suppose that there might be some additional
conditions needed, like a dropped column in the parent table,
but experimentation didn't find it.
It might be useful to show the results of
select attname, attnum, attisdropped from pg_attribute
where attrelid = 'products_product_offers'::regclass and attnum > 0;
Also, if you can make a self-contained SQL script that exhibits
the problem for you, that would be very useful.
regards, tom lane
Maxim Gasumyants <m@gasumyants.com> writes:
> I will check version once more - I wrote that it latest version based on the thing, that yum does not pull any
updates.Here is the command for main table and partitioned table:
> p-# where attrelid = 'products_product_offers'::regclass and attnum > 0;
> attname | attnum | attisdropped
> -------------------------------+--------+--------------
> id | 1 | f
> shop_id | 2 | f
> ........pg.dropped.3........ | 3 | t
> parent_id | 4 | f
> ........pg.dropped.5........ | 5 | t
> currency_id | 6 | f
> article | 7 | f
> name | 8 | f
> type | 9 | f
> image | 10 | f
> barcodes | 11 | f
> sku | 12 | f
> dimensions | 13 | f
> weight | 14 | f
> purchasingprice | 15 | f
Hm, I think that confirms my idea about the underlying nature of the bug.
The parent's stored expression for volume would refer to dimensions as
being var 13. Now, when you create a new child partition it would have no
dropped columns, so that dimensions would be column 11 and purchasingprice
would be column 13 in the child. Thus, the observed symptom is explained
if we failed to renumber the vars in the GENERATED expression while copying
it to the child partition.
However, AFAICT we do that correctly. I didn't bisect, but I think
this was fixed by commit d9253df12, which shipped in PG 12.5.
Please double-check your server version, eg with "select version();"
regards, tom lane
You are right - we were using 12.2, just I didn’t confirmed it well.
Thank you so much, Tom.
4 февр. 2022 г., в 21:00, Tom Lane <tgl@sss.pgh.pa.us> написал(а):Maxim Gasumyants <m@gasumyants.com> writes:I will check version once more - I wrote that it latest version based on the thing, that yum does not pull any updates. Here is the command for main table and partitioned table:p-# where attrelid = 'products_product_offers'::regclass and attnum > 0;
attname | attnum | attisdropped
-------------------------------+--------+--------------
id | 1 | f
shop_id | 2 | f
........pg.dropped.3........ | 3 | t
parent_id | 4 | f
........pg.dropped.5........ | 5 | t
currency_id | 6 | f
article | 7 | f
name | 8 | f
type | 9 | f
image | 10 | f
barcodes | 11 | f
sku | 12 | f
dimensions | 13 | f
weight | 14 | f
purchasingprice | 15 | f
Hm, I think that confirms my idea about the underlying nature of the bug.
The parent's stored expression for volume would refer to dimensions as
being var 13. Now, when you create a new child partition it would have no
dropped columns, so that dimensions would be column 11 and purchasingprice
would be column 13 in the child. Thus, the observed symptom is explained
if we failed to renumber the vars in the GENERATED expression while copying
it to the child partition.
However, AFAICT we do that correctly. I didn't bisect, but I think
this was fixed by commit d9253df12, which shipped in PG 12.5.
Please double-check your server version, eg with "select version();"
regards, tom lane