带有citus扩展的Postgresql切分不起作用

Question

我使用带有citus扩展的Postgresql对表进行切分，并且无法像下面这样分割表。下表有一个主键和两个唯一键。我正试图用主键(即pid )对列进行分割。注意事项：我不允许更改表结构。这些表是由工具创建的。

CREATE TABLE person 
(
    pid bigint NOT NULL,
    name character varying(100),
    address_pid bigint NOT NULL,
    address_type character varying(100),
    CONSTRAINT id_pkey PRIMARY KEY (pid),
    CONSTRAINT addr_id UNIQUE (address_pid),
    CONSTRAINT addr_type_id UNIQUE (address_type, address_pid)
);

这是我的切分查询：

select create_distributed_table('person', 'pid');

抛出的错误是：

Error: Distributed relations cannot have UNIQUE, EXCLUDE, or PRIMARY KEY constraints that do not include the partition column

有人能帮我把这类桌子分拆吗？

@CraigKerstiens：

如何处理切分时，我们有多个外键，如这一个。

CREATE TABLE table
(
    pid bigint NOT NULL,
    search_order integer NOT NULL,
    resource_pid bigint NOT NULL,
    search_pid bigint NOT NULL,
    CONSTRAINT hfj_search_result_pkey PRIMARY KEY (pid),
    CONSTRAINT idx_searchres_order UNIQUE (search_pid, search_order),
    CONSTRAINT fk_searchres_res FOREIGN KEY (resource_pid)
        REFERENCES public.table1 (res_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT fk_searchres_search FOREIGN KEY (search_pid)
        REFERENCES public.table2 (pid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

假设table1和table2已经被分割。

Answer 1

此时，在Citus中，您不可能有一个不包含分区列的唯一约束。在这种情况下，可以强制执行person id所特有的地址，而不是全局唯一的地址。要做到这一点，你可以：

CREATE TABLE person 
(
    pid bigint NOT NULL,
    name character varying(100),
    address_pid bigint NOT NULL,
    address_type character varying(100),
    CONSTRAINT id_pkey PRIMARY KEY (pid),
    CONSTRAINT addr_id UNIQUE (pid, address_pid),
    CONSTRAINT addr_type_id UNIQUE (pid, address_type, address_pid)
);