From: Bruce Momjian <bruce@momjian.us>
Message-Id: <201102241510.p1OFAMn29296@momjian.us>
Subject: Re: PG_update to 9.0.4 in ubuntu lucid 64
In-Reply-To: <0b7f3d7bf2db6c664ff89ce47b13ab2c@collab.sm-wg.net>
To: Bernhard Rohrer <graylion@sm-wg.net>
Date: Thu, 24 Feb 2011 10:10:22 -0500 (EST)
CC: Bernhard Schrader <bernhard.schrader@innogames.de>,
	pgsql-admin@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="US-ASCII"

Bernhard Rohrer wrote:
> 
>  thanks guys
> 
> It'll be a few days before i can do that, but will come back and report.
> Can i use the dump file that pg_upgrade produced? it is there after
> all.

Yes, you can use the dump file pg_upgrade creates to see the table name
producing the error.  Also, a --schema-only pg_dump is a small file, or
should be.

---------------------------------------------------------------------------


> 
> thanks
> 
> B ----------------original message----------------- From: "Bruce
> Momjian" To: "Bruce Momjian" CC: "Bernhard Rohrer" graylion@sm-wg.net
> , "Bernhard Schrader" bernhard.schrader@innogames.de ,
> pgsql-admin@postgresql.org Date: Wed, 23 Feb 2011 18:37:40 -0500 (EST)
> -------------------------------------------------
> 
> 
> > Bruce Momjian wrote:
> >> Bernhard Rohrer wrote:
> >> > Thanks that worked. :)
> >> >
> >> > After this and some more entertainment we are now here:
> >> >
> >> > Restoring database schema to new cluster
> >> > psql:/usr/lib/postgresql/9.0/bin/pg_upgrade_dump_db.sql:24606:
> >> ERROR:
> >> > column "name" in child table must be marked NOT NULL
> >> >
> >> >
> >> > There were problems executing "/usr/lib/postgresql/9.0/bin/psql"
> >> --set
> >> > ON_ERROR_STOP=on --no-psqlrc --port 5432 --username "postgres" -f
> >> > "/usr/lib/postgresql/9.0/bin/pg_upgrade_dump_db.sql" --dbname
> >> template1
> >> > >> "/dev/null"
> >> >
> >> >
> >> > does that mean line24606? it looks like manual edititng required ...
> >>
> >> I checked the source code and the check it is failing on has this comment:
> >>
> >> /*
> >> * Check columns in child table match up with columns in parent, and increment
> >> * their attinhcount.
> >> *
> >> * Called by ATExecAddInherit
> >> *
> >> * Currently all parent columns must be found in child. Missing columns are an
> >> * error. One day we might consider creating new columns like CREATE TABLE
> >> * does. However, that is widely unpopular --- in the common use case of
> >> * partitioned tables it's a foot-gun.
> >> *
> >> * The data type must match exactly. If the parent column is NOT NULL then
> >> * the child must be as well. Defaults are not compared, however.
> >> */
> >> MergeAttributesIntoExisting()
> >>
> >> It seems somehow your schema is corrupt --- it is pg_dump that is
> >> failing, and threfore pg_upgrade. We need to find out how you got into
> >> that state. Do a manual pg_dump and see what table is being referenced
> >> on line 24606. It is saying that that table has a 'name' column that is
> >> not marked NOT NULL, while the parent table does have a NOT NULL
> >> specification. Those should match. I don't remember hearing about a
> >> bug in that area of the code.
> >
> > FYI, you can easily reproduce the failure by trying to restore a pg_dump
> > --schema dump into an empty database.
> >
> > --
> > Bruce Momjian
>  http://momjian.us
> > EnterpriseDB http://enterprisedb.com
> >
> > + It's impossible for everything to be true. +
> >
> 
> -- ------------- Bernhard Rohrer Consulting 529 Howth Road Dublin 5,
> Ireland
> 
> +353 87 7907 134

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +