On Tue, Sep 15, 2015 at 7:19 AM, Jim Nasby wrote:
> On 9/14/15 11:59 AM, David G. Johnston wrote:
>>
>> On Mon, Sep 14, 2015 at 11:11 AM, Ingmar Brouns wrote:
>> However, when I dump the schema using pg_dump, and then load the
>> resulting sql file, suddenly 'c' does follow 'a'. So restoring my
>> schema has changed my table's layout. I feel dumping and loading
>> should not alter column positions. Any thoughts?
When using pg_dump --inserts, meaning that the column names are not
dumped in the INSERT query used, this would cause a problem. In other
cases, aka the use of COPY or --column-inserts the dump will be nicely
consistent. It is true that we have a problem though as pg_dump relies
on attnum to order its columns in its logic.
> To elaborate... without looking at the code I'm pretty sure what's happening
> here is that pg_dump simply dumps the entire parent table, including the
> added column. In fact, it must be doing this because we don't have any way
> to track when a column is added after table creation.
>
> pg_dump then spits out CREATE TABLE child(...) INHERITS(parent), and the
> database correctly puts all the parent fields first in the child.
Yep. Taking the problem the other way around, we could fix the backend
such as the column ordering is not messed up on the child table when
the new column is added on the parent instead of putting the blame in
pg_dump. FWIW, that's something I am investigating on this thread of
-hackers:
http://www.postgresql.org/message-id/20150926132237.GJ5702@alap3.anarazel.de
--
Michael
