Re: Constraint documentation

On August 10, 2018 7:17:09 PM GMT+05:30, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)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.

+1

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.