digoal
2021-07-29
PostgreSQL , 列删除 , pg_attribute
新增1600个列后无法在新增问题不大, 问题是drop后还是不能新增, 即使vacuum full操作后, pg_attribute 依旧存在dropped column, 不是很合理, 原因猜测： 除非vacuum full时row被原样拷贝了, 那dropped的column可能依旧存在与tuple内, 需要元数据来确保解析的正确性.
这个问题肯定是要解决的. 已上报bug, 按PG社区的节奏应该很快可以解决.
更新, 我的错, 文档已经写了这是个limit,不是bug,所以需要加新的feature.
https://www.postgresql.org/docs/13/limits.html
Columns that have been dropped from the table also contribute to the maximum column limit. Moreover, although the dropped column values for newly created tuples are internally marked as null in the tuple's null bitmap, the null bitmap also occupies space.
修复这个问题也并不简单,因为还牵涉到pk, fk等逻辑或物理关联的问题, 关联信息都是要与att系统表进行映射的.
复现方法.
do language plpgsql $$
declare
sql text := 'create table c (';
begin
for i in 1..1600 loop
sql := sql||'c'||i||' int8,';
end loop;
sql := rtrim(sql,',')||')';
execute sql;
end;
$$;
postgres=# alter table c add column c int8;
ERROR: 54011: tables can have at most 1600 columns
LOCATION: ATExecAddColumn, tablecmds.c:6573
postgres=# alter table c drop column c1;
ALTER TABLE
postgres=# alter table c add column c int8;
ERROR: 54011: tables can have at most 1600 columns
LOCATION: ATExecAddColumn, tablecmds.c:6573
postgres=# vacuum full c;
VACUUM
postgres=# alter table c add column c int8;
ERROR: 54011: tables can have at most 1600 columns
LOCATION: ATExecAddColumn, tablecmds.c:6573
postgres=# select attname from pg_attribute where attrelid ='c'::regclass and attisdropped;
attname
------------------------------
........pg.dropped.1........
(1 row)
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
