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 1lEh04-0004aw-6Z for pgsql-general@arkaria.postgresql.org; Tue, 23 Feb 2021 23:21:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lEh03-0005Y3-1Y for pgsql-general@arkaria.postgresql.org; Tue, 23 Feb 2021 23:21:51 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lEh02-0005Xw-0t for pgsql-general@lists.postgresql.org; Tue, 23 Feb 2021 23:21:50 +0000 Received: from out3-smtp.messagingengine.com ([66.111.4.27]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lEgzu-0001AZ-NY for pgsql-general@lists.postgresql.org; Tue, 23 Feb 2021 23:21:49 +0000 Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailout.nyi.internal (Postfix) with ESMTP id 56A575C00E3; Tue, 23 Feb 2021 18:21:40 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute3.internal (MEProxy); Tue, 23 Feb 2021 18:21:40 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= subject:to:cc:references:from:message-id:date:mime-version :in-reply-to:content-type:content-transfer-encoding; s=fm2; bh=U NXCa2nq3WFGF/Cs6WyAyqXO+tmebph1mNG60YV2iUA=; b=OWkXeWWGZRs7+07sy 8qy88wsMJ95zeOVWIS4e0jWY8OrSVamdZ9NCwCKdtRbHUGDq3RQ/Jys55YNHWpn2 gGhggRpCyVrsnH8BtfYnWZD7lFa7BySN9CJw2gur80V337FHumVcqEqDFt0xB6na rOPKwrDk2FfQ82m7ombgL6pj0MQCyFzgWI9TM4d+Mxs2qkLe0P91QQ+uGJaDb4um TdoByJ2ANsQXV606B1+Y5OIM91B6pTrccNfGNTL2/3bEo8Z1LDwUXDoGaBHwMLiR a1717yJ2sOvzR1Idssh1M73JkJjoLWKAYoHlSHQEzKjzSgzRg8k9DEjOToQHf3aI dogXQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :date:from:in-reply-to:message-id:mime-version:references :subject:to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender :x-sasl-enc; s=fm2; bh=UNXCa2nq3WFGF/Cs6WyAyqXO+tmebph1mNG60YV2i UA=; b=A5GJMZUJ54upvIWEj5zLJiPdrWeKgdwCHSNCoSDqvmlrbl9rvDF5mFPP9 3oEfHiVqtKg4lx9aq8mRUiBAZfVqFIcBJALv6RAEdY/lI7r+4N/HpMwgpCGN7C5S HKdaTymRrM5eZRGLw9Dj78mRho3tTQ5TEkfgjuVjCthfOpSOpWlmrvORWOo5cGUq mJWW28nKP0W9rdBW0g5fwEADHg5JUeA2u4DmppC6brGGQZT9h+jV+Er3fmN8oGfJ ha6M6fAA95kVVxTW3OA9Ycj1VnDVFoiTYiSwd4R/5zxGpE8GznDV4AulC4MVvTA+ EN4BbMyhKn4UUeiig19IBpuvwAT0A== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeduledrkeeigddtiecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecunecujfgurhepuffvfhfhkffffgggjggtgfesthekre dttdefjeenucfhrhhomheptegurhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhl rghvvghrsegrkhhlrghvvghrrdgtohhmqeenucggtffrrghtthgvrhhnpeevveefveefue egffekkefhgeeiueffgfetueejvdfghfeujeejhfekjeelgedtgeenucfkphephedtrddu leejrdektddrvdenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfh hrohhmpegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Received: from ranger.suse (50-197-80-2-static.hfc.comcastbusiness.net [50.197.80.2]) by mail.messagingengine.com (Postfix) with ESMTPA id 58FA01080063; Tue, 23 Feb 2021 18:21:39 -0500 (EST) Subject: Re: pg_restore - generated column - not populating To: Santosh Udupi Cc: Tom Lane , pgsql-general@lists.postgresql.org References: <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> <72424ae7-0c66-5377-7db9-48425b853dd9@aklaver.com> From: Adrian Klaver Message-ID: <344c6530-cabf-12df-6814-401a7eb8a800@aklaver.com> Date: Tue, 23 Feb 2021 15:21:38 -0800 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.7.0 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8; format=flowed Content-Language: en-US Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 2/23/21 12:57 PM, Santosh Udupi wrote: > So that it makes it a lot easier for the application logic just to > collect json fields and update in one column "info" instead of including > multiple columns in the insert/update statements. I doubt it, but then again this why I don't answer Postgres/JSON SO questions anymore. Trying to apply logic to the contortions people go to make their life more difficult left me with headaches. At any rate this is getting off-topic for the the dump/restore issue you have. When you look at the restored tables in each variation of the restore database do they look the same as below? If you drop the table in the problem database and then recreate it using the script below and then populate it with data does it work? > > On Tue, Feb 23, 2021 at 12:20 PM Adrian Klaver > > wrote: > > On 2/23/21 12:15 PM, Santosh Udupi wrote: > > Here is my table structure. I will try to get the pg_dump output for > > this table in both the versions. > > > > 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) ; > > Which generates(pun intended) the question, why? You are deconstructing > info into its component parts after the fact, why not just input the > data directly into the fields. > > > > > ---================================================================= > > -- *** 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'); > > > > ---================================================================= > > > > > > > > On Tue, Feb 23, 2021 at 10:40 AM Tom Lane > > >> wrote: > > > >     Santosh Udupi > >> writes: > >      > Both are different versions > >      > The following works: > >      > Version: pg_dump (pgAdmin Windows)  version:13.1 > > > >      > The following does not work: (Does not populate the > generated column > >      > values) > >      > Version: pg_dump (PostgreSQL v13.2 on Ubuntu 20.04) > version 13.2 > > > >     Hmm ... well, that would be a regression, but you're going to > have > >     to show us how to reproduce it.  I'm thinking there must be > something > >     odd about the way the table is declared. > > > >                              regards, tom lane > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > -- Adrian Klaver adrian.klaver@aklaver.com