pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_restore - generated column - not populating
26+ messages / 4 participants
[nested] [flat]
* pg_restore - generated column - not populating
@ 2021-02-23 01:08 Santosh Udupi <email@hitha.net>
0 siblings, 1 reply; 26+ messages in thread
From: Santosh Udupi @ 2021-02-23 01:08 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore - generated column - not populating
@ 2021-02-23 01:16 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Santosh Udupi <email@hitha.net>
0 siblings, 1 reply; 26+ messages in thread
From: Adrian Klaver @ 2021-02-23 01:16 UTC (permalink / raw)
To: Santosh Udupi <email@hitha.net>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore - generated column - not populating
@ 2021-02-23 01:57 Santosh Udupi <email@hitha.net>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 26+ messages in thread
From: Santosh Udupi @ 2021-02-23 01:57 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore - generated column - not populating
@ 2021-02-23 02:20 Santosh Udupi <email@hitha.net>
parent: Santosh Udupi <email@hitha.net>
0 siblings, 1 reply; 26+ messages in thread
From: Santosh Udupi @ 2021-02-23 02:20 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org
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.h...
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
>>
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore - generated column - not populating
@ 2021-02-23 03:43 Santosh Udupi <email@hitha.net>
parent: Santosh Udupi <email@hitha.net>
0 siblings, 1 reply; 26+ messages in thread
From: Santosh Udupi @ 2021-02-23 03:43 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org
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.h...
>
>
> 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
>>>
>>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore - generated column - not populating
@ 2021-02-23 05:23 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Santosh Udupi <email@hitha.net>
0 siblings, 1 reply; 26+ messages in thread
From: Adrian Klaver @ 2021-02-23 05:23 UTC (permalink / raw)
To: Santosh Udupi <email@hitha.net>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore - generated column - not populating
@ 2021-02-23 14:36 Santosh Udupi <email@hitha.net>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 26+ messages in thread
From: Santosh Udupi @ 2021-02-23 14:36 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore - generated column - not populating
@ 2021-02-23 15:26 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Santosh Udupi <email@hitha.net>
0 siblings, 1 reply; 26+ messages in thread
From: Adrian Klaver @ 2021-02-23 15:26 UTC (permalink / raw)
To: Santosh Udupi <email@hitha.net>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore - generated column - not populating
@ 2021-02-23 15:39 Santosh Udupi <email@hitha.net>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 26+ messages in thread
From: Santosh Udupi @ 2021-02-23 15:39 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore - generated column - not populating
@ 2021-02-23 15:50 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Santosh Udupi <email@hitha.net>
0 siblings, 1 reply; 26+ messages in thread
From: Adrian Klaver @ 2021-02-23 15:50 UTC (permalink / raw)
To: Santosh Udupi <email@hitha.net>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore - generated column - not populating
@ 2021-02-23 17:18 Santosh Udupi <email@hitha.net>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 26+ messages in thread
From: Santosh Udupi @ 2021-02-23 17:18 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore - generated column - not populating
@ 2021-02-23 17:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Santosh Udupi <email@hitha.net>
0 siblings, 1 reply; 26+ messages in thread
From: Tom Lane @ 2021-02-23 17:34 UTC (permalink / raw)
To: Santosh Udupi <email@hitha.net>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore - generated column - not populating
@ 2021-02-23 18:18 Santosh Udupi <email@hitha.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 26+ messages in thread
From: Santosh Udupi @ 2021-02-23 18:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore - generated column - not populating
@ 2021-02-23 18:40 Tom Lane <tgl@sss.pgh.pa.us>
parent: Santosh Udupi <email@hitha.net>
0 siblings, 1 reply; 26+ messages in thread
From: Tom Lane @ 2021-02-23 18:40 UTC (permalink / raw)
To: Santosh Udupi <email@hitha.net>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore - generated column - not populating
@ 2021-02-23 20:15 Santosh Udupi <email@hitha.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 26+ messages in thread
From: Santosh Udupi @ 2021-02-23 20:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore - generated column - not populating
@ 2021-02-23 20:20 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Santosh Udupi <email@hitha.net>
1 sibling, 1 reply; 26+ messages in thread
From: Adrian Klaver @ 2021-02-23 20:20 UTC (permalink / raw)
To: Santosh Udupi <email@hitha.net>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore - generated column - not populating
@ 2021-02-23 20:57 Santosh Udupi <email@hitha.net>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 26+ messages in thread
From: Santosh Udupi @ 2021-02-23 20:57 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore - generated column - not populating
@ 2021-02-23 23:21 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Santosh Udupi <email@hitha.net>
0 siblings, 1 reply; 26+ messages in thread
From: Adrian Klaver @ 2021-02-23 23:21 UTC (permalink / raw)
To: Santosh Udupi <email@hitha.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore - generated column - not populating
@ 2021-02-24 00:25 Santosh Udupi <email@hitha.net>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 26+ messages in thread
From: Santosh Udupi @ 2021-02-24 00:25 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore - generated column - not populating
@ 2021-02-24 00:42 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Santosh Udupi <email@hitha.net>
0 siblings, 1 reply; 26+ messages in thread
From: Adrian Klaver @ 2021-02-24 00:42 UTC (permalink / raw)
To: Santosh Udupi <email@hitha.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore - generated column - not populating
@ 2021-02-24 00:46 Santosh Udupi <email@hitha.net>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 0 replies; 26+ messages in thread
From: Santosh Udupi @ 2021-02-24 00:46 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore - generated column - not populating
@ 2021-02-24 01:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Santosh Udupi <email@hitha.net>
1 sibling, 2 replies; 26+ messages in thread
From: Tom Lane @ 2021-02-24 01:03 UTC (permalink / raw)
To: Santosh Udupi <email@hitha.net>; +Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-bugs@lists.postgresql.org
[ 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
Attachments:
partition_generated.sql (text/plain, 2.0K) download
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');
insert into tbl_main (info, created_on) values
('{"vp": 42, "o": 1000, "qr": "foo"}', '2023-06-01'),
('{"vp": 43, "o": 1001, "qr": "bar"}', '2013-06-01');
* Re: pg_restore - generated column - not populating
@ 2021-02-24 02:18 Santosh Udupi <email@hitha.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 26+ messages in thread
From: Santosh Udupi @ 2021-02-24 02:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-bugs@lists.postgresql.org
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
>
>
Attachments:
image.png (image/png, 62.9K) download
view image
image.png (image/png, 84.6K) download
view image
* Re: pg_restore - generated column - not populating
@ 2021-04-26 13:40 Peter Eisentraut <peter.eisentraut@enterprisedb.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 26+ messages in thread
From: Peter Eisentraut @ 2021-04-26 13:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Santosh Udupi <email@hitha.net>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-bugs@lists.postgresql.org
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
inline patch 1: src/bin/pg_dump/common.c (+0/-0 lines) download inline-1.patch
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,
* Re: pg_restore - generated column - not populating
@ 2021-05-04 12:25 Peter Eisentraut <peter.eisentraut@enterprisedb.com>
parent: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
0 siblings, 1 reply; 26+ messages in thread
From: Peter Eisentraut @ 2021-05-04 12:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Santosh Udupi <email@hitha.net>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore - generated column - not populating
@ 2021-05-04 15:08 Santosh Udupi <email@hitha.net>
parent: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
0 siblings, 0 replies; 26+ messages in thread
From: Santosh Udupi @ 2021-05-04 15:08 UTC (permalink / raw)
To: Peter Eisentraut <peter.eisentraut@enterprisedb.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-bugs@lists.postgresql.org
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.
>
^ permalink raw reply [nested|flat] 26+ messages in thread
end of thread, other threads:[~2021-05-04 15:08 UTC | newest]
Thread overview: 26+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-02-23 01:08 pg_restore - generated column - not populating Santosh Udupi <email@hitha.net>
2021-02-23 01:16 ` Adrian Klaver <adrian.klaver@aklaver.com>
2021-02-23 01:57 ` Santosh Udupi <email@hitha.net>
2021-02-23 02:20 ` Santosh Udupi <email@hitha.net>
2021-02-23 03:43 ` Santosh Udupi <email@hitha.net>
2021-02-23 05:23 ` Adrian Klaver <adrian.klaver@aklaver.com>
2021-02-23 14:36 ` Santosh Udupi <email@hitha.net>
2021-02-23 15:26 ` Adrian Klaver <adrian.klaver@aklaver.com>
2021-02-23 15:39 ` Santosh Udupi <email@hitha.net>
2021-02-23 15:50 ` Adrian Klaver <adrian.klaver@aklaver.com>
2021-02-23 17:18 ` Santosh Udupi <email@hitha.net>
2021-02-23 17:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-02-23 18:18 ` Santosh Udupi <email@hitha.net>
2021-02-23 18:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-02-23 20:15 ` Santosh Udupi <email@hitha.net>
2021-02-23 20:20 ` Adrian Klaver <adrian.klaver@aklaver.com>
2021-02-23 20:57 ` Santosh Udupi <email@hitha.net>
2021-02-23 23:21 ` Adrian Klaver <adrian.klaver@aklaver.com>
2021-02-24 00:25 ` Santosh Udupi <email@hitha.net>
2021-02-24 00:42 ` Adrian Klaver <adrian.klaver@aklaver.com>
2021-02-24 00:46 ` Santosh Udupi <email@hitha.net>
2021-02-24 01:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-02-24 02:18 ` Santosh Udupi <email@hitha.net>
2021-04-26 13:40 ` Peter Eisentraut <peter.eisentraut@enterprisedb.com>
2021-05-04 12:25 ` Peter Eisentraut <peter.eisentraut@enterprisedb.com>
2021-05-04 15:08 ` Santosh Udupi <email@hitha.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox