已删除列的清除PostgreSQL知识

Question

我在Postgres数据库中有一张表，用于测试环境，在其中需要同时添加和删除多个列。问题是Postgres最多只支持1600列，这个计数包括已删除的列。我的表永远不会有1600个“未删除”的列，但随着时间的推移，它累积的列数超过了1600个。

我尝试过使用VACUUM和VACUUM FULL，并尝试将现有列重新设置为其自身类型（ALTER TABLE table ALTER COLUMN anycol TYPE anytype），以使Postgres扫描所有列并清理已删除列的内存，但这些方法都不能重置Postgres的“列数”。

我知道可以通过复制整个表来解决这个问题，但这样做也存在问题，并且是另一个问题。

你知道有什么办法让Postgres忘记已经删除的列吗？

我知道Postgres并不是为这样的应用程序设计的，但我不想深入讨论我们为什么选择以这种方式实现它。如果你有其他工具可用，我会很感兴趣听听，但我仍然希望找到解决这个问题的方法。

- Erik

---

你说过你已经在表上尝试了 VACUUM。那么 VACUUM FULL 呢？或者尝试使用 ALTER TABLE table ALTER COLUMN anycol TYPE anytype;，将列更改为它已经拥有的相同类型。 - Wayne Conrad

正如您已经发现的那样，至少有两种方法：1）CREATE TABLE new AS select * FROM the_table; 重新命名事物；另一种是pg_dump -t thetable; 删除表格；从pg_dump结果重新创建表格。在这两种情况下，您都需要重建FK约束（在第二种情况下稍微简单一些）。 - joop

Answer 1

除了重新创建表格，否则无法实现此操作，正如您已经发现的那样。

否则，数据库系统将不得不以某种方式跟踪已清除删除列使用的存储空间，并重新编号属性。这将非常昂贵和复杂。

- Peter Eisentraut

---

知道这个很好。现在我只需要想办法有效地复制表格。 - Erik

在我看来，这似乎需要使用VACUUM FULL（包括ANALYZE？），它会重写整个表并需要一个表锁。更新pg_class的开销不会太大，相比于它所要做的所有其他工作而言。 - Frank Heikens

Answer 2

系统表pg_attribute仍然显示旧的（已删除）列。我不知道为什么，但这看起来像是一个bug。

SELECT 
  relnatts, 
  attname, 
  attisdropped 
FROM 
  pg_class 
    JOIN pg_attribute att ON attrelid = pg_class.oid 
WHERE 
  relname = 'your_table_name';

您能否将一个包含简单示例的错误报告发送至 [e-mail] 或 http://www.postgresql.org/support/submitbug？

- Frank Heikens

---

如果我的记忆没有出错，它实际上是为了事务感知的 alter table 语句而存在的。但是没错，它最终会删除该行。 :-) - Denis de Bernardy