Re: [sqlite] Is pragma foreign_key_check fast enough to be used in normal application flow?
On Fri, 24 May 2013 13:47:08 +0200 Clemens Ladisch wrote: > Foreign key checks are not intended to prevent user errors but to > prevent programming errors. In other words, your program is > responsible for preventing the user from trying to delete some X that > is still referenced by some Y (by doing a check before deleting, or, > preferrably, by structuring the UI so that deleting such a X is not > possible). That is not true. In the first place, foreign keys aren't defined in terms of user/programming errors. They're defined, as you know, in terms of referential integrity. How that integrity came to be violated is of no concern to the DBMS. In the second place, the application is fundamentally unable to always prevent integrity violations. No matter how carefully the UI is structured, nothing prevents another process, quite outside the application's control, from deleting the very row that the application's insert will require. That is why the DBMS does the enforcement instead of the application. Yes, SQLite defaults to SERIALIZABLE isolation. The application has the option of SELECTing every affected FK in a transaction before commencing with the INSERT, thereby preventing their deletion by other processes. If all inserts are written that way, concurrency suffers and deadlocks are very difficult to avoid. Even if every RI violation is prevented, PK violations in general cannot be. Better constraint violation messages would be nice. Unfortunately, it's not easily done. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is pragma foreign_key_check fast enough to be used in normal application flow?
To get answers in a similar situation I found useful the EXPLAIN QUERY PLAN command. On Fri, May 24, 2013 at 3:56 PM, kyan wrote: > On Fri, May 24, 2013 at 4:46 PM, Marc L. Allen > wrote: > >> It's exhaustive in that it absolutely verifies if the key exists or not. >> However, it doesn't necessarily do a full database scan. I assume it uses >> available indexes and does a standard lookup on the key. >> >> So, it still might be fast enough for what you want (though I missed the >> beginning of the thread). >> >> -Original Message- >> From: sqlite-users-boun...@sqlite.org [mailto: >> sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp >> >> On Fri, May 24, 2013 at 7:07 AM, kyan wrote: >> >> It is exhaustive >> > > Thank you both for your answers. > > Since I am writing code for an application server that connects to > databases of different database vendors used by other development teams I > have no way of knowing anything about the underlying database, so I will > not take any chances. > > -- > Constantine Yannakopoulos > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is pragma foreign_key_check fast enough to be used in normal application flow?
On Fri, May 24, 2013 at 4:46 PM, Marc L. Allen wrote: > It's exhaustive in that it absolutely verifies if the key exists or not. > However, it doesn't necessarily do a full database scan. I assume it uses > available indexes and does a standard lookup on the key. > > So, it still might be fast enough for what you want (though I missed the > beginning of the thread). > > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto: > sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp > > On Fri, May 24, 2013 at 7:07 AM, kyan wrote: > > It is exhaustive > Thank you both for your answers. Since I am writing code for an application server that connects to databases of different database vendors used by other development teams I have no way of knowing anything about the underlying database, so I will not take any chances. -- Constantine Yannakopoulos ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is pragma foreign_key_check fast enough to be used in normal application flow?
It's exhaustive in that it absolutely verifies if the key exists or not. However, it doesn't necessarily do a full database scan. I assume it uses available indexes and does a standard lookup on the key. So, it still might be fast enough for what you want (though I missed the beginning of the thread). -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Friday, May 24, 2013 9:40 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Is pragma foreign_key_check fast enough to be used in normal application flow? On Fri, May 24, 2013 at 7:07 AM, kyan wrote: > > So my question is, is [the foreign_key_check] pragma exhaustive or is > it somehow optimised so that it does not always perform a full > database scan -e.g. by means of some internal per-transaction FK > violation counter or list? Because if it is optimised and thus fast > enough then I suppose I can try to use it for the purpose I have > described. > It is exhaustive -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is pragma foreign_key_check fast enough to be used in normal application flow?
On Fri, May 24, 2013 at 7:07 AM, kyan wrote: > > So my question is, is [the foreign_key_check] pragma exhaustive or is it > somehow optimised so > that it does not always perform a full database scan -e.g. by means of some > internal per-transaction FK violation counter or list? Because if it is > optimised and thus fast enough then I suppose I can try to use it for the > purpose I have described. > It is exhaustive -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is pragma foreign_key_check fast enough to be used in normal application flow?
On Fri, May 24, 2013 at 3:56 PM, Clemens Ladisch wrote: > This is why we have transactions. > Perhaps but I would like to refrain from arguing on this at this time. Instead I would like an answer to my original question if possible. TIA. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is pragma foreign_key_check fast enough to be used in normal application flow?
kyan wrote: > Apart from performance, there is also the issue of concurrency, where the > program does the query , decides it is OK to make the change but in the > meantime somebody else has made another change that invalidates the > program's change, for instance inserted a detail record to the master the > user is trying to delete. This is why we have transactions. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is pragma foreign_key_check fast enough to be used in normal application flow?
On Fri, May 24, 2013 at 2:47 PM, Clemens Ladisch wrote: > Foreign key checks are not intended to prevent user errors but to > prevent programming errors. In other words, your program is responsible > for preventing the user from trying to delete some X that is still > referenced by some Y (by doing a check before deleting, or, preferrably, > by structuring the UI so that deleting such a X is not possible). > I am sorry but I disagree. If my program was to prevent the FK violation it would have to do a query; infact it would be the exact same query the SQLite engine does in order to enforce the FK. This is clearly redundant. Apart from performance, there is also the issue of concurrency, where the program does the query , decides it is OK to make the change but in the meantime somebody else has made another change that invalidates the program's change, for instance inserted a detail record to the master the user is trying to delete. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is pragma foreign_key_check fast enough to be used in normal application flow?
kyan wrote: > Due to its implementation of foreign keys and their deferring, SQLite does > not provide any useful information when a foreign key is violated; it gives > a not very helpful "Foreign key constraint failed" message instead. This is > a problem because an application developer cannot produce a proper message > to a user when a FK is violated, for instance "You cannot delete X because > it is connected to Ys" because they don't know and can't somehow deduce X > and Y. Foreign key checks are not intended to prevent user errors but to prevent programming errors. In other words, your program is responsible for preventing the user from trying to delete some X that is still referenced by some Y (by doing a check before deleting, or, preferrably, by structuring the UI so that deleting such a X is not possible). Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is pragma foreign_key_check fast enough to be used in normal application flow?
Hello Dr. Hipp and all, Due to its implementation of foreign keys and their deferring, SQLite does not provide any useful information when a foreign key is violated; it gives a not very helpful "Foreign key constraint failed" message instead. This is a problem because an application developer cannot produce a proper message to a user when a FK is violated, for instance "You cannot delete X because it is connected to Ys" because they don't know and can't somehow deduce X and Y. It occurred to me that this may be adequately worked around if foreign keys are deferred, using the "pragma foreign_key_check" command. The idea is that when a SQLITE_CONSTRAINT error occurs at commit, the application code can catch it and use the foreign_key_check pragma to get information about the FK violation(s) that caused the commit error so that a meaningful user message can be produced before doing a rollback. But this would impose a serious performance penalty if the check is "exhaustive" meaning that ALL records in the database are checked against ALL foreign key constraints. So my question is, is this pragma exhaustive or is it somehow optimised so that it does not always perform a full database scan -e.g. by means of some internal per-transaction FK violation counter or list? Because if it is optimised and thus fast enough then I suppose I can try to use it for the purpose I have described. TIA. -- Constantine Yannakopoulos ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
