Re: Foreign keys

Joshua D. Drake wrote:
>
> >In any case the same logic that leads to it being desirable to report all
> >the
> >errors to the user in a UI and not just report them one by one also
> >applies to
> >the database. I'm not sure it's the most important issue in the world, but
> >it
> >does seem like a "it would be nice" feature if it reported all the errors
> >in
> >the statement, not just the first one it finds.
> >
>
> Seems kind of extraneous to me. I am guessing it would cause yet further
> overhead with our foreign key checks.

But in this case, it would be (or should be) overhead only in the case
of failure. In the case of success, all the constraints are checked
anyway -- they just succeed.

I would expect that the number of applications for which a constraint
violation is the norm and not the exception is very small.

But Tom's concern is a valid one. I expect a reasonable compromise
would be to record and show the errors for only the non-deferred
constraints in the currently executing statement, because after that
point the transaction is in an error state anyway and thus can't
continue without a rollback to a savepoint. It probably wouldn't make
sense to evaluate the deferred constraints within the erroring
statement anyway -- they're deferred, which by definition means they
don't get evaluated until commit, so evaluating them at failure time
could easily show errors that are only there because subsequent
statements never got executed.

As for the deferred constraints, it might be reasonable to show errors
only up to some limit (controlled by a GUC, perhaps), with the default
limit being 1, which is what we have now. Otherwise you run the risk
of throwing millions of errors, which is surely not desirable. The
downside to this is until you've hit the limit, you have to evaluate
*all* the deferred constraints, which could take a while, whereas the
current setup will return immediately upon encountering the first
constraint error.

> My testing shows that the use of foreign keys on high velocity single
> transaction loads, can cause easily a 50% reduction in performance. Why
> add to that? What we need to be doing is finding a way to decrease the
> impact of foreign key checks.

I definitely agree here, but this should be independent of how foreign
key failures are handled once they're detected. In other words, what
you're experiencing is the perfomance hit that comes from evaluating
the constraints, not from reporting the errors afterwards.

--
Kevin Brown kevin(at)sysexperts(dot)com