openerp-india team mailing list archive

[Stuart Bishop <782688@xxxxxxxxxxxxxxxxxx>]

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?

-- 
You received this bug notification because you are a member of OpenERP
Indian Team, which is subscribed to OpenERP Server.
https://bugs.launchpad.net/bugs/782688

Title:
  OpenERP table cannot be upgraded with pg_upgrade

Status in OpenERP Server:
  Invalid

Bug description:
  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.

To manage notifications about this bug go to:
https://bugs.launchpad.net/openobject-server/+bug/782688/+subscriptions