pg_restore - generated column - not populating

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

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:

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:

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:

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

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/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

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

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:

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

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:

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

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:

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

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: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

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