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 [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
