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

"Hans Buschmann" <buschmann(at)nidsa(dot)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 changes in 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 part of 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