Thread: [BUGS] BUG #14684: pg_dump omits columns in inherited tables / psql -d showsdeleted columns

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

buschmann@nidsa.net writes:
> 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 could not reproduce any such misbehavior from the details you gave.
Perhaps you could provide a self-contained example?  (Ie, starting
from scratch, create and alter some tables to arrive at a bogus state.)

Note that it's not necessarily wrong for a column deleted from the parent
to still exist in a child; if the child had a local definition to start
with then it would still have one afterwards.  For example,
create table p (f1 int, f2 int, f3 int);create table c (f2 int) inherits (p);-- you'll get some bleat about merging f2
withthe inherited columnalter table p drop column f2;alter table p drop column f3;-- c will now have f1 (inherited) and
f2(local), but not f3
 

But I can't think of a case where we should allow a column to exist
in a parent but not a child.

You might find it useful to examine pg_attribute.attislocal (does
column have a "local" definition?) and pg_attribute.attinhcount
(number of times column is inherited from parent table(s)) for the
seemingly-misbehaving columns.
        regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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

"Hans Buschmann" <buschmann@nidsa.net> writes:
> To better show the problem I wrote a little test case:

> 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);

OK, so this is wrong usage.  By declaring the columns in the parent and
then again in the child table, you have caused those columns to be
doubly defined: they are considered to have *both* inherited and local
definitions in the child.  Therefore, dropping the parent table's column
removes the inherited source but not the local source, and so they remain
in existence in the child.  The way to write the child table to get the
behavior you expect is more like

CREATE TABLE archiv.of_test_archiv (   PRIMARY KEY (id_of),   CONSTRAINT ck_of_old CHECK ((of_season < 22))
)
INHERITS (public.of_test);

You do need to redeclare the PRIMARY KEY constraint locally because that's
implicitly NO INHERIT.

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

New columns are always added at the end.

> The dump output shows (in parts):
> ...
> Here the table creation of of_test_archiv misses the newly added columns and still has the dropped column.

pg_dump is doing the correct thing to restore the actual state of the
child table, namely that the "added" columns have only inherited
definitions while the others have local definitions.

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

Yeah, the child table's column ordering may not be preserved in a sequence
like this, because after a dump/restore it will have all inherited columns
before all non-inherited ones, even if they were in some other order
before due to ALTER TABLE manipulations.  There's pretty much no way
around that given our current implementation, and some have argued that
it's a feature not a bug anyway.

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

No, it still was there, as your \d output showed.

BTW, I believe that ALTER TABLE NO INHERIT will result in all the
child columns acquiring a "local definition" flag, which means that
they'd subsequently not go away even if you reattach the child to the
parent and then drop column(s) in the parent.  You could argue for
NO INHERIT effectively doing a DROP COLUMN on columns with no local
definition, but it was agreed that that posed too much risk of
unintentional data loss.  So if you've been doing NO INHERIT/re-INHERIT
freely, it'd help explain why your columns aren't disappearing when you
drop them in the parent.
        regards, tom lane


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