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

Thanks for the quick answer.

My point on this topic is consistency:

1. I expect the command add column xxx to a table hierarchy followed by a drop column xxx to the same hierarchy to be logical a null operation.
add columns adds the columns to all inherited tables but drop column removes the column only in the parent table.

2. I expect the \d command to reflect the current logical status of the table

3. I expect all tools to see the same logical status of the tables, e.g. psql \d and pg_dump

In my original post I forgot to mention that the example was on the test machine after pg_dump/pg_restor from the production server.
To better show the problem I wrote a little test case:

---------------------------------------------------------------------------------

-- create database xxxdb template=template0 encoding 'UTF8' lc_collate='C';
-- \c xxxdb
-- create schema archiv;

select version();

ALTER DATABASE xxxdb SET search_path TO public,archiv;

SET search_path TO public,archiv;

drop table if exists of_test_archiv;
drop table if exists of_test;

CREATE TABLE of_test (
id_of integer PRIMARY KEY,
of_season smallint,
of_p_deliver_etd date,
of_p_deliver_eta date,
of_style_cus character varying(35),
CONSTRAINT ck_of_new CHECK ((of_season >= 22)) NO INHERIT
);

CREATE TABLE archiv.of_test_archiv (
id_of integer PRIMARY KEY,
of_season smallint,
of_p_deliver_etd date,
of_p_deliver_eta date,
of_style_cus character varying(35),
CONSTRAINT ck_of_old CHECK ((of_season < 22))
)
INHERITS (public.of_test);

\d of_test

\d of_test_archiv

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

alter table of_test
drop column of_style_cus
;

\d of_test

\d of_test_archiv

-- pg_dump -U postgres -d xxxdb -f c:/xxxdb_struct.sql

----------------------------------------------------------------------------------

The log in psql:

version
-------------------------------------------------------------
PostgreSQL 9.6.3, compiled by Visual C++ build 1800, 64-bit
(1 row)

ALTER DATABASE
SET
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
Table "public.of_test"
Column | Type | Modifiers
------------------+-----------------------+-----------
id_of | integer | not null
of_season | smallint |
of_p_deliver_etd | date |
of_p_deliver_eta | date |
of_style_cus | character varying(35) |
Indexes:
"of_test_pkey" PRIMARY KEY, btree (id_of)
Check constraints:
"ck_of_new" CHECK (of_season >= 22) NO INHERIT
Number of child tables: 1 (Use \d+ to list them.)

Table "archiv.of_test_archiv"
Column | Type | Modifiers
------------------+-----------------------+-----------
id_of | integer | not null
of_season | smallint |
of_p_deliver_etd | date |
of_p_deliver_eta | date |
of_style_cus | character varying(35) |
Indexes:
"of_test_archiv_pkey" PRIMARY KEY, btree (id_of)
Check constraints:
"ck_of_old" CHECK (of_season < 22)
Inherits: of_test

ALTER TABLE
ALTER TABLE
Table "public.of_test"
Column | Type | Modifiers
------------------+----------+-----------
id_of | integer | not null
of_season | smallint |
of_p_deliver_etd | date |
of_p_deliver_eta | date |
of_id_off | smallint |
of_id_for_group | smallint |
Indexes:
"of_test_pkey" PRIMARY KEY, btree (id_of)
Check constraints:
"ck_of_new" CHECK (of_season >= 22) NO INHERIT
Number of child tables: 1 (Use \d+ to list them.)

Table "archiv.of_test_archiv"
Column | Type | Modifiers
------------------+-----------------------+-----------
id_of | integer | not null
of_season | smallint |
of_p_deliver_etd | date |
of_p_deliver_eta | date |
of_style_cus | character varying(35) |
of_id_off | smallint |
of_id_for_group | smallint |
Indexes:
"of_test_archiv_pkey" PRIMARY KEY, btree (id_of)
Check constraints:
"ck_of_old" CHECK (of_season < 22)
Inherits: of_test

---------------------------------------------------------------------------------------

Please note the ordering of the columns in of_test_archiv !!!

The dump output shows (in parts):

...

CREATE TABLE of_test (
id_of integer NOT NULL,
of_season smallint,
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 >= 22)) NO INHERIT
);

ALTER TABLE of_test OWNER TO postgres;

SET search_path = archiv, pg_catalog;

--
-- Name: of_test_archiv; Type: TABLE; Schema: archiv; Owner: postgres
--

CREATE TABLE of_test_archiv (
id_of integer,
of_season smallint,
of_p_deliver_etd date,
of_p_deliver_eta date,
of_style_cus character varying(35),
CONSTRAINT ck_of_old CHECK ((of_season < 22))
)
INHERITS (public.of_test);

ALTER TABLE of_test_archiv OWNER TO postgres;

...
-----------------------------------------------------------------------------------

Here the table creation of of_test_archiv misses the newly added columns and still has the dropped column.

When you later restore the dump on a different machine (e.g. as backup), the definition of the of_test_archiv table changes in psql \d:

xxxdb=# \d of_test_archiv
Table "archiv.of_test_archiv"
Column | Type | Modifiers
------------------+-----------------------+-----------
id_of | integer | not null
of_season | smallint |
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:
"of_test_archiv_pkey" PRIMARY KEY, btree (id_of)
Check constraints:
"ck_of_old" CHECK (of_season < 22)
Inherits: of_test

xxxdb=# alter table of_test_archiv no inherit of_test;
ALTER TABLE
xxxdb=# \d of_test_archiv
Table "archiv.of_test_archiv"
Column | Type | Modifiers
------------------+-----------------------+-----------
id_of | integer | not null
of_season | smallint |
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:
"of_test_archiv_pkey" PRIMARY KEY, btree (id_of)
Check constraints:
"ck_of_old" CHECK (of_season < 22)

Now table of_test_archiv has of_style_cus as a column, even when in the original database this column was not more part of the table definition (see above)

The dump/restore operation is no longer logical invariant to the table structure.

Because I have many of these inherited tables and I am still in the process of restructuring the data, about a year ago I got an error of restoring the production database to the dev machine bcause of inheritence error (I cant remember exactly and could not nail it down at the time: it seemed to be a type error because of column ordering).

So it is possible that you get more serious data problems by changing columns in inherited tables.

I would expect that pg_dump recognizes the same table struct as psql \d at any time.

BTW: the bloat output (e.g. NOTICE: merging column "id_of" with inherited definition) appears in psql window even when output is redirected with \o