From: Bernhard Rohrer <graylion@sm-wg.net>
To: pgsql-admin@postgresql.org
Subject: Fwd: Re: PG_update to 9.0.4 in ubuntu lucid 64
Date: Thu, 03 Mar 2011 10:09:40 +0000
Message-ID: <4D6F68E4.6050503@sm-wg.net> (raw)
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<bruce@momjian.us> http://momjian.us
> EnterpriseDBhttp://enterprisedb.com
>
> + It's impossible for everything to be true. +
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: graylion@sm-wg.net
Subject: Re: Fwd: Re: PG_update to 9.0.4 in ubuntu lucid 64
In-Reply-To: <4D6F68E4.6050503@sm-wg.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox