Postgres 12 如何将跨服务器外部表挂载为现有分区表的分区

问题根因 #

PostgreSQL原生分区表不支持挂载外部表作为分区的核心限制是：只要父分区表定义了任何唯一约束（含主键约束），就无法关联外部表分区。
这是因为postgres_fdw无法跨独立PostgreSQL实例做全局唯一性校验，数据库从设计层面禁止了这种关联逻辑，你之前仅删除login字段的唯一约束但仍保留(id,region)主键约束，所以报错仍然存在。

可行解决方案 #

方案1：移除父表唯一约束，直接挂载外部表分区 #

如果你要保留原生分区表的使用方式，按以下步骤操作：

1. 删除汉堡服务器上父表test的所有唯一约束：

-- 删除主键约束
ALTER TABLE public.test DROP CONSTRAINT test_pkey;
-- 如果还有残留的login唯一约束也一并删除
ALTER TABLE public.test DROP CONSTRAINT IF EXISTS test_login_key;

2. 执行外部表挂载操作：

ALTER TABLE public.test ATTACH PARTITION public.moscow FOR VALUES IN (200);

3. 补充业务规则适配：

• 主键唯一性由你预设的ID生成规则保证：汉堡分区ID为n*1000+100、莫斯科分区ID为n*1000+200，天然全局唯一，无需数据库层面主键约束校验。
• 单分区login唯一性：分别给本地hamburg分区、莫斯科服务器上的moscow表添加唯一约束(login,region)，即可保证同区域下login不重复。
• 写权限控制：给业务账号授权时，仅授予本地hamburg分区的INSERT/DELETE权限，父表test仅授予SELECT/UPDATE权限，即可满足业务写操作仅操作本地分区的要求。

方案2：用视图模拟分区表，保留两边表的约束 #

如果不想删除原有主键、唯一约束，可以放弃原生分区表，改用视图实现同等效果：

1. 删除原有test分区表，创建同名视图：

CREATE VIEW public.test AS
SELECT * FROM public.hamburg
UNION ALL
SELECT * FROM public.moscow;

2. 创建INSTEAD OF触发器控制视图写操作：

-- 插入触发器：仅允许region=100的本地数据插入
CREATE OR REPLACE FUNCTION test_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.region = 100 THEN
        INSERT INTO public.hamburg VALUES (NEW.*);
        RETURN NEW;
    ELSE
        RAISE EXCEPTION '仅允许插入汉堡本地分区数据';
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_insert
INSTEAD OF INSERT ON public.test
FOR EACH ROW EXECUTE FUNCTION test_insert_trigger();

-- 删除触发器：仅允许删除region=100的本地数据
CREATE OR REPLACE FUNCTION test_delete_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.region = 100 THEN
        DELETE FROM public.hamburg WHERE id = OLD.id AND region = OLD.region;
        RETURN OLD;
    ELSE
        RAISE EXCEPTION '仅允许删除汉堡本地分区数据';
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_delete
INSTEAD OF DELETE ON public.test
FOR EACH ROW EXECUTE FUNCTION test_delete_trigger();

该方案下SELECT/UPDATE操作视图的效果和原生分区表完全一致，同时可以保留hamburg、moscow两张表的主键、唯一约束，不需要调整原有表结构。

补充说明 #

你遇到的pgAdmin报错cannot unpack non-iterable Response object是pgAdmin前端工具的兼容性bug，和数据库逻辑无关，直接用SQL语句操作即可。

内容的提问来源于stack exchange，提问作者Vind Iskald