How to Mimic Postgres Foreign Keys into a Partitioned Table

Question

I have a partitioned table (call it A) with a serial primary key that is referenced by another table (call it B). I know that I can't actually create a foreign key from one to the other (since I don't know from which partition the data is actually stored), so instead, I am attempting to mimic the behavior of a foreign key using check constraints. Something like the following:

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;

This works just fine, until I go to dump and restore the database. At that point, Postgres doesn't know that table B depends on the data in table A (and its partitions), and B happens to be dumped prior to table A. I tried to add the "DEFERRABLE" keyword to the line where I add the constraint, but Postgres doesn't support deferrable check constraints.

My proposed approach is to convert my check constraint to a constraint trigger, which I CAN defer, then import my database dump in a transaction. Is there a more straight-forward approach to this? For example, is there some way for me to tell Postgres not to dump table B until table A and all of its partitions have been dumped (e.g., add dependencies from B to the partitions of A)? Some other pattern that I should be using instead? Thank you.

Answer 1

pg_dump sorts automatically the table alphabetically (see my comment above). However, if you want to change the order how the table are dumped and restored but cannot rename your tables according to the desired order, you can use the --use-list option with pg_restore. See http://www.postgresql.org/docs/9.3/static/app-pgrestore.html

pg_restore allow to control the order, how the database elements are restored with the option --use-list.

You have first to dump the database in custom format using the option -Fc, otherwise you cannot restore the dump with pg_restore:

pg_dump -Fc your_database -f database.dump

Than you generate a file which lists all elements in the dump:

pg_restore --list database.dump > backup.txt

The file backup.txt will be used as input for the pg_restore option --use-list, but first you can edit the file and change the order of the lines with copy/paste. You can independent change both table creation and data insert. Pay attention that your list remain consistent. You can also delete lines completely in order to exclude elements from the restore.

Finally restore your dump with the option --use-list:

pg_restore -d your_database --use-list backup.txt database.dump

I tested this procedure with your example and changed the order of tables A and B. If table A is restored first, dump is restored without errors. Otherwise, if B is restored first, the restore fails as expected with the error:

pg_restore: [archiver (db)] COPY failed for table "b": ERROR: new row for relation "b" violates check constraint "b_akey_check" DETAIL: Failing row contains (1). CONTEXT: COPY b, line 1: "1" WARNING: errors ignored on restore: 1