On Wed, Aug 27, 2014 at 09:40:30PM -0400, Noah Misch wrote:
> > > 3. use the pg_dump binary-upgrade code when such cases happen
>
> +1. We have the convention that, while --binary-upgrade can inject catalog
> hacks, regular pg_dump uses standard, documented DDL. I like that convention
> on general aesthetic grounds and for its benefit to non-superusers. Let's
> introduce the DDL needed to fix this bug while preserving that convention,
> namely DDL to toggle attislocal.
I have spend some time researching this, and I am not sure what to
recommend. The basic issue is that CREATE TABLE INHERITS always puts
the inherited columns first, so to preserve column ordering, you have to
use CREATE TABLE and then ALTER TABLE INHERIT. The problem there is
that ALTER TABLE INHERIT doesn't preserve attislocal, and it also has
problems with constraints not being marked local. I am just not sure we
want to add SQL-level code to do that. Would it be documented?
I decided to step back and consider some issues. Basically, in
non-binary-upgrade mode, pg_dump is take:
CREATE TABLE A(a int, b int, c int);
CREATE TABLE B(a int, c int);
ALTER TABLE a INHERIT B;
and dumping it as:
CREATE TABLE a (
a integer,
b integer,
c integer
)
INHERITS (b);
This looks perfect, but, of course, it isn't. Columns c is going to be
placed before 'b'. You do get a notice about merged columns, but no
mention of the column reordering:
NOTICE: merging column "a" with inherited definition
NOTICE: merging column "c" with inherited definition
I think there are two common cases for CREATE TABLE INHERITS, and then
there is this odd one. The common cases are cases where all columns
inherited are mentioned explicitly in CREATE TABLE INHERITS, in order,
and the other case where none of the inherited columns are mentioned.
The case above is the odd case where some are mentioned, but in a
different order.
I have developed the attached patch to warn about column reordering in
this odd case. The patch mentions the reordering of c:
NOTICE: merging column "a" with inherited definition
NOTICE: merging column "c" with inherited definition; column moved earlier to match inherited column location
This, of course, will be emitted when the pg_dump output is restored.
This is probably the minimum we should do.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
