PostgreSQL – How to imitate the Postgres foreign key into the partition table

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));< br />
WITH aKey AS (INSERT INTO APart1 (Field1) VALUES (1) RETURNING MyKe y)
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;

pg_dump -Fc your_database -f database.dump

pg_restore --list database.dump> backup.txt

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

I have a partition table (call it A ), where one of the serial primary keys is referenced by another table (call it B). I know I can’t actually create a foreign key from one to the other (because I don’t know which partition the data is actually stored in), so instead, I tried to imitate the behavior of foreign keys using check restrictions. Similar to 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;< br />$$LANGUAGE PLPGSQL;

ALTER TABLE B ADD CHECK (ValidateKeyInA(AKey));
< br />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 well until I go to dump and restore the database. At that time, Postgres I don’t know that table B depends on the data in table A (and its partitions), and B was dumped just before table A. I tried to add the “DEFERRABLE” keyword to the row where I was to add constraints, but Postgres does not support the Delay check constraints.

The method I suggest is to convert my check constraints into constraint triggers, I can defer, and then import my database dump in the transaction. Is there a more direct way to do this ? For example, is there a way for me to tell Postgres not to dump table B until table A and all its partitions are dumped (for example, add a dependency from B to A’s partition)? What other modes should I use? Thank you.

pg_dump automatically sorts the tables in alphabetical order (see the comments above). However, if you want to change the order of dumping and restoring tables, it cannot be based on To rename the tables in the required order, you can use the –use-list option with pg_restore. See http://www.postgresql.org/docs/9.3/static/app-pgrestore.html

< p>pg_restore allows you to control the order, how to use the option -use-list to restore database elements.

You first use the option -Fc to dump the database in a custom format, otherwise you cannot use pg_restore to restore the dump:

pg_dump -Fc your_database -f database.dump

Rather than generating a file listing all the 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 use copy/paste to change The order of rows. You can independently change the table creation and data insertion. Please note that your list remains the same. You can also delete rows completely to exclude elements from the restoration.

Finally use the option –use-list Restore dump:

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

I tested this process using your example and The order of tables A and B has been changed. If you restore table A first, the dump will be restored without error. Otherwise, if you restore B first, the restore will fail as expected with an 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