On 9/14/15 11:59 AM, David G. Johnston wrote:
On Mon, Sep 14, 2015 at 11:11 AM, Ingmar Brouns <swingi@xxxxxxxxx <mailto:swingi@xxxxxxxxx>>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? Any or all of the following: 1) Help the community implement the outstanding concepts surrounding the separation and recording of separate data for physical and logical column order. 2) Suggest, and/or implement, ways that the current behavior could be more readily discovered and comprehended by users without having to discover it by accident. 3) Understand the problem and mitigate its impact in your specific work.
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.
I'm pretty certain that nothing here violates relational theory. It's another example of why SELECT * is a bad idea. Hence why you should do #3. (I've thought about adding a "chaos" setting where all row results get ordered by random(). That wouldn't help this case until we get #1 though.)
There's basically 0 chance of this being changed until #1 is done. At that point I'd expect pg_dump to start working correctly here, but it's also possible that adding a field to a parent would no longer go to the end of the children.
#2 could be as simple as a change to the documentation. Patches (or even just re-written text) welcome.
-- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
