Postgresql – Error: relation doesn’t exist on self referencing check constraint when restoring database

postgresqlrestore

I'm trying to take a database dump from a PostgreSQL 9.4 database and restore it into a 9.6 database. It works, but in the error output I see the following error:

ERROR:  relation "my_tbl" does not exist

The error refers to a function check_constraint(). It is used for testing a constraint of my_tbl, and it references my_tbl.

To make this easier to visualize, here's a dummy code that illustrates the problem (the real code has a lot of irrelevant details):

CREATE TABLE public.my_tbl
(
  id integer,
  value citext,
  CONSTRAINT abc CHECK (check_constraint(value))
)

CREATE OR REPLACE FUNCTION check_constraint(
    value citext)
  RETURNS boolean AS
$BODY$
SELECT NOT EXISTS(
    SELECT id
    FROM my_tbl
    WHERE id = value::integer;
)
$BODY$
  LANGUAGE sql VOLATILE
  COST 100;

Obviously that's a circular dependency and is what's causing the error because one is created before the other. Even though everything ends up being created anyway, I'd like to get rid of this error. Is there a way to do this without changing the code of check_constraint()?

Best Answer

Apart from the incomprehensible function, you are violating a documented rule for check constraints:

PostgreSQL does not support CHECK constraints that reference table data other than the new or updated row being checked. While a CHECK constraint that violates this rule may appear to work in simple tests, it cannot guarantee that the database will not reach a state in which the constraint condition is false (due to subsequent changes of the other row(s) involved). This would cause a database dump and reload to fail. The reload could fail even when the complete database state is consistent with the constraint, due to rows not being loaded in an order that will satisfy the constraint. If possible, use UNIQUE, EXCLUDE, or FOREIGN KEY constraints to express cross-row and cross-table restrictions.

If what you desire is a one-time check against other rows at row insertion, rather than a continuously-maintained consistency guarantee, a custom trigger can be used to implement that. (This approach avoids the dump/reload problem because pg_dump does not reinstall triggers until after reloading data, so that the check will not be enforced during a dump/reload.)

Implement this in some other fashion. Since you don't show us the real use case, it is hard to advise how exactly you should do that.

The immediate reason for the import to fail is probably that the table does not exist yet when the function is defined.

Related Question