From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Santosh Udupi <email@hitha.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: pg_restore - generated column - not populating
Date: Tue, 23 Feb 2021 15:21:38 -0800
Message-ID: <344c6530-cabf-12df-6814-401a7eb8a800@aklaver.com> (raw)
In-Reply-To: <CACLRvHb_uHK9gH7wyv5yXeZT_J_UG-Y5Zi78wkhMZdSurK=B=A@mail.gmail.com>
References: <CACLRvHZ4a-+SM_159+tcrHdEqxFrG=W4gwTRnwf7Oj0UNj5R2A@mail.gmail.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>
<72424ae7-0c66-5377-7db9-48425b853dd9@aklaver.com>
<CACLRvHb_uHK9gH7wyv5yXeZT_J_UG-Y5Zi78wkhMZdSurK=B=A@mail.gmail.com>
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
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> 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 <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>
> > <mailto:tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>>> wrote:
> >
> > Santosh Udupi <email@hitha.net <mailto:email@hitha.net>
> <mailto:email@hitha.net <mailto:email@hitha.net>>> 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 <mailto:adrian.klaver@aklaver.com>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: adrian.klaver@aklaver.com, email@hitha.net, tgl@sss.pgh.pa.us, pgsql-general@lists.postgresql.org
Subject: Re: pg_restore - generated column - not populating
In-Reply-To: <344c6530-cabf-12df-6814-401a7eb8a800@aklaver.com>
References: <CACLRvHZ4a-+SM_159+tcrHdEqxFrG=W4gwTRnwf7Oj0UNj5R2A@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> <72424ae7-0c66-5377-7db9-48425b853dd9@aklaver.com> <CACLRvHb_uHK9gH7wyv5yXeZT_J_UG-Y5Zi78wkhMZdSurK=B=A@mail.gmail.com> <344c6530-cabf-12df-6814-401a7eb8a800@aklaver.com>
* 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