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

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