Re: plpgsql constraint checked data fails to restore

Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
> I think pg_dump should add the check constraints in the same manner as
> it does triggers.

> Bruce, do we have a TODO item for this?

No, because that idea has been proposed and rejected before --- it adds
overhead (extra table scans) and reduces readability of the SQL dump,
in order to "support" a programming technique that will never really
work correctly anyway. A CHECK constraint that depends on anything more
than the content of the row being checked is simply wrong.

Essentially what we implement is what SQL92 calls the "intermediate"
level of CHECK support:

1) The following restrictions apply for Intermediate SQL:

a) The <search condition> contained in a <check constraint defi-
nition> shall not contain a <subquery>.

regression=# create table bbb(f2 int check (f2 in (select f1 from aaa)));
ERROR: cannot use subquery in check constraint

Of course, a function call that executes a query internally is simply
a cheat to try to bypass this restriction; the fact that we don't catch
you cheating doesn't mean we promise it will work. The function call
is a lot worse, in fact, because there is no way pg_dump can even detect
the data dependency, and thus no way to know when it is safe to add the
check constraint.

There is no point in changing the behavior of pg_dump until and unless
we improve the handling of CHECK constraints to support subqueries ---
which is more or less the same thing as supporting database-wide
ASSERTIONs, and I don't know of anybody even thinking of working on that.

regards, tom lane