19

I've obtained a dump of my PostgreSQL database with:

pg_dump -U user-name -d db-name -f dumpfile

which I then proceed to restore in another database with:

psql X -U postgres  -d db-name-b -f dumpfile

My problem is that the database contains referential constraints, checks and triggers and some of these (checks it would seem in particular) fail during restoration since the information is not loaded in the order that would cause those checks to be honored. For instance, inserting a row in a table may be associated with a CHECK that calls a plpgsql function that checks whether a condition holds in some other unrelated table. If that latter table is not loaded by psql before the former, an error occurs.

The following is a SSCCE that produces such a database that once dumped with pg_dump cannot be restored:

CREATE OR REPLACE FUNCTION fail_if_b_empty () RETURNS BOOLEAN AS $$
    SELECT EXISTS (SELECT 1 FROM b)
$$ LANGUAGE SQL;

CREATE TABLE IF NOT EXISTS a (
     i              INTEGER                    NOT NULL
);

INSERT INTO a(i) VALUES (0),(1);
CREATE TABLE IF NOT EXISTS b (
    i  INTEGER NOT NULL
);
INSERT INTO b(i) VALUES (0);

ALTER TABLE a ADD CONSTRAINT a_constr_1 CHECK (fail_if_b_empty());

Is there a way to disable (from the command line) all such constraints during dump restoration and enable them back again afterwards? I am running PostgreSQL 9.1.

  • I am wondering, AFAIK there are no -X and -d options for pg_dump. pg_dump produces a dump that is restorable in an empty DB. – dezso Sep 3 '14 at 15:12
  • 1
    @dezso right, these were typos, I've updated the question. The dump sadly, is not restorable in an empty DB due to the reasons I am citing. – Marcus Junius Brutus Sep 3 '14 at 15:24
  • The question badly needs your version of Postgres. This should be obvious without me pointing it out. – Erwin Brandstetter Sep 3 '14 at 17:25
  • @ErwinBrandstetter I can reproduce the same problem on 9.6, see bugs.debian.org/cgi-bin/bugreport.cgi?bug=859033 for another example (more real-world, slightly larger, MWE) – mirabilos Mar 31 '17 at 14:07
  • 1
    @mirabilos: I'd say: if you use a function that references other tables in a CHECK constraint, then all guarantees are voided, because that's not officially supported, just tolerated. But declaring the CHECK constraint NOT VALID made it work for me in every respect. There may be corner cases I never touched ... – Erwin Brandstetter Mar 31 '17 at 16:03
16

So you look up other tables in a CHECK constraint.

CHECK constraints are supposed to run IMMUTABLE checks. What passes OK for a row at one time should pass OK at any time. That's how CHECK constraints are defined in the SQL standard. That's also the reason for this restriction (per documentation):

Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row.

Now, expressions in CHECK constraints are allowed to use functions, even user-defined functions. Those should be restricted to IMMUTABLE functions, but Postgres does not currently enforce this. According to this related discussion on pgsql-hackers, one reason is to allow references to the current time, which is not IMMUTABLE by nature.

But you are looking up rows of another table, which is completely in violation of how CHECK constraints are supposed to work. I am not surprised that pg_dump fails to provide for this.

Move your check in another table to a trigger (which is the right tool), and it should work with modern versions of Postgres.

PostgreSQL 9.2 or later

While the above is true for any version of Postgres, several tools have been introduced with Postgres 9.2 to help with your situation:

pg_dump option --exclude-table-data

A simple solution would be to dump the db without data for the violating table with:

--exclude-table-data=my_schema.my_tbl

Then append just the data for this table at the end of the dump with:

--data-only --table=my_schema.my_tbl

But complications with other constraints on the same table might ensue. There is an even better solution:

NOT VALID

There is the NOT VALID modifier for constraints. Only available for FK constraint in v9.1, but this was extended to CHECK constraints in 9.2. Per documentation:

If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates [...]

A plain postgres dump file consists of three "sections":

  • pre_data
  • data
  • post-data

Postgres 9.2 also introduced an option to dump sections separately with -- section=sectionname, but that's not helping with the problem at hand.

Here is where it gets interesting. Per documentation:

Post-data items include definitions of indexes, triggers, rules, and constraints other than validated check constraints. Pre-data items include all other data definition items.

Bold emphasis mine.
You can change the offending CHECK constraint to NOT VALID, which moves the constraint to the post-data section. Drop and recreate:

ALTER TABLE a DROP CONSTRAINT a_constr_1;
ALTER TABLE a ADD  CONSTRAINT a_constr_1 CHECK (fail_if_b_empty()) NOT VALID;

This should solve your problem. You can even leave the constraint in that state, since that better reflects what it actually does: check new rows, but give no guarantees for existing data. There is nothing wrong with a NOT VALID check constraint. If you prefer, you can validate it later:

ALTER TABLE a VALIDATE CONSTRAINT a_constr_1;

But then you are back to the status quo ante.

  • I've enriched the question with a SSCCE that shows a database that cannot be restored. I get what you're saying, however I don't see why the problematic situation I showcase in my SSCCE cannot also be reproduced with triggers instead of checks. – Marcus Junius Brutus Sep 3 '14 at 19:04
  • 1
    @MarcusJuniusBrutus: Because check constraints are evaluated for all rows that are already in the table on creation, while triggers only run on defined events. – Erwin Brandstetter Sep 3 '14 at 19:33
  • I reproduced the exact schema logic using triggers. Using triggers, the restoration does indeed succeed but it appears that this is only due to the fact the pg_dump adds the triggers at the end of the dump file, whereas it creates the CHECKs as part of the CREATE TABLE command. So the restoration could have also succeeded for the check case if the pg_dump tool used a different approach. I fail to see why my DDL is OK if I use triggers but not OK if I use checks as the exact same logic is implemented in both cases (you can see the version of the script using triggers in my own answer). – Marcus Junius Brutus Sep 3 '14 at 23:22
  • 1
    @MarcusJuniusBrutus: if you feel pg_dump should generate different DDL for check constraints (e.g. adding all of them at the end) you should post that to the Postgres mailing list as an enhancement request. But I do agree with Erwin that you are mis-using check constraints for something they were not designed for. So I wouldn't expect that change request to be implemented in the near future. Btw: your SSCCE would be better modeled using a foreign key between the two tables. – a_horse_with_no_name Sep 4 '14 at 7:09
  • @MarcusJuniusBrutus: There are solutions for Postgres 9.2 or later. That's why your version of Postgres is crucial. Maybe upgrading is an option for you? Postgres 9.1 is aging anyway ... – Erwin Brandstetter Sep 4 '14 at 17:20
2

It seems that this is due to the way pg_dump creates the dump. Looking at the actual dump I saw that the CHECK constraint was present in the dump file using the syntax that's part of the CREATE TABLE command:

CREATE TABLE a (
    i integer NOT NULL,
    CONSTRAINT a_constr_1 CHECK (fail_if_b_empty())
);      

This creates the failure upon restoration of the database as the check is put in place before either table a or table b have any data in them. If however, the dump file is edited and the CHECK is added using the following syntax instead, at the end of the dump file:

ALTER TABLE a ADD CONSTRAINT a_constr_1 CHECK (fail_if_b_empty()); 

... then there is no problem in the restoration.

The exact same logic can be implemented using a TRIGGER as in the following script:

CREATE OR REPLACE FUNCTION fail_if_b_empty (
    ) RETURNS BOOLEAN AS $$
    SELECT EXISTS (SELECT 1 FROM b)
$$ LANGUAGE SQL;

DROP TABLE IF EXISTS a;

CREATE TABLE IF NOT EXISTS a (
    i   INTEGER   NOT NULL
);

INSERT INTO a(i) VALUES (0),(1);

CREATE TABLE IF NOT EXISTS b (
    i  INTEGER NOT NULL
);

INSERT INTO b(i) VALUES (0);

CREATE TRIGGER tr1 AFTER INSERT OR UPDATE ON a
FOR EACH ROW
EXECUTE PROCEDURE fail_if_b_empty();  

In this case however, pg_dump creates (by default) the trigger at the end of the dump file (and not in the CREATE TABLE statement as in the case of a check) and so the restoration succeeds.

  • don't see any trigger in your example – Sam Watkins Jun 26 '15 at 7:58
  • 1
    @SamWatkins copy-paste error, fixed it. – Marcus Junius Brutus Jun 26 '15 at 8:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.