postgresql – 如何将Postgres外键模仿到分区表中

CREATE TABLE A (
    MyKey SERIAL PRIMARY KEY
);

CREATE TABLE B (
    AKey INT,-- Should have: REFERENCES A (MyKey),-- but can't due to Postgres limitations
);

CREATE TABLE APart1 (
    Field1 INT,PRIMARY KEY (MyKey)
) INHERITS (A);

CREATE TABLE APart2 (
    Field2 INT,PRIMARY KEY (MyKey)
) INHERITS (A);

CREATE FUNCTION ValidateKeyInA(aKey INT) RETURNS BOOL AS $$
    BEGIN
        PERFORM * FROM A WHERE MyKey = aKey;
        IF FOUND THEN
            RETURN TRUE;
        END IF;
        RETURN FALSE;
    END;
$$LANGUAGE PLPGSQL;

ALTER TABLE B ADD CHECK (ValidateKeyInA(AKey));

WITH aKey AS (INSERT INTO APart1 (Field1) VALUES (1) RETURNING MyKey)
INSERT INTO B (AKey) SELECT * FROM aKey;

WITH aKey AS (INSERT INTO APart2 (Field2) VALUES (2) RETURNING MyKey)
INSERT INTO B (AKey) SELECT * FROM aKey;

这很好用,直到我去转储和恢复数据库.那时,Postgres不知道表B依赖于表A(及其分区)中的数据,而B恰好在表A之前被转储.我试图将“DEFERRABLE”关键字添加到我所在的行添加约束,但Postgres不支持可延迟检查约束.

我建议的方法是将我的检查约束转换为约束触发器,我可以推迟,然后在事务中导入我的数据库转储.对此有更直接的方法吗？例如,有没有办法让我告诉Postgres不要转储表B,直到表A及其所有分区都被转储(例如,从B添加依赖关系到A的分区)？我应该使用的其他一些模式呢？谢谢.