Peter Eisentraut <peter.eisentr...@2ndquadrant.com> writes:
> I think it would be very easy to restore check constraints separately
> after all tables in pg_dump. There is already support for that, but
> it's only used when necessary, for things like not-valid constraints.
> The argument in favor of keeping the constraint with the table is
> probably only aesthetics,
No, it's mainly about performance. Checking the constraint at data load
time avoids extra scans of the table, and should work in any case that
we consider supported.
To be clear, I totally reject the notion that we should consider this
case supported, or that kluging pg_dump to not fail would make it so.
As a counterexample, if you have a poor-mans-FK check constraint on
table A that only succeeds when there's a matching row in table B, it
cannot prevent the case where you insert a valid (matching) row in
table A and then later delete its matching row in B.
Maybe someday we'll have full database assertions (with, no doubt,
a ton of performance caveats). In the meantime, let's not slow down
CHECK constraints for everyone in order to partially fix a
fundamentally broken use-case. If the documentation isn't clear enough
about such cases being unsupported, by all means let's make it so.
regards, tom lane
