sql - 如何在 PostgreSQL 中安全地重新创建表
问题描述
我的 PostgreSQL 服务器上有一些表占用的磁盘空间比应有的要多得多。我知道完全真空会回收空间,但是完全真空会在表上设置排他锁,并且这些表会不断被写入,完全真空会导致服务中断过多(其中一个表使用超过 TB 的额外空间)。所以我想出了以下函数来重新创建一个表而不锁定它。
create or replace function recreate_table(t text) returns void as $$
declare
new_name text;
old_name text;
seq_name text;
tab regclass;
begin
new_name := 'new_' || t;
old_name := 'old_' || t;
seq_name := t || '_id_seq';
execute format('create table %I (like %I including all)', new_name, t);
execute format('alter table %I rename to %I', t, old_name);
execute format('alter table %I rename to %I', new_name, t);
execute format('alter sequence %I owned by %I.id', seq_name, t);
execute format('insert into %I select * from %I', t, old_name);
--- execute format('drop table %I', old_name);
end;
$$ LANGUAGE plpgsql;
它似乎按预期工作,但我想知道这有多安全。这种方法有什么我没有看到的问题吗?
注意:我使用插入是因为有问题的表包含日志数据,因此它们会不断附加到,但现有记录永远不会改变。为了正确地做到这一点,我会使用插入和更新的某种组合,但是我的 postgres 是 9.4 并且不支持 upserts 并且使用插入对于我的目的来说已经足够了。
更新:看起来像alter table
独占锁定表并且锁定一直持续到函数完成,因此表在执行插入时保持锁定。此外,显然 PostgreSQL 本身并不支持函数内的提交。使用 dlink API 可以解决此问题。需要弄清楚如何使用它在表重命名和插入之间进行提交。
解决方案
推荐阅读
- r - 根据 groupby 列下的某些过滤条件替换 ar data.table 的整列
- android - android 上的 HttpURLConnection 不适用于 24 级以下的 API
- android - Firebase数据上的Ionic Firebase未定义值
- github - Shopify 主题开发中的 .gitignore 中包含哪些文件和文件夹
- arrays - 在 Angular 更新 CRUD 表单上显示数组中的多个对象
- python - 如何在输出单词被陈述的次数时从用户输入生成列表或嵌套列表?
- amazon-web-services - 如何添加 terraform aws_cognito_user_pool_client 访问令牌和 id 令牌有效性?
- node.js - node.js mongoose find() 没有价值
- swift - 如何在 defaultCameraController 中禁用双击?
- sql - 如何更新表以删除特殊字符并仅返回字母数字值 -Teradata