On Fri, May 9, 2014 at 4:20 AM, Andrew Moss <curioussq...@googlemail.com>wrote:
> I am really surprised that FTS behaves this way. To my mind this is a bug > in the FTS extension that makes it unusable for many applications. Was > anyone else aware of this problem or made attempts at resolving it? > I suspect the primary use case it was designed and tested for (and in fact the way we use it at my place of employment) was more for "only growing datasets" and less for an environment where stuff is continually being added and deleted. We use SQLite as a "caching data structure" for information read from a proprietary less functional third party database product so as to speed up queries for a session, but the session is discarded when the program is closed. (Trust me, it makes sense for us.) That does not mean that zero consideration was given to your use case, but I suspect (though I have no evidence to back me up) that the test cases for deletion were focused on correct functionality in the face of relatively random insertions and deletions, not for "record leaks" due to future merge failures. In any case, I was not aware of this problem, and can see where (if validated, and it seems reproducible per your directions) it is a problem for some use cases and some cleanup would be useful for those use cases. Regardless, it does seem to "work correctly" though in a suboptimal way per resource usage. Have you tried creating a new database at some point that consists of only the "live active" records in a "leaky" database so as to compare what the size could/should be if the "current data set" had been created with zero deletions of data along the way? -- Scott Robison _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
