Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id E92BD1337BA4 for ; Thu, 3 Mar 2011 06:09:48 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 78322-04 for ; Thu, 3 Mar 2011 10:09:41 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from collab.sm-wg.net (collab.sm-wg.net [86.43.106.231]) by mail.postgresql.org (Postfix) with ESMTP id AC5F01337B43 for ; Thu, 3 Mar 2011 06:09:37 -0400 (AST) Received: from [172.16.2.1] (unknown [172.16.2.1]) by collab.sm-wg.net (Postfix) with ESMTPSA id D3C1F2E7AEC3 for ; Thu, 3 Mar 2011 10:09:48 +0000 (GMT) Message-ID: <4D6F68E4.6050503@sm-wg.net> Date: Thu, 03 Mar 2011 10:09:40 +0000 From: Bernhard Rohrer User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.13) Gecko/20101208 Lightning/1.0b2 Thunderbird/3.1.7 MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Fwd: Re: PG_update to 9.0.4 in ubuntu lucid 64 Content-Type: multipart/alternative; boundary="------------040503010709020004080204" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201103/29 X-Sequence-Number: 36901 This is a multi-part message in MIME format. --------------040503010709020004080204 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit OK, here goes: -- For binary upgrade, must preserve relfilenodes SELECT binary_upgrade.set_next_heap_relfilenode('88788'::pg_catalog.oid); SELECT binary_upgrade.set_next_toast_relfilenode('88795'::pg_catalog.oid); SELECT binary_upgrade.set_next_index_relfilenode('88797'::pg_catalog.oid); CREATE TABLE ir_act_url ( id integer DEFAULT nextval('ir_actions_id_seq'::regclass) NOT NULL, name character varying(64) DEFAULT ''::character varying, type character varying(32) DEFAULT 'window'::character varying NOT NULL, usage character varying(32) DEFAULT NULL::character varying, url text NOT NULL, target character varying(64) NOT NULL, create_uid integer, create_date timestamp without time zone, write_date timestamp without time zone, write_uid integer ); -- For binary upgrade, recreate inherited column. UPDATE pg_catalog.pg_attribute SET attislocal = false WHERE attname = 'id' AND attrelid = 'ir_act_url'::pg_catalog.regclass; -- For binary upgrade, recreate inherited column. UPDATE pg_catalog.pg_attribute SET attislocal = false WHERE attname = 'name' AND attrelid = 'ir_act_url'::pg_catalog.regclass; -- For binary upgrade, recreate inherited column. UPDATE pg_catalog.pg_attribute SET attislocal = false WHERE attname = 'type' AND attrelid = 'ir_act_url'::pg_catalog.regclass; -- For binary upgrade, recreate inherited column. UPDATE pg_catalog.pg_attribute SET attislocal = false WHERE attname = 'usage' AND attrelid = 'ir_act_url'::pg_catalog.regclass; -- For binary upgrade, recreate inherited column. UPDATE pg_catalog.pg_attribute SET attislocal = false WHERE attname = 'create_uid' AND attrelid = 'ir_act_url'::pg_catalog.regclass; -- For binary upgrade, recreate inherited column. UPDATE pg_catalog.pg_attribute SET attislocal = false WHERE attname = 'create_date' AND attrelid = 'ir_act_url'::pg_catalog.regclass; -- For binary upgrade, recreate inherited column. UPDATE pg_catalog.pg_attribute SET attislocal = false WHERE attname = 'write_date' AND attrelid = 'ir_act_url'::pg_catalog.regclass; -- For binary upgrade, recreate inherited column. UPDATE pg_catalog.pg_attribute SET attislocal = false WHERE attname = 'write_uid' AND attrelid = 'ir_act_url'::pg_catalog.regclass; -- For binary upgrade, set up inheritance this way. *24606: ALTER TABLE ONLY ir_act_url INHERIT ir_actions;* so it comes from the catalog! Thanks Bernhard On 24/02/11 15:10, Bruce Momjian wrote: > 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 >>> EnterpriseDBhttp://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 http://momjian.us > EnterpriseDBhttp://enterprisedb.com > > + It's impossible for everything to be true. + --------------040503010709020004080204 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
OK, here goes:

-- For binary upgrade, must preserve relfilenodes
SELECT binary_upgrade.set_next_heap_relfilenode('88788'::pg_catalog.oid);
SELECT binary_upgrade.set_next_toast_relfilenode('88795'::pg_catalog.oid);
SELECT binary_upgrade.set_next_index_relfilenode('88797'::pg_catalog.oid);

CREATE TABLE ir_act_url (
    id integer DEFAULT nextval('ir_actions_id_seq'::regclass) NOT NULL,
    name character varying(64) DEFAULT ''::character varying,
    type character varying(32) DEFAULT 'window'::character varying NOT NULL,
    usage character varying(32) DEFAULT NULL::character varying,
    url text NOT NULL,
    target character varying(64) NOT NULL,
    create_uid integer,
    create_date timestamp without time zone,
    write_date timestamp without time zone,
    write_uid integer
);

-- For binary upgrade, recreate inherited column.
UPDATE pg_catalog.pg_attribute
SET attislocal = false
WHERE attname = 'id'
  AND attrelid = 'ir_act_url'::pg_catalog.regclass;

-- For binary upgrade, recreate inherited column.
UPDATE pg_catalog.pg_attribute
SET attislocal = false
WHERE attname = 'name'
  AND attrelid = 'ir_act_url'::pg_catalog.regclass;

-- For binary upgrade, recreate inherited column.
UPDATE pg_catalog.pg_attribute
SET attislocal = false
WHERE attname = 'type'
  AND attrelid = 'ir_act_url'::pg_catalog.regclass;

-- For binary upgrade, recreate inherited column.
UPDATE pg_catalog.pg_attribute
SET attislocal = false
WHERE attname = 'usage'
  AND attrelid = 'ir_act_url'::pg_catalog.regclass;

-- For binary upgrade, recreate inherited column.
UPDATE pg_catalog.pg_attribute
SET attislocal = false
WHERE attname = 'create_uid'
  AND attrelid = 'ir_act_url'::pg_catalog.regclass;

-- For binary upgrade, recreate inherited column.
UPDATE pg_catalog.pg_attribute
SET attislocal = false
WHERE attname = 'create_date'
  AND attrelid = 'ir_act_url'::pg_catalog.regclass;

-- For binary upgrade, recreate inherited column.
UPDATE pg_catalog.pg_attribute
SET attislocal = false
WHERE attname = 'write_date'
  AND attrelid = 'ir_act_url'::pg_catalog.regclass;

-- For binary upgrade, recreate inherited column.
UPDATE pg_catalog.pg_attribute
SET attislocal = false
WHERE attname = 'write_uid'
  AND attrelid = 'ir_act_url'::pg_catalog.regclass;

-- For binary upgrade, set up inheritance this way.
24606: ALTER TABLE ONLY ir_act_url INHERIT ir_actions;

so it comes from the catalog!

Thanks

Bernhard

On 24/02/11 15:10, Bruce Momjian wrote: 
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. +

--------------040503010709020004080204--