postgresql 在postgres openerp中,一个表最多可以有1600列

des4xlb0  于 2022-11-04  发布在  PostgreSQL
关注(0)|答案(4)|浏览(300)

在我的open-erp应用程序中,我得到以下错误:

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

我从product_product表中删除了不必要的列,但仍然出现上述错误。
我该如何解决,请建议我。等待回复。谢谢

fd3cxomn

fd3cxomn1#

我知道这可能有点晚了(也许你已经在旅行中找到了答案),但是在写这篇文章的时候(2016年8月),从Postgres数据库表中删除一个列实际上并没有从表空间中删除该列,它仅仅是隐藏了它,并且该列仍然计入表空间的列限制,请参见Postgres的文档中关于ALTER TABLE操作的内容。
“* DROP COLUMN表单不会实际移除数据行,而只是让SQL作业看不见它。数据表中后续的插入和更新作业将会储存数据行的Null值。因此,卸除数据行的速度很快,但不会立即减少数据表的磁盘大小。因为被删除的列所占用的空间没有被回收。随着现有行的更新,空间将随着时间的推移而被回收。(这些语句在删除系统oid列时不适用;这是通过立即重写完成的。)*”
来源:https://www.postgresql.org/docs/9.5/static/sql-altertable.html
因此,如果有迁移或某些操作在表上重复执行DROP/ADD循环,则将开始耗尽可用列,直到达到限制。
删除表并重新创建它,或者使用INSERT INTO将数据复制到新表中(尽管您必须重新创建外键等),将得到一个干净的表,其中不包含所有(隐藏的)从先前操作中删除的列。这将有效地重置列数计数。

3bygqnnd

3bygqnnd2#

一个帮助我减少列数的快速修复方法是将数据库转储到一个文件中,删除数据库,然后按如下所示重新创建它:

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

m3eecexj3#

这是因为所有的列都在ir.model.fields表中,它与ir.model是一对多的关系。您需要从那里删除。
也可以从UI中删除列。
转到Settings -> Database structure -> Models
找到您的模型并从中删除字段。
希望对你有帮助。

a9wyjsp7

a9wyjsp74#

我们在用ORM构建后端时遇到了同样的问题,我们无法确定是哪个表导致了这个问题。所以我创建了一个脚本来重新创建所有的表和外键。

DROP TABLE IF EXISTS for_keys;
    CREATE TEMP TABLE for_keys (
       table_schema text,
       current_table_name text,
       current_column_name text,
       foreign_table_schema text,
       foreign_table_name text,
       foreign_column_name text
    );

    DO $$
    DECLARE
       tabele text[];
       tabela text;
       temp_tabela text;
       fks for_keys[];
       fks_item for_keys;

    BEGIN
       SELECT array_agg(table_name) INTO tabele FROM information_schema.tables WHERE table_schema = 'public';
       FOREACH tabela IN ARRAY tabele
       LOOP
       -- Creating temporary tables
        temp_tabela := CONCAT(tabela,'_temp');
        EXECUTE format('CREATE TABLE %I (like %I including all); INSERT INTO %I SELECT * FROM %I', temp_tabela, tabela, temp_tabela, tabela);

       -- Extracting foreign keys
        SELECT
        tc.table_schema, 
        tc.table_name as current_table_name, 
        kcu.column_name current_column_name, 
        ccu.table_schema AS foreign_table_schema,
        ccu.table_name AS foreign_table_name,
        ccu.column_name AS foreign_column_name
        INTO
        fks_item
        FROM 
            information_schema.table_constraints AS tc 
            JOIN information_schema.key_column_usage AS kcu
              ON tc.constraint_name = kcu.constraint_name
              AND tc.table_schema = kcu.table_schema
            JOIN information_schema.constraint_column_usage AS ccu
              ON ccu.constraint_name = tc.constraint_name
              AND ccu.table_schema = tc.table_schema
        WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name=tabela;
        if found then
         fks := array_append(fks, fks_item);
        end if;
       END LOOP;

       -- Dropping old tables after safely extracting all foreign keys
       FOREACH tabela IN ARRAY tabele
       LOOP
        EXECUTE format('DROP TABLE %I cascade', tabela);
       END LOOP;

       -- Creating new tables and dropping temp
       FOREACH tabela IN ARRAY tabele
       LOOP
        temp_tabela := CONCAT(tabela,'_temp');
        EXECUTE format('CREATE TABLE %I (like %I including all); INSERT INTO %I SELECT * FROM %I', tabela, temp_tabela, tabela, temp_tabela);
        EXECUTE format('DROP TABLE %I', temp_tabela);
       END LOOP;

       -- Re-creating relations between tables
       FOREACH fks_item IN ARRAY fks
       LOOP
        EXECUTE format('ALTER TABLE %I ADD FOREIGN KEY (%I) REFERENCES %I(%I)', fks_item.current_table_name, fks_item.current_column_name, fks_item.foreign_table_name, fks_item.foreign_column_name);
        raise notice 'Created relation: %', CONCAT(fks_item.current_table_name,'_',fks_item.current_column_name,' -> ',fks_item.foreign_table_name ,'_', fks_item.foreign_column_name);
       END LOOP;

    END $$;

相关问题