pg_restore - generated column - not populating
Hi all, My database has tables with generated columns. I altered a table and added a generated column as below: alter table billing add primary_bill_to_id int GENERATED ALWAYS as ((info->>'vp')::int) stored Now, when I do the pg_dump and pg_restore, this column does not get populated. It remains null "Info" is the jsonb column in the table and info->>'vp' has values in multiple rows but still generated column "primary_bill_to_id" is null after the restore I am using postgres version 13 Can you tell me what am I missing? Thank you for your help. Santosh
Re: pg_restore - generated column - not populating
I used the following commands for dump pg_dump -c mydb | gzip -9 > mydb.gz pg_dump -C -Fc mydb > mydb.backup pg_dump -Ft mydb > mydb.tar For restore, I created a blank database by issuing the command "createdb mydb" and then tried gunzip -c mydb.gz | psql mydb pg_restore -d mydb mydb.backup pg_restore -Ft -d mydb mydb.backup I have tried -c, -C, schema only etc but nothing has worked so far. I didn't check the Postgres logs. Thanks for the suggestion. I will check that. On Mon, Feb 22, 2021 at 5:16 PM Adrian Klaver wrote: > On 2/22/21 5:08 PM, Santosh Udupi wrote: > > Hi all, > > > > My database has tables with generated columns. I altered a table and > > added a generated column as below: > > > > alter table billing add primary_bill_to_id int GENERATED ALWAYS as > > ((info->>'vp')::int) stored > > > > > > Now, when I do the pg_dump and pg_restore, this column does not get > > populated. It remains null > > > > "Info" is the jsonb column in the table and info->>'vp' has values in > > multiple rows but still generated column "primary_bill_to_id" is null > > after the restore > > > > I am using postgres version 13 > > > > Can you tell me what am I missing? > > Without the commands you used to do the dump and restore it will be > difficult to come to any conclusions. It would also be helpful to look > at the Postgres logs from the restore to see if there are any error > messages. > > > > > Thank you for your help. > > Santosh > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: pg_restore - generated column - not populating
The logs don't show errors. I came across something similar here https://www.postgresql-archive.org/Dumping-restoring-fails-on-inherited-generated-column-td6114378.html but not sure what the solution is. On Mon, Feb 22, 2021 at 5:57 PM Santosh Udupi wrote: > I used the following commands for dump > > pg_dump -c mydb | gzip -9 > mydb.gz > pg_dump -C -Fc mydb > mydb.backup > pg_dump -Ft mydb > mydb.tar > > For restore, I created a blank database by issuing the command "createdb > mydb" and then tried > > gunzip -c mydb.gz | psql mydb > pg_restore -d mydb mydb.backup > pg_restore -Ft -d mydb mydb.backup > > I have tried -c, -C, schema only etc but nothing has worked so far. > > I didn't check the Postgres logs. Thanks for the suggestion. I will check > that. > > > > On Mon, Feb 22, 2021 at 5:16 PM Adrian Klaver > wrote: > >> On 2/22/21 5:08 PM, Santosh Udupi wrote: >> > Hi all, >> > >> > My database has tables with generated columns. I altered a table and >> > added a generated column as below: >> > >> > alter table billing add primary_bill_to_id int GENERATED ALWAYS as >> > ((info->>'vp')::int) stored >> > >> > >> > Now, when I do the pg_dump and pg_restore, this column does not get >> > populated. It remains null >> > >> > "Info" is the jsonb column in the table and info->>'vp' has values in >> > multiple rows but still generated column "primary_bill_to_id" is null >> > after the restore >> > >> > I am using postgres version 13 >> > >> > Can you tell me what am I missing? >> >> Without the commands you used to do the dump and restore it will be >> difficult to come to any conclusions. It would also be helpful to look >> at the Postgres logs from the restore to see if there are any error >> messages. >> >> > >> > Thank you for your help. >> > Santosh >> > >> >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com >> >
Re: pg_restore - generated column - not populating
If I backup using pgAdmin, I am able to restore using pg_restore but for some reason, pg_rsestore on the output from pg_dump does not create values for the generated columns On Mon, Feb 22, 2021 at 6:20 PM Santosh Udupi wrote: > The logs don't show errors. I came across something similar here > https://www.postgresql-archive.org/Dumping-restoring-fails-on-inherited-generated-column-td6114378.html > > > but not sure what the solution is. > > On Mon, Feb 22, 2021 at 5:57 PM Santosh Udupi wrote: > >> I used the following commands for dump >> >> pg_dump -c mydb | gzip -9 > mydb.gz >> pg_dump -C -Fc mydb > mydb.backup >> pg_dump -Ft mydb > mydb.tar >> >> For restore, I created a blank database by issuing the command "createdb >> mydb" and then tried >> >> gunzip -c mydb.gz | psql mydb >> pg_restore -d mydb mydb.backup >> pg_restore -Ft -d mydb mydb.backup >> >> I have tried -c, -C, schema only etc but nothing has worked so far. >> >> I didn't check the Postgres logs. Thanks for the suggestion. I will check >> that. >> >> >> >> On Mon, Feb 22, 2021 at 5:16 PM Adrian Klaver >> wrote: >> >>> On 2/22/21 5:08 PM, Santosh Udupi wrote: >>> > Hi all, >>> > >>> > My database has tables with generated columns. I altered a table and >>> > added a generated column as below: >>> > >>> > alter table billing add primary_bill_to_id int GENERATED ALWAYS >>> as >>> > ((info->>'vp')::int) stored >>> > >>> > >>> > Now, when I do the pg_dump and pg_restore, this column does not get >>> > populated. It remains null >>> > >>> > "Info" is the jsonb column in the table and info->>'vp' has values in >>> > multiple rows but still generated column "primary_bill_to_id" is null >>> > after the restore >>> > >>> > I am using postgres version 13 >>> > >>> > Can you tell me what am I missing? >>> >>> Without the commands you used to do the dump and restore it will be >>> difficult to come to any conclusions. It would also be helpful to look >>> at the Postgres logs from the restore to see if there are any error >>> messages. >>> >>> > >>> > Thank you for your help. >>> > Santosh >>> > >>> >>> >>> -- >>> Adrian Klaver >>> adrian.kla...@aklaver.com >>> >>
Re: pg_restore - generated column - not populating
The pg_restore command is actually pg_restore -Ft -d mydb mydb.tar (my mistake). I didn't provide the -h -p -U since I use the super user account to restore (I will try adding them). The restore had always worked until I altered the table in the source database. After I added the column, the restore still takes place but does not populate the generated column. I did a backup using pgAdmin and the restore populated all data using the same syntax on the tar file. So my suspicion is that pg_dump is not doing the dump correctly. I will work on it further. Thanks for your suggestions. On Mon, Feb 22, 2021 at 9:23 PM Adrian Klaver wrote: > On 2/22/21 7:43 PM, Santosh Udupi wrote: > > If I backup using pgAdmin, I am able to restore using pg_restore but for > > some reason, pg_rsestore on the output from pg_dump does not create > > values for the generated columns > > > > To troubleshoot this: > > 1) Stick to one dump/restore combination. The three versions you tried > before just confuse the issue. For instance: > > pg_dump -Ft mydb > mydb.tar > pg_restore -Ft -d mydb mydb.backup > > makes no sense. As mydb.backup came from: > > pg_dump -C -Fc mydb > mydb.backup > > I have not tested, but I'm pretty sure the pg_restore just ignored the > -Ft and just did -Fc. > > 2) Big explicit in your dump and restore commands for -h(ost), -p(ort) > and -U(ser). I suspect you may not be restoring to where you think you are. > > 3) Closely follow the progress of both the dump and the restore. > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: pg_restore - generated column - not populating
Got it. Must be the version difference. I run pgAdmin on Windows PC but direct pg_dump on Ubuntu 20.04. On Tue, Feb 23, 2021 at 7:27 AM Adrian Klaver wrote: > On 2/23/21 6:36 AM, Santosh Udupi wrote: > > The pg_restore command is actually pg_restore -Ft -d mydb mydb.tar (my > > mistake). > > > > I didn't provide the -h -p -U since I use the super user account to > > restore (I will try adding them). The restore had always worked until I > > altered the table in the source database. > > > > After I added the column, the restore still takes place but does not > > populate the generated column. I did a backup using pgAdmin and the > > restore populated all data using the same syntax on the tar file. So my > > suspicion is that pg_dump is not doing the dump correctly. I will work > > on it further. Thanks for your suggestions. > > > > pgAdmin uses pg_dump to do backups. > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: pg_restore - generated column - not populating
Right. pg_dump under the pgAdmin runtime folder works perfectly. pg_dump in postgres13 (ubuntu) does not work. Exact same syntax. When I try to restore, the backup that was taken using pgAdmin's version restores properly but the one taken using postgres13's pg_dump, restores the database but does not populate the generated columns. Strange!! On Tue, Feb 23, 2021 at 7:50 AM Adrian Klaver wrote: > On 2/23/21 7:39 AM, Santosh Udupi wrote: > > Got it. Must be the version difference. I run pgAdmin on Windows PC but > > direct pg_dump on Ubuntu 20.04. > > The OS does not really make a difference it is the pg_dump/restore > versions and the Postgres server(s) versions that are important. > > > > > On Tue, Feb 23, 2021 at 7:27 AM Adrian Klaver > <mailto:adrian.kla...@aklaver.com>> wrote: > > > > On 2/23/21 6:36 AM, Santosh Udupi wrote: > > > The pg_restore command is actually pg_restore -Ft -d mydb > > mydb.tar (my > > > mistake). > > > > > > I didn't provide the -h -p -U since I use the super user account > to > > > restore (I will try adding them). The restore had always worked > > until I > > > altered the table in the source database. > > > > > > After I added the column, the restore still takes place but does > not > > > populate the generated column. I did a backup using pgAdmin and > the > > > restore populated all data using the same syntax on the tar file. > > So my > > > suspicion is that pg_dump is not doing the dump correctly. I will > > work > > > on it further. Thanks for your suggestions. > > > > > > > pgAdmin uses pg_dump to do backups. > > > > > > -- > > Adrian Klaver > > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: pg_restore - generated column - not populating
Both are different versions The following works: Version: pg_dump (pgAdmin Windows) version:13.1 Method: Backup using Windows connecting to the remote host: Command: pg_dump -Fc -p 5432 -h -d mydb > mydb.backup1 -U postgres Restore locally on Ubuntu : pg_restore -d mydb-restore mydb.backup1 --- 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 Method: Backup locally on Ubuntu 20.04 Command: pg_dump -Fc -p 5432 -h localhost -d mydb > mydb.backup2 -U postgres Restore locally on Ubuntu : pg_restore -d mydb-restore mydb.backup2 On Tue, Feb 23, 2021 at 9:34 AM Tom Lane wrote: > Santosh Udupi writes: > > Right. pg_dump under the pgAdmin runtime folder works perfectly. pg_dump > > in postgres13 (ubuntu) does not work. Exact same syntax. > > So, are these identical pg_dump versions? We did fix some things > in this area in 13.2. > > regards, tom lane >
Re: pg_restore - generated column - not populating
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) ;
---=
-- *** 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
>
Re: pg_restore - generated column - not populating
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.
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 > <mailto:t...@sss.pgh.pa.us>> wrote:
> >
> > Santosh Udupi mailto:em...@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.kla...@aklaver.com
>
Re: pg_restore - generated column - not populating
Yes, this is what we have been doing now:- Backup using pg_dump, create
the new database at the destination, manually create the tables which give
problems, and then do the pg_restore. Another solution for us is to backup
using pg_dump that comes with pgAdmin (Windows), rsync it to the
destination server and then do the pg_restore on the server.
On Tue, Feb 23, 2021 at 3:21 PM Adrian Klaver
wrote:
> 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
> > mailto:adrian.kla...@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');
>
Re: pg_restore - generated column - not populating
Sure. I will try that. On Tue, Feb 23, 2021 at 4:42 PM Adrian Klaver wrote: > On 2/23/21 4:25 PM, Santosh Udupi wrote: > > Yes, this is what we have been doing now:- Backup using pg_dump, create > > the new database at the destination, manually create the tables which > > give problems, and then do the pg_restore. Another solution for us is to > > Well that introduces another error, where the restore trips on the > existing table(s). By the way this is the first time you mentioned > multiple tables. Do they share similar structure? In any case you should > not have to do this and in the spirit of identifying the problem and > fixing it, what happens if you do: > > 1) Dump locally > > 2) Restore locally, without creating the tables ahead. > > 3) Examine and report back here the table(s) schema(and sample data) > after the fresh restore. > > 4) Drop the table(s) and create from script and populate. Report on > whether they work. > > > backup using pg_dump that comes with pgAdmin (Windows), rsync it to the > > destination server and then do the pg_restore on the server. > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
