pg_restore - generated column - not populating

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
21 messages Options
12
Reply | Threaded
Open this post in threaded view
|

pg_restore - generated column - not populating

Santosh Udupi
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

Reply | Threaded
Open this post in threaded view
|

Re: pg_restore - generated column - not populating

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: pg_restore - generated column - not populating

Santosh Udupi
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 <[hidden email]> 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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: pg_restore - generated column - not populating

Santosh Udupi
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 <[hidden email]> 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 <[hidden email]> 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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: pg_restore - generated column - not populating

Santosh Udupi
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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: pg_restore - generated column - not populating

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: pg_restore - generated column - not populating

Santosh Udupi
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 <[hidden email]> 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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: pg_restore - generated column - not populating

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: pg_restore - generated column - not populating

Santosh Udupi
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 <[hidden email]> 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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: pg_restore - generated column - not populating

Adrian Klaver-4
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 <[hidden email]
> <mailto:[hidden email]>> 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
>     [hidden email] <mailto:[hidden email]>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: pg_restore - generated column - not populating

Santosh Udupi
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 <[hidden email]> 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 <[hidden email]
> <mailto:[hidden email]>> 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
>     [hidden email] <mailto:[hidden email]>
>


--
Adrian Klaver
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: pg_restore - generated column - not populating

Tom Lane-2
Santosh Udupi <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: pg_restore - generated column - not populating

Santosh Udupi
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 <[hidden email]> wrote:
Santosh Udupi <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: pg_restore - generated column - not populating

Tom Lane-2
Santosh Udupi <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: pg_restore - generated column - not populating

Santosh Udupi
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 <[hidden email]> wrote:
Santosh Udupi <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: pg_restore - generated column - not populating

Adrian Klaver-4
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 <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>     Santosh Udupi <[hidden email] <mailto:[hidden email]>> 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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: pg_restore - generated column - not populating

Santosh Udupi
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 <[hidden email]> 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 <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>     Santosh Udupi <[hidden email] <mailto:[hidden email]>> 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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: pg_restore - generated column - not populating

Adrian Klaver-4
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
> <[hidden email] <mailto:[hidden email]>> 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 <[hidden email]
>     <mailto:[hidden email]>
>      > <mailto:[hidden email] <mailto:[hidden email]>>> wrote:
>      >
>      >     Santosh Udupi <[hidden email] <mailto:[hidden email]>
>     <mailto:[hidden email] <mailto:[hidden email]>>> 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
>     [hidden email] <mailto:[hidden email]>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: pg_restore - generated column - not populating

Santosh Udupi
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 <[hidden email]> 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
> <[hidden email] <mailto:[hidden email]>> 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 <[hidden email]
>     <mailto:[hidden email]>
>      > <mailto:[hidden email] <mailto:[hidden email]>>> wrote:
>      >
>      >     Santosh Udupi <[hidden email] <mailto:[hidden email]>
>     <mailto:[hidden email] <mailto:[hidden email]>>> 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
>     [hidden email] <mailto:[hidden email]>
>


--
Adrian Klaver
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: pg_restore - generated column - not populating

Adrian Klaver-4
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
[hidden email]


12