Per my comment at https://postgr.es/m/20190422225129.GA6126@alvherre.pgsql I think that pg_dump can possibly cause bogus partition definitions, when the users explicitly decide to join tables as partitions that have different column ordering than the parent table. Any COPY or INSERT command without an explicit column list that tries to put tuples in the table will fail after the restore.
Tom Lane said: > I haven't looked at the partitioning code, but I am quite sure that that's > always happened for old-style inheritance children, and I imagine pg_dump > is just duplicating that old behavior. Actually, the new code is unrelated to the old one; for legacy inheritance, the children are always created exactly as they were created at definition time. If you use ALTER TABLE ... INHERITS (attach a table as a children after creation) then obviously the child table cannot be modified to match its new parent; and pg_dump reproduces the exact column ordering that the table originally had. If you use "CREATE TABLE ... INHERITS (parent)" then the child columns are reordered *at that point* (creation time); the dump will, again, reproduce the exact same definition. I think failing to reproduce the exact same definition is a pg_dump bug that should be fixed and backpatched to pg10. It's just sheer luck that nobody has complained of being bitten by it. -- Álvaro Herrera http://www.twitter.com/alvherre
