Thank you for reply ! Yes you are right in most of your points.
The code overhead/footprint can be inside preprocessor macros and we can have for development as we normally do a build with debug ON so in production there will be nothing new. Throwing a bunch of false positives to stderr/sqlite3_(hook*) in deep debug mode can be managed by a user script that go through the output and filter then or do other things each use case my require. For example when I asked this question again I've got this error in a "delete" statement with one specific id but that exploded to several possible tables/fields combination and it took me 3 hours to find it. As usual it was silly once you discover it but till then you loose some hairs. Let's have a compile time debug mode in sqlite where things like this and others can make possible find alternative ways to solve everyday problems, (I tried to find a way to do it myself through the sqlite3 sources but I couldn't find my way through the virtual machine with the available documentation). Cheers ! > Tue Apr 05 2016 12:58:07 PM CEST from "R Smith" <rsmith at rsweb.co.za> >Subject: Re: [sqlite] FOREIGN KEY constraint failed > > On 2016/04/04 10:43 PM, Domingo Alvarez Duarte wrote: > >>Thanks for reply ! >> >> I already sent a proposal to Richard to add a pragma "PRAGMA DEBUG_MODE" >>and >> when set throw any kind of error to stderr/sqlite3_(hook) this way ther is >>no >> need to store temporary conditions to show later. >> >> And of course sqlite knows which table/field failed to flag the error, it >> doesn't throw a dice to do it. >> > The thing you are missing, is that there might be thousands of FK > violations throughout a transaction, all of which (or most of which) > might get resolved before the end of the transaction, and as such is > absolutely useless to inspect/record/notify/whatever. > > Let's assume there are one thousand violations, and three of them did > not get resolved, such as violation number 322, no. 567 and no. 828. > If you "ask the user" or the program via API about every one of the 1000 > violations, surely the time-waste will be intense, and even if you can > live with the time-waste, how will the application/user ever know that > violation no. 435, for instance, is going to definitely get resolved so > that it might report back to the API some form of "OK, we can accept > this one" or record for its own purposes the violation to "deal with > later" when in fact at some point it gets resolved without necessarily a > second check and certainly not a second failure to revisit it? > > It is extremely rare that the "Last violation" (number 1000 in our > example above) is going to end up being THE ONE, or even "one of" the > offenders. Perhaps only in cases where there is only 1 FK violation in > the entire scope of the transaction, and those cases are rarest (such as > a single insert/delete) and if it does happen, you already know the > exact item causing violation, so the API to disclose its identity is > superfluous. > > The only way this feature is feasible is keeping a complete list of > violations internally and a mechanism to revisit them marking the > resolve (if any). The mechanism itself will be heavy and the memory > footprint of the list can run into gigabytes easily, even for a mediocre > database, seeing as a transaction updating 10 rows may easily need to > check hundreds of FK constraints and recursed constraints. > > As Mr. Bee pointed out - we see this question asked often, lots of > people would like to have it implemented. This may be true, but that's > simply because, without investigation, the concept/implementation seems > easy to lots of people. It only seems that way though. > > Cheers, > Ryan > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?