[BUGS] BUG #14684: pg_dump omits columns in inherited tables / psql -d showsdeleted columns - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      14684
Logged by:          Hans Buschmann
Email address:      buschmann@nidsa.net
PostgreSQL version: 9.6.3
Operating system:   Windows x64
Description:

I use a partitioned table design with an active table (getting the current
data) and an archiv table.

During application development I added some columns to the existing active
table by 

alter table or_followupadd column of_id_off smallint
,add column of_id_for_group smallint
;

I also dropped a column by 

alter table or_followup
drop column of_style_cus
;

When I take a schema_only dump with pg_dump, the table creation looks like
(excerpt from dmp file)

CREATE TABLE or_followup (
...   of_p_deliver_etd date,   of_p_deliver_eta date,   of_id_off smallint,   of_id_for_group smallint,   CONSTRAINT
ck_of_newCHECK ((of_season >= 24)) NO INHERIT
 
);


CREATE TABLE or_followup_archiv (
...   of_p_deliver_etd date,   of_p_deliver_eta date,   of_style_cus character varying(35),   CONSTRAINT ck_of_old
CHECK((of_season < 24)) NO INHERIT
 
)
INHERITS (public.or_followup);

AS we might see, the inserted and the deleted columns (all after the date
columns) are not created in the (inheriting) archiv table!

Additionely I compared psql \d output: (excerpt from screen)

xxxdb=# \d public.or_followup                                         Table "public.or_followup"        Column
|        Type          |                          
Modifiers
------------------------+-----------------------+-------------------------------------------------------------
...of_p_deliver_etd       | date                  |of_p_deliver_eta       | date                  |of_id_off
 | smallint              |of_id_for_group        | smallint              |
 
Indexes:
...
Check constraints:   "ck_of_new" CHECK (of_season >= 24) NO INHERIT
Triggers:
...
Number of child tables: 1 (Use \d+ to list them.)


xxxdb=# \d or_followup_archiv;                                     Table "archiv.or_followup_archiv"        Column
  |         Type          |                          
Modifiers
------------------------+-----------------------+-------------------------------------------------------------
...of_p_deliver_etd       | date                  |of_p_deliver_eta       | date                  |of_id_off
 | smallint              |of_id_for_group        | smallint              |of_style_cus           | character
varying(35)|
 
Indexes:
...
Check constraints:   "ck_of_old" CHECK (of_season < 24) NO INHERIT
Inherits: or_followup

In psql \d the added columns are displayed in the inherited archiv table,
but the dropped column is still there!

It seems that the catalogs are screwed up when adding /deleting columns
inderectly for inherited tables. This was tested under PG 9.6.3 and 10beta1
under Windows x64.

I didn't check if other row altering statements are reflected correctly,
like adding/change defaults, types etc.

Surprisingly the full backup and restore via pg_dump and pg_restore to a
fresh system does not produce errors and runs flawlessly (tested often in
backup procedure)

Could anybody reproduce this behavior?

Thanks

Hans Buschmann




--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs