BUG #14684: pg_dump omits columns in inherited tables / psql -d shows deleted columns

From: buschmann(at)nidsa(dot)net
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14684: pg_dump omits columns in inherited tables / psql -d shows deleted columns
Date: 2017-06-02 12:19:47
Message-ID: 20170602121947.1436.81319@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14684
Logged by: Hans Buschmann
Email address: buschmann(at)nidsa(dot)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_followup
add 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_new CHECK ((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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Mike Palmiotto 2017-06-02 14:24:39 Re: BUG #14682: row level security not work with partitioned table
Previous Message Ray Warren 2017-06-02 08:43:34 Re: BUG #14683: *** glibc detected *** SELECT: double free or corruption