Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lEiaF-0001SJ-OD for pgsql-bugs@arkaria.postgresql.org; Wed, 24 Feb 2021 01:03:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lEiaD-0001KO-SS for pgsql-bugs@arkaria.postgresql.org; Wed, 24 Feb 2021 01:03:17 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lEiaD-0001KH-5H for pgsql-bugs@lists.postgresql.org; Wed, 24 Feb 2021 01:03:17 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lEiaA-0001AM-L4 for pgsql-bugs@lists.postgresql.org; Wed, 24 Feb 2021 01:03:15 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 11O136Zu4007189; Tue, 23 Feb 2021 20:03:06 -0500 From: Tom Lane To: Santosh Udupi cc: Peter Eisentraut , Adrian Klaver , pgsql-bugs@lists.postgresql.org Subject: Re: pg_restore - generated column - not populating In-reply-to: References: <9881df8c-f88e-977e-f222-adb04ba1ec80@aklaver.com> <89a62c70-4386-a3ef-f2ea-5e0f448cef11@aklaver.com> <0275a27c-2447-f8e5-489b-d7e419997299@aklaver.com> <3874667.1614101644@sss.pgh.pa.us> <3877970.1614105611@sss.pgh.pa.us> Comments: In-reply-to Santosh Udupi message dated "Tue, 23 Feb 2021 12:15:21 -0800" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <4007030.1614128400.0@sss.pgh.pa.us> Date: Tue, 23 Feb 2021 20:03:06 -0500 Message-ID: <4007188.1614128586@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <4007030.1614128400.1@sss.pgh.pa.us> [ redirecting to pgsql-bugs ] Santosh Udupi 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 ------- =_aaaaaaaaaa0 Content-Type: text/plain; name="partition_generated.sql"; charset="us-ascii" Content-ID: <4007030.1614128400.2@sss.pgh.pa.us> Content-Description: partition_generated.sql 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'); ------- =_aaaaaaaaaa0--