OpenERP table cannot be upgraded with pg_upgrade

[graylion]

I tried upgrading my postgres from 8.4 to 9.0 which lead to the following error:

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

looking at the dump file I see the following:

-- 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;

the full discussion of this issue can be found in several threads on the PGAdmin mailing list here: http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=user_nodes&user=34980

I am not going to attach the dump since this is public and it contains other data from other databases. Please email me if you require the dump.

The analysis from a developer is:

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.

so somehow OpenERP is creating this, making pg_dump fail, which I would consider an OpenERP bug. Also PGsql is allowing this to happen, which is probably also a PGSql bug. I am sending this report to the postgres developer I am working with on this.

[graylion (graylion)]

oh and I am using OpenERP 6.0.2

[xrg (xrg)]

On Saturday 14 May 2011, you wrote:
> You have been subscribed to a public bug:
>
> I tried upgrading my postgres from 8.4 to 9.0 which lead to the
> following error:
>
> 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
>

I have already filed a bug in postgres, a few days ago, see #6024

We are doing (in postgres) sth. that the specification didn't expect us, but
allowed us, to do: drop the constraint from an inherited table. Since the
inheritance is a feature only used in ir.actions, we are unlikely to modify
our ORM to prevent that.

Of course, what we can do, is to fix the orm models not to have null names :)
But that may not fix all existing dbs (to be investigated further).

[xrg (xrg)]

Found a link:
http://postgresql.1045698.n5.nabble.com/BUG-6024-pg-dump-won-t-dump-ALTERed-
inherited-fields-td4390166.html

[Antony Lesuisse (OpenERP) (al-openerp)]

This seems to be a postgresql bug if pg_dump cannot dump pg database.

[Stuart Bishop (stub)]

I just tripped over this. Reading the link in #3, upstream says that the PostgreSQL bug is that it is allowing OpenERP to drop a constraint that it should not be allowed to drop. This means that this is also an OpenERP issue, because fixing this PostgreSQL bug will break OpenERP which relies on it.

[Stuart Bishop (stub)]

Using pg_dump/pg_restore to do the upgrade appears to work fine. However, the NOT NULL constraint is silently put back onto the child table.

The PostgreSQL docs explicitly state "neither can columns or check constraints of child tables be dropped or altered if they are inherited from any parent tables". pg_dump seems to be handling fixing the schema, but pg_upgrade has problems.

Does OpenERP still require the NOT NULL constraint to be removed from the child tables?

[Andrew (aes)]

This is not postgresql error. openERP has a defect in its schema that is allowed to slip through because of a bug in postgres. Once that bug is fixed, that will cause openERP more problems.

Discussion here:
http://postgresql.1045698.n5.nabble.com/BUG-6024-pg-dump-won-t-dump-ALTERed-inherited-fields-td4390166.html

This isn't really a pg_dump deficiency. The bug is that we let you do
that ALTER. Inherited constraints shouldn't be droppable, and indeed
are not droppable except in the single case of NOT NULL. This is on the
to-fix list --- in fact there was a patch submitted for it last year,
although it got returned for rework and we've not seen it again yet.

The openERP schema should be fixed.

[Yann Papouin (yann-papouin)]

Just running into it when upgrading Ubuntu from 12.04 (postgresql 9.1) to 14.04 (postgresql 9.3).

A fix, at least when creating tables, must be proposed since postgresql forbid the restoration of a database if this schema error is there.

Maybe it could be an assertion when parsing the model that specified that required is necessary at this place.