Thread
Commits
-
pg_dump: Fix dump of generated columns in partitions
- bdaa0d2d7269 12.7 landed
- a6a3a274abd1 13.3 landed
- feb270d1005f 14.0 landed
-
pg_restore - generated column - not populating
Santosh Udupi <email@hitha.net> — 2021-02-23T01:08:17Z
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
Adrian Klaver <adrian.klaver@aklaver.com> — 2021-02-23T01:16:48Z
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.klaver@aklaver.com
-
Re: pg_restore - generated column - not populating
Santosh Udupi <email@hitha.net> — 2021-02-23T01:57:36Z
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 <adrian.klaver@aklaver.com> 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.klaver@aklaver.com >
-
Re: pg_restore - generated column - not populating
Santosh Udupi <email@hitha.net> — 2021-02-23T02:20:47Z
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 <email@hitha.net> 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 <adrian.klaver@aklaver.com> > 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.klaver@aklaver.com >> >
-
Re: pg_restore - generated column - not populating
Santosh Udupi <email@hitha.net> — 2021-02-23T03:43:36Z
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 <email@hitha.net> 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 <email@hitha.net> 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 <adrian.klaver@aklaver.com> >> 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.klaver@aklaver.com >>> >>
-
Re: pg_restore - generated column - not populating
Adrian Klaver <adrian.klaver@aklaver.com> — 2021-02-23T05:23:02Z
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.klaver@aklaver.com
-
Re: pg_restore - generated column - not populating
Santosh Udupi <email@hitha.net> — 2021-02-23T14:36:26Z
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 <adrian.klaver@aklaver.com> 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.klaver@aklaver.com >
-
Re: pg_restore - generated column - not populating
Adrian Klaver <adrian.klaver@aklaver.com> — 2021-02-23T15:26:57Z
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.klaver@aklaver.com
-
Re: pg_restore - generated column - not populating
Santosh Udupi <email@hitha.net> — 2021-02-23T15:39:15Z
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 <adrian.klaver@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.klaver@aklaver.com >
-
Re: pg_restore - generated column - not populating
Adrian Klaver <adrian.klaver@aklaver.com> — 2021-02-23T15:50:48Z
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 <adrian.klaver@aklaver.com > <mailto:adrian.klaver@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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com
-
Re: pg_restore - generated column - not populating
Santosh Udupi <email@hitha.net> — 2021-02-23T17:18:00Z
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 <adrian.klaver@aklaver.com> 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 <adrian.klaver@aklaver.com > > <mailto:adrian.klaver@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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com >
-
Re: pg_restore - generated column - not populating
Tom Lane <tgl@sss.pgh.pa.us> — 2021-02-23T17:34:04Z
Santosh Udupi <email@hitha.net> 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
Santosh Udupi <email@hitha.net> — 2021-02-23T18:18:52Z
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 <remotehost_ip> -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 <tgl@sss.pgh.pa.us> wrote: > Santosh Udupi <email@hitha.net> 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
Tom Lane <tgl@sss.pgh.pa.us> — 2021-02-23T18:40:11Z
Santosh Udupi <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
-
Re: pg_restore - generated column - not populating
Santosh Udupi <email@hitha.net> — 2021-02-23T20:15:21Z
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 <tgl@sss.pgh.pa.us> wrote: > Santosh Udupi <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 > -
Re: pg_restore - generated column - not populating
Adrian Klaver <adrian.klaver@aklaver.com> — 2021-02-23T20:20:48Z
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>> wrote: > > Santosh Udupi <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 -
Re: pg_restore - generated column - not populating
Santosh Udupi <email@hitha.net> — 2021-02-23T20:57:01Z
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 <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>> wrote: > > > > Santosh Udupi <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 > -
Re: pg_restore - generated column - not populating
Adrian Klaver <adrian.klaver@aklaver.com> — 2021-02-23T23:21:38Z
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 -
Re: pg_restore - generated column - not populating
Santosh Udupi <email@hitha.net> — 2021-02-24T00:25:05Z
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 <adrian.klaver@aklaver.com> 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 > > <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 > -
Re: pg_restore - generated column - not populating
Adrian Klaver <adrian.klaver@aklaver.com> — 2021-02-24T00:42:23Z
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.klaver@aklaver.com
-
Re: pg_restore - generated column - not populating
Santosh Udupi <email@hitha.net> — 2021-02-24T00:46:03Z
Sure. I will try that. On Tue, Feb 23, 2021 at 4:42 PM Adrian Klaver <adrian.klaver@aklaver.com> 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.klaver@aklaver.com >
-
Re: pg_restore - generated column - not populating
Tom Lane <tgl@sss.pgh.pa.us> — 2021-02-24T01:03:06Z
[ 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
-
Re: pg_restore - generated column - not populating
Santosh Udupi <email@hitha.net> — 2021-02-24T02:18:06Z
Hi Tom, To add to your finding: we compared between the postgresql pg_dump and the pgAdmin (win) pg_dump. The main table definition remains the same but the partition table versions differ [image: image.png] [image: image.png] On Tue, Feb 23, 2021 at 5:03 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > [ 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 > >
-
Re: pg_restore - generated column - not populating
Peter Eisentraut <peter.eisentraut@enterprisedb.com> — 2021-04-26T13:40:53Z
On 24.02.21 02:03, Tom Lane wrote: > [ 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? The backend side of this would be fixed by the proposed <https://www.postgresql.org/message-id/ac35da1c-e746-ea19-bfc3-84819a4e907d%40enterprisedb.com> (it's the same code for ALTER TABLE ... INHERIT and ATTACH PARTITION). The pg_dump side can apparently be fixed by adding diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c index 1a261a5545..c210883ca3 100644 --- a/src/bin/pg_dump/common.c +++ b/src/bin/pg_dump/common.c @@ -585,7 +585,7 @@ flagInhAttrs(DumpOptions *dopt, TableInfo *tblinfo, int numTables) } /* Remove generation expression from child */ - if (foundGenerated && !dopt->binary_upgrade) + if (foundGenerated && !dopt->binary_upgrade && !tbinfo->ispartition) tbinfo->attrdefs[j] = NULL; } } Looks like this was accidentally broken by the last minor release's fixes in this area. -
Re: pg_restore - generated column - not populating
Peter Eisentraut <peter.eisentraut@enterprisedb.com> — 2021-05-04T12:25:58Z
On 26.04.21 15:40, Peter Eisentraut wrote: >> 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? > > The backend side of this would be fixed by the proposed > <https://www.postgresql.org/message-id/ac35da1c-e746-ea19-bfc3-84819a4e907d%40enterprisedb.com> > (it's the same code for ALTER TABLE ... INHERIT and ATTACH PARTITION). > > The pg_dump side can apparently be fixed by adding > > diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c > index 1a261a5545..c210883ca3 100644 > --- a/src/bin/pg_dump/common.c > +++ b/src/bin/pg_dump/common.c > @@ -585,7 +585,7 @@ flagInhAttrs(DumpOptions *dopt, TableInfo *tblinfo, > int numTables) > } > > /* Remove generation expression from child */ > - if (foundGenerated && !dopt->binary_upgrade) > + if (foundGenerated && !dopt->binary_upgrade && > !tbinfo->ispartition) > tbinfo->attrdefs[j] = NULL; > } > } > > Looks like this was accidentally broken by the last minor release's > fixes in this area. Both of these issues have been fixed and will be in the next minor releases.
-
Re: pg_restore - generated column - not populating
Santosh Udupi <email@hitha.net> — 2021-05-04T15:08:13Z
Thank you so much!! On Tue, May 4, 2021 at 5:26 AM Peter Eisentraut < peter.eisentraut@enterprisedb.com> wrote: > On 26.04.21 15:40, Peter Eisentraut wrote: > >> 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? > > > > The backend side of this would be fixed by the proposed > > < > https://www.postgresql.org/message-id/ac35da1c-e746-ea19-bfc3-84819a4e907d%40enterprisedb.com> > > > (it's the same code for ALTER TABLE ... INHERIT and ATTACH PARTITION). > > > > The pg_dump side can apparently be fixed by adding > > > > diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c > > index 1a261a5545..c210883ca3 100644 > > --- a/src/bin/pg_dump/common.c > > +++ b/src/bin/pg_dump/common.c > > @@ -585,7 +585,7 @@ flagInhAttrs(DumpOptions *dopt, TableInfo *tblinfo, > > int numTables) > > } > > > > /* Remove generation expression from child */ > > - if (foundGenerated && !dopt->binary_upgrade) > > + if (foundGenerated && !dopt->binary_upgrade && > > !tbinfo->ispartition) > > tbinfo->attrdefs[j] = NULL; > > } > > } > > > > Looks like this was accidentally broken by the last minor release's > > fixes in this area. > > Both of these issues have been fixed and will be in the next minor > releases. >