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

I have a partitioned table (call it A) where a serial primary key is referenced by another table (call it B). I know I can’t actually go from one to the other One creates a foreign key (because I don’t know which partition the data is actually stored in), so instead, I tried to imitate the behavior of the foreign key 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;
$$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;

This works well, until I go to dump and restore the database. At that time, Postgres did not 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 deferrable check constraints.

The method I suggest is to add The check constraint is converted to a constraint trigger, I can postpone it, 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 table alphabetically (see comments above). However, if you want To change the order of dumping and restoring tables, but you cannot rename the 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 allows you to control the order, how to use the option –use-list to restore the 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 list of all elements in the dump File:

pg_restore --list database.dump> backup.txt

The file backup.txt will be used as the input of pg_restore option –use-list, But first you can edit the file and use copy/paste to change the order of rows. You can change the table creation and data insertion independently. 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 to restore the dump:

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

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

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

Leave a Comment

Your email address will not be published.