Unrestorable dumps due to alter inherited column constraints

[Jens Wilke]

OpenERP Server 5.0.5

We ran into this problem: http://archives.postgresql.org/pgsql-hackers/2009-11/msg00146.php
We had dumps that were not restorable, because the column name in ir_actions has a not null constraint but the inherited column name in ir_act_window hasn't got this constraint.
This seems to be caused by an alter table statement by openerp which drops that constraint from ir_act_window.
Alter on inherited columns is likely to be removed in future versions of postgres.
This workflow needs to be removed from openerp to guarantee proper backups and to be compatible with future versions of postgres.

[Jens Wilke (jens-wilke)]

Also Affects 5.0.6

Are there any news to this report?

[hobbes (hobbes-poukram)]

I have had some discussions on irc and learnt some postgres in the process. It seems to be a pg bug that is trigered by an openerp bug. I have been able to restore my DB with these steps :

* first restore only the schema with the -s option to pg_restore
* then modify the column "name" of table "ir_act_window" and drop the "not null" constraint
* then restore the data only, while disabling triggers (this requires using the postgres super user) with these options : -a -S postgres --disable-triggers

With these steps I have no errors when restoring, and my DB is usable again

[hobbes (hobbes-poukram)]

anyway openerp needs to be fixed soon since the possibility to drop the inherited "not null" constraint will be removed :

http://thread.gmane.org/gmane.comp.db.postgresql.devel.general/127567

[Jens Wilke (jens-wilke)]

It's possible to restore the dump just in one step, when you delete the constraint from the dump.

[hobbes (hobbes-poukram)]

Then you impact ir_actions and all inheriting tables. I don't know if this is sane and would prefer to only impact ir_act_window.

It should be possible to add the "drop not null" on ir_act_window.name, but I don't know the format of the dump and have been unable to do it.

[Jens Wilke (jens-wilke)]

It's not the same.
You will only obtain the original state of the DB with your description.

[hobbes (hobbes-poukram)]

Yep that's all I want :-)

I don't know why there is a "not null" constraint on one table and not on the inheriting one. But as long as it is like that in a default setup, I just want to get it back when I restore my DB.

[Maximilian Gaß (mxey)]

There is a menu entry in account/project/project_view.xml with an empty name. The solution is either to assign a name to this entry/get rid of it or to remove required from ir_actions.name.

[Richard (John) Nopkins@Servosoft (richard-nopkins)]

Yes, absolutely true.

some coders have not assigned any name to some actions/xml records.
and this is a real crime . victims are the users.

to openerp teams,
Make sure evryfield is given pure respect.

[Anup(SerpentCS) (anup-serpent)]

Yes, we agree to this point.
It has been fixed by putting the missing names into actions of menuitems.
However, we will keep an eye on this kind of flaws.
Thanks.

[Jens Wilke (jens-wilke)]

The real crime is to change the database scheme on the fly.
Isn't it? This seems to be a deeper design problem.

[Maximilian Gaß (mxey)]

Putting the missing name into the actions fixes the immediate problem, but not the real one. The model should be fixed to set required=True for this field.

Also, the ORM should not drop constraints on inherited fields.

[Cloves Almeida (cjalmeida)]

That's why, if you really care about your data, always do a manual schema update. Schemas are too important to be changed automagically. Oh, and incremental backups...

[maddus (maddusito)]

Hi there,

I am effected by this mistake. Someone could tell me what to do exactly to get rid of this mistake? I tryed the method of hobbes (* first restore only the schema with the -s option to pg_restore
* then modify the column "name" of table "ir_act_window" and drop the "not null" constraint
* then restore the data only, while disabling triggers (this requires using the postgres super user) with these options : -a -S postgres --disable-triggers) but it didn't work.

After this I changed in account/project/project_view.xml

<field name="name"></field>
to
<field name="name">test</field>

but also no effect. It has to be a special name? Some other ideas?

Thanks
maddus

[Jay Vora (Serpent Consulting Services) (jayvora)]

Hello Maddus,

This problem has already been solved.

Your issue is that your backup contains this null value(name is null for the action) and you are trying to restore that DB.
Am I right?

If no, have this change <field name="name">test</field> in your xml file, update the Database, have a back up again and try to restore.

If you fear losing data on upgrade, go on Administration/Low Level Objects/Actions/Window Actions.Search the records which have no name. Give those orphans a name and back up again.

Hope this helps.
Thanks.

[hakimkerbiche (hak-dz)]

Hello every body,

I happy to join this forum,

Thank you very much for your answers

Really i find there the solution for my problem of restoring a database,
thanks Jay,

[Maximilian Gaß (mxey)]

You only have fixed a symptom here. As a next step, the model needs to be fixed to make the field required. That will prevent the ORM from dropping the constraint on the inherit table.

Additionally, in the longer term, the ORM needs to be fixed to error out in these situations, because PostgreSQL will change to prevent dropping NOT NULL constraints on inherited tables.

I have attached a patch to fix the model, but I am not deep enough into the ORM to fix the actual issue.