table can have at most 1600 columns in postgres openerp

Question

In my open-erp application I am getting error below:

     2015-04-01 09:35:55,959 4169 ERROR new_db openerp.sql_db: bad query: ALTER TABLE "product_product" ADD COLUMN "location" VARCHAR
Traceback (most recent call last):
  File "/opt/openerp/server/openerp/sql_db.py", line 226, in execute
    res = self._obj.execute(query, params)
OperationalError: tables can have at most 1600 columns

2015-04-01 09:35:55,960 4169 ERROR new_db openerp: Failed to initialize database `new_db`.
Traceback (most recent call last):
  File "/opt/openerp/server/openerp/cli/server.py", line 97, in preload_registry
    db, registry = openerp.pooler.get_db_and_pool(dbname,update_module=update_module)
  File "/opt/openerp/server/openerp/pooler.py", line 33, in get_db_and_pool
    registry = RegistryManager.get(db_name, force_demo, status, update_module)
  File "/opt/openerp/server/openerp/modules/registry.py", line 203, in get
    update_module)
  File "/opt/openerp/server/openerp/modules/registry.py", line 233, in new
    openerp.modules.load_modules(registry.db, force_demo, status, update_module)
  File "/opt/openerp/server/openerp/modules/loading.py", line 350, in load_modules
    force, status, report, loaded_modules, update_module)
  File "/opt/openerp/server/openerp/modules/loading.py", line 256, in load_marked_modules
    loaded, processed = load_module_graph(cr, graph, progressdict, report=report, skip_modules=loaded_modules, perform_checks=perform_checks)
  File "/opt/openerp/server/openerp/modules/loading.py", line 165, in load_module_graph
    init_module_models(cr, package.name, models)
  File "/opt/openerp/server/openerp/modules/module.py", line 374, in init_module_models
    result = obj._auto_init(cr, {'module': module_name})
  File "/opt/openerp/server/openerp/osv/orm.py", line 3164, in _auto_init
    cr.execute('ALTER TABLE "%s" ADD COLUMN "%s" %s' % (self._table, k, get_pg_type(f)[1]))
  File "/opt/openerp/server/openerp/sql_db.py", line 161, in wrapper
    return f(self, *args, **kwargs)
  File "/opt/openerp/server/openerp/sql_db.py", line 226, in execute
    res = self._obj.execute(query, params)
OperationalError: tables can have at most 1600 columns

I remove the unnecessary columns from the product_product table, but the above error still comes.

How do I resolve it, kindly suggest me. waiting for reply. Thanks

Answer 1

I know this may be a little late (and maybe you've already found this answer in your travels) but as of this writing (Aug 2016) dropping a column from a Postgres database table does not actually remove the column from the table space, it merely hides it and the column still counts toward the table space's column limit, see Postgres' documentation on the ALTER TABLE operation.

"The DROP COLUMN form does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent insert and update operations in the table will store a null value for the column. Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed. The space will be reclaimed over time as existing rows are updated. (These statements do not apply when dropping the system oid column; that is done with an immediate rewrite.)"

Source: https://www.postgresql.org/docs/9.5/static/sql-altertable.html

So if you have migrations or some operation that performs a DROP/ADD cycle repeatedly on a table, you will begin to exhaust the available columns until you reach the limit.

Dropping the table and recreating it, or copying the data into a new table using INSERT INTO (though you'll have to recreate foreign keys, etc.), will result in a clean table without all of the (hidden) dropped columns from previous operations. This will effectively reset your column number count.

Answer 2

A quick fix that helped me out to reduce the number of colums was dumping the database to a file, deleting the database and then recreating it as follows:

sudo su - postgres
pg_dump nameOfDatabase > backup.psql
dropdb nameOfDatabase
createdb --owner nameOfOwner nameOfDatabase
psql -d nameOfDatabase -f backup.psql
logout