From: Tom Lane <tgl@sss.pgh.pa.us>
To: Santosh Udupi <email@hitha.net>
Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: pg_restore - generated column - not populating
Date: Tue, 23 Feb 2021 20:03:06 -0500
Message-ID: <4007188.1614128586@sss.pgh.pa.us> (raw)
In-Reply-To: <CACLRvHZHqzVddKL8_v0-cMTezuXnyMAmpYErvJC2gghreVh72g@mail.gmail.com>
References: <CACLRvHZ4a-+SM_159+tcrHdEqxFrG=W4gwTRnwf7Oj0UNj5R2A@mail.gmail.com>
<9881df8c-f88e-977e-f222-adb04ba1ec80@aklaver.com>
<CACLRvHY+bD8R-=BWP6U5fBNu0qm_uevhjq6GJekMhPJ1Wyxghw@mail.gmail.com>
<CACLRvHYOp4uFbJPk_J35oDWs+fv_bpV0LrBQ4dJda_mWKgupUw@mail.gmail.com>
<CACLRvHYzbfhkg6Z2=HdrKv6YhcipXgnyMhEz0xx1dSALDcn_hQ@mail.gmail.com>
<ab320161-2511-2559-b190-f71b07b5d089@aklaver.com>
<CACLRvHaB_mLCygA6AQnc5jh+cBme2A-=Su9oDyO4O20k5ZSKSw@mail.gmail.com>
<89a62c70-4386-a3ef-f2ea-5e0f448cef11@aklaver.com>
<CACLRvHaK2tCmXPKSrjKAiQdVV0+zMQ_Gy3onpkFXD4VLnm46Ng@mail.gmail.com>
<0275a27c-2447-f8e5-489b-d7e419997299@aklaver.com>
<CACLRvHYQp2BdiS1w+Pt6tHsub=x19-E+L67R2hiJFNwbHY9jYw@mail.gmail.com>
<3874667.1614101644@sss.pgh.pa.us>
<CACLRvHaX_6fU_uoEJv=DPNYc4bcYOrmzn+e=dWhW4-com5K9zQ@mail.gmail.com>
<3877970.1614105611@sss.pgh.pa.us>
<CACLRvHZHqzVddKL8_v0-cMTezuXnyMAmpYErvJC2gghreVh72g@mail.gmail.com>
[ redirecting to pgsql-bugs ]
Santosh Udupi <email@hitha.net> writes:
> Here is my table structure.
Indeed, this looks pretty busted, both in v13 and HEAD. It seems that
pg_dump is not coping well with GENERATED columns attached to a
partition parent table. I made the attached script with a bit of
sample data, loaded it into an empty database, and dumped it.
The dump is evidently assuming that ALTER TABLE ATTACH PARTITION
is going to cause the generated-ness of the columns to propagate
to the children, but it doesn't. There also seems to be considerable
confusion about which columns of the child tables should be included
in the dumped data.
I suspect this example is revealing bugs in both the backend
(ATTACH PARTITION ought to take care of this, no?) and pg_dump
(the backend can't be blamed for pg_dump's choices of columns
to dump). Peter?
regards, tom lane
Attachments:partition_generated.sql (text/plain, 2.0K) download
create table tbl_main(
item_id int GENERATED ALWAYS AS IDENTITY,
-----------------------------------------------------
operating_offices int [] GENERATED ALWAYS AS (
nullif(array[(info->>'o')::int], '{NULL}') ) stored ,
-----------------------------------------------------
primary_bill_to_id int GENERATED ALWAYS as ((info->>'vp')::int) stored ,
----------------------------------------------
item_status_array text [] GENERATED ALWAYS as ( array[
coalesce(info->>'qr', info->>'s'), info->>'v'] ) stored ,
-------------------------------------------------
info jsonb
------------------------------
,is_complete bool GENERATED ALWAYS as (coalesce( (info->>'lf')::bool =
true or (info->>'lg')::bool = true, false)) stored
--------------------------------------------
,is_deleted bool GENERATED ALWAYS as ( coalesce( (info->>'cv')::bool,
false) ) stored
------------------------------
,is_a_template bool GENERATED ALWAYS as ( coalesce( (info->>'cw')::bool,
false) ) stored
-------------------------------------------
,created_by_user_id int
,created_on timestamptz default now()
----------------------------------
,primary key(item_id,created_on )
) partition by range (created_on) ;
---=================================================================
-- *** index
CREATE INDEX tbl_main_idxgin ON tbl_main USING gin (info);
---=================================================================
-- **** partitions
-- default partition
create table tbl_main_partition_default
partition of tbl_main default;
create table tbl_main_partition_2021
partition of tbl_main
for values from ('2020-01-01') to ('2022-01-01');
create table tbl_main_partition_2022
partition of tbl_main
for values from ('2022-01-01') to ('2023-01-01');
create table tbl_main_partition_2023
partition of tbl_main
for values from ('2023-01-01') to ('2024-01-01');
insert into tbl_main (info, created_on) values
('{"vp": 42, "o": 1000, "qr": "foo"}', '2023-06-01'),
('{"vp": 43, "o": 1001, "qr": "bar"}', '2013-06-01');
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, email@hitha.net, peter.eisentraut@enterprisedb.com, adrian.klaver@aklaver.com, pgsql-bugs@lists.postgresql.org
Subject: Re: pg_restore - generated column - not populating
In-Reply-To: <4007188.1614128586@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox