Re: [sqlite] LEFT JOIN: ON vs. WHERE
This is exactly the kind of advice I was looking for. Thanks again! Ben > On Oct 27, 2019, at 4:04 PM, Simon Slavin wrote: > > On 27 Oct 2019, at 10:45pm, Benjamin Asher wrote: > >> Query A >> >> SELECT * FROM tab1 LEFT JOIN tab2 ON tab2.x='constant' WHERE >> tab1.x='constant’; >> >> Query B >> >> SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=tab2.x WHERE tab1.x=‘constant' > > Your use of JOIN here is extremely usual. Depending on your data, you either > don't need it, or it does nothing. > > Suppose you have many rows in tab1 where x='constant' and also many rows in > tab2 where x='constant'. What are you expecting the engine to do with them ? > Should it match one tab1 row with one tab2 row ? Or should it generate a > set of every possible combination ? This tells you what your ON clause > should be. > > Generally, a constant belongs in a WHERE clause. To answer your original > question > >> Is there an advantage to putting WHERE-type filtering in the ON constraint >> vs leaving it in the WHERE clause for LEFT JOINs? > > SQLite reads the entire statement and does things to figure out the best way > to execute it given the indexes available. The kind of deconstruction you > did to the query in your head, happens inside the SQL engine. So we cannot > give you an answer "always put it in ON" or "always put it in WHERE". > Instead we say "Don't make your query look weird just to save a millisecond. > Have your query make sense, so someone who reads it understands what you are > doing.". > > If you want to worry about speed or efficiency, worry about making useful > indexes for your tables instead of exactly how to phrase your query. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Count Rows Inserted into FTS5 Index w/ External Content Table
Interesting! I appreciate the detailed response. I don't think the shadow table digging fits our risk profile exactly :), but it's interesting to know where to look if we want to check ourselves. I realized after rereading all of this that ultimately we want to keep track of the max rowid inserted into the index, so I think we'll just track that separately on our own. Thanks again! Ben > On Oct 20, 2019, at 1:45 AM, Dan Kennedy wrote: > > >> On 19/10/62 06:31, Ben Asher wrote: >> Hello! I'm trying to write some code to keep an external content table in >> sync with the index. To do this, I need to be able to get some state about >> the index: either how many rows have been inserted so far or the max rowid >> that has been inserted into the index. However, if I try to run queries >> (where "fts" is the name of the FTS5 virtual table) like "SELECT COUNT(*) >> FROM fts" or "SELECT MAX(rowid) FROM fts", the result always ends up being >> an answer as if I had run those queries on the external content table. Is >> there some other way I can query the state in this case? > > You can, but it's not quite as easy as it could be. Assuming you're not using > the "columnsize=0" option, the xRowCount() API, which is only available from > within an auxiliary function returns the value you want: > > https://sqlite.org/fts5.html#xRowCount > > The xRowCount() will just fall back to the "SELECT count(*)..." method, which > will report the number of rows in the external content table, not the number > of rows that have been added to the index. > > So if you're prepared to write an auxiliary fts5 function in C that invokes > the xRowCount() API and returns its value, you could do something like: > > SELECT row_count(text) FROM text LIMIT 1; > > Assuming your fts5 table is named "text". Or, if you include the "matchinfo" > demo code in your app from here: > > https://sqlite.org/src/artifact/08c11ec968148d4c > > in your build: > > SELECT matchinfo(text, 'n') FROM text LIMIT 1; > > Both of these will only work if there is at least one row in the external > content table (as otherwise the query will return zero rows). If your > external content table is sometimes empty, you'll have to figure out a > full-text query that always returns at least one row to use. > > Another way to go, if you're a risk-taking sort, is to read the same value > directly from the fts5 shadow tables. The query: > > SELECT block FROM test_data WHERE id=1; > > returns a blob that consists of a series of SQLite varints. The first of > those varints is the number of rows in the index. Again, assuming your fts5 > table is named "test". > > This isn't actually a public interface, so you might get burned if it changes > at some point. I think that's pretty unlikely, but no promises! > > On consideration, I'd probably go for the direct query on the test_data > table. Not least because if you use the xRowCount() API from a non-full-text > query you will need this fix, which won't be released until 3.31.0: > > https://sqlite.org/src/info/b528bdcd45db1b78 > > But have a test in place to ensure it doesn't break when you upgrade SQLite. > And if you can, build SQLite directly into the app (almost always a good > idea), don't use the system version. > > Good luck, > > Dan. > > > > > >> >> Thanks! >> >> Ben >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Schema updates across threads in WAL & multithread mode
Hi D. Richard Hipp. Sorry for the late reply. I had some email settings wrong somewhere, and I didn't realize I had a reply until I remembered this and checked the archives months later. You had written: >> >> Is there something we need to do proactively to ensure that schema update >> appears immediately from other threads? >> > >When a database connection has a read transaction open, it continues >to see a snapshot of the database as it existed when the read >transaction was first opened. Outside changes to the database, >including schema changes, are invisible to the connection holding the >transaction. This is the "I" in "ACID". > >As soon as you release the read transaction and start another, all >changes will immediately become visible. > >If you are not deliberately holding a read transaction open, perhaps >you are doing so accidentally by failing to sqlite3_reset() or >sqlite3_finalize() a prepared statement. You can perhaps figure out >which statement that is by running: > > SELECT sql FROM sqlite_stmt WHERE busy; That is very interesting. We definitely don't expect a read transaction to be open at that point. This is super helpful. I'll check on this next week. Thank you! Ben On Fri, Aug 16, 2019 at 12:49 PM Ben Asher wrote: > To clarify, we add a column on our writer connection, and then "SELECT * > FROM table" on the reader connection does not include the column that was > added. > > Ben > > On Fri, Aug 16, 2019 at 11:32 AM Ben Asher wrote: > >> Hi folks! We're running (sqlite 3.27.2) into an issue where we make a >> schema update (adding a column to a table) on our writer connection, but >> then the schema update isn't immediately available on the read-only >> connections that we use on other threads, which causes a crash in our >> application (app expects the column to exist at that point). I've verified >> that the column does indeed get added, and everything works fine after >> restarting the application (i.e. all connections loaded fresh pickup the >> schema update). >> >> Is there something we need to do proactively to ensure that schema update >> appears immediately from other threads? >> >> Some notes about our setup: >> >> sqlite 3.27.2 >> Using multithread mode (SQLITE_OPEN_NOMUTEX) >> Using WAL mode >> >> Thanks! >> >> Ben >> > > > -- > Ben > -- Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Schema updates across threads in WAL & multithread mode
Hi Simon. Sorry for the late reply. I had some email settings wrong somewhere, and I didn't realize I had a reply until I remembered this and checked the archives months later. You had written: >Can I ask the maximum number of columns you expect to exist in that table ? I'm working up to trying to convince you to add a row to something instead, but I want to make sure you're doing what I think you're doing. > >Other people may be able to answer your question. It's a small number of columns– less than 10. The table already has data, and we added the column with a default value. Thanks! Ben On Fri, Aug 16, 2019 at 12:49 PM Ben Asher wrote: > To clarify, we add a column on our writer connection, and then "SELECT * > FROM table" on the reader connection does not include the column that was > added. > > Ben > > On Fri, Aug 16, 2019 at 11:32 AM Ben Asher wrote: > >> Hi folks! We're running (sqlite 3.27.2) into an issue where we make a >> schema update (adding a column to a table) on our writer connection, but >> then the schema update isn't immediately available on the read-only >> connections that we use on other threads, which causes a crash in our >> application (app expects the column to exist at that point). I've verified >> that the column does indeed get added, and everything works fine after >> restarting the application (i.e. all connections loaded fresh pickup the >> schema update). >> >> Is there something we need to do proactively to ensure that schema update >> appears immediately from other threads? >> >> Some notes about our setup: >> >> sqlite 3.27.2 >> Using multithread mode (SQLITE_OPEN_NOMUTEX) >> Using WAL mode >> >> Thanks! >> >> Ben >> > > > -- > Ben > -- Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Schema updates across threads in WAL & multithread mode
Hi José. Sorry for the late reply. I had some email settings wrong somewhere, and I didn't realize I had a reply until I remembered this and checked the archives months later. You had asked: > Are you using BEGIN and END before and after the schema update? Yes that's correct. We are doing the schema updates inside of an explicit transaction. Thanks! Ben On Fri, Aug 16, 2019 at 12:49 PM Ben Asher wrote: > To clarify, we add a column on our writer connection, and then "SELECT * > FROM table" on the reader connection does not include the column that was > added. > > Ben > > On Fri, Aug 16, 2019 at 11:32 AM Ben Asher wrote: > >> Hi folks! We're running (sqlite 3.27.2) into an issue where we make a >> schema update (adding a column to a table) on our writer connection, but >> then the schema update isn't immediately available on the read-only >> connections that we use on other threads, which causes a crash in our >> application (app expects the column to exist at that point). I've verified >> that the column does indeed get added, and everything works fine after >> restarting the application (i.e. all connections loaded fresh pickup the >> schema update). >> >> Is there something we need to do proactively to ensure that schema update >> appears immediately from other threads? >> >> Some notes about our setup: >> >> sqlite 3.27.2 >> Using multithread mode (SQLITE_OPEN_NOMUTEX) >> Using WAL mode >> >> Thanks! >> >> Ben >> > > > -- > Ben > -- Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Count Rows Inserted into FTS5 Index w/ External Content Table
I should have included this in the first place. Here are the queries used
to setup this table and an example:
CREATE TABLE text (text TEXT NONNULL);
CREATE VIRTUAL TABLE text_fts_index USING fts5(text, content=text,
content_rowid=rowid, prefix='2 3 4 5 6 7 8 9 10', tokenize='unicode61');
INSERT INTO text VALUES ('hello');
-- I was hoping the following would return 0, but it returns 1, presumably
because it's getting the answer from the external content table
SELECT COUNT(content_rowid) FROM text_fts_index;
Thanks again!
Ben
On Fri, Oct 18, 2019 at 4:31 PM Ben Asher wrote:
> Hello! I'm trying to write some code to keep an external content table in
> sync with the index. To do this, I need to be able to get some state about
> the index: either how many rows have been inserted so far or the max rowid
> that has been inserted into the index. However, if I try to run queries
> (where "fts" is the name of the FTS5 virtual table) like "SELECT COUNT(*)
> FROM fts" or "SELECT MAX(rowid) FROM fts", the result always ends up being
> an answer as if I had run those queries on the external content table. Is
> there some other way I can query the state in this case?
>
> Thanks!
>
> Ben
>
--
Ben
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Count Rows Inserted into FTS5 Index w/ External Content Table
Hello! I'm trying to write some code to keep an external content table in sync with the index. To do this, I need to be able to get some state about the index: either how many rows have been inserted so far or the max rowid that has been inserted into the index. However, if I try to run queries (where "fts" is the name of the FTS5 virtual table) like "SELECT COUNT(*) FROM fts" or "SELECT MAX(rowid) FROM fts", the result always ends up being an answer as if I had run those queries on the external content table. Is there some other way I can query the state in this case? Thanks! Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Schema updates across threads in WAL & multithread mode
To clarify, we add a column on our writer connection, and then "SELECT * FROM table" on the reader connection does not include the column that was added. Ben On Fri, Aug 16, 2019 at 11:32 AM Ben Asher wrote: > Hi folks! We're running (sqlite 3.27.2) into an issue where we make a > schema update (adding a column to a table) on our writer connection, but > then the schema update isn't immediately available on the read-only > connections that we use on other threads, which causes a crash in our > application (app expects the column to exist at that point). I've verified > that the column does indeed get added, and everything works fine after > restarting the application (i.e. all connections loaded fresh pickup the > schema update). > > Is there something we need to do proactively to ensure that schema update > appears immediately from other threads? > > Some notes about our setup: > > sqlite 3.27.2 > Using multithread mode (SQLITE_OPEN_NOMUTEX) > Using WAL mode > > Thanks! > > Ben > -- Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Schema updates across threads in WAL & multithread mode
Hi folks! We're running (sqlite 3.27.2) into an issue where we make a schema update (adding a column to a table) on our writer connection, but then the schema update isn't immediately available on the read-only connections that we use on other threads, which causes a crash in our application (app expects the column to exist at that point). I've verified that the column does indeed get added, and everything works fine after restarting the application (i.e. all connections loaded fresh pickup the schema update). Is there something we need to do proactively to ensure that schema update appears immediately from other threads? Some notes about our setup: sqlite 3.27.2 Using multithread mode (SQLITE_OPEN_NOMUTEX) Using WAL mode Thanks! Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ISO8601 vs Numeric Timestamp for Date Storage
Thanks all! Super helpful. Ben On Wed, Feb 6, 2019 at 11:55 AM Ben Asher wrote: > Hi there! We're having a debate at my company about date storage in > SQLite. SQLite has builtin support for ISO8601 in its date functions, so > some folks have started storing dates as ISO8601 SQLite-compatible date > strings. Are there pitfalls to storing dates this way compared to a unix > timestamp? I'm curious to know if anyone has experience and would highly > recommend sticking to one or the other for a particular reason. I'd also be > grateful if anyone could point me to any articles exploring this subject. > > Thanks! > > Ben > -- Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ISO8601 vs Numeric Timestamp for Date Storage
Hi there! We're having a debate at my company about date storage in SQLite. SQLite has builtin support for ISO8601 in its date functions, so some folks have started storing dates as ISO8601 SQLite-compatible date strings. Are there pitfalls to storing dates this way compared to a unix timestamp? I'm curious to know if anyone has experience and would highly recommend sticking to one or the other for a particular reason. I'd also be grateful if anyone could point me to any articles exploring this subject. Thanks! Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Blob and Text columns: what's the difference?
Ah great to know. Thanks! Ben On Tue, Aug 28, 2018 at 7:29 AM Richard Hipp wrote: > On 8/28/18, Ben Asher wrote: > > I seem to remember that BLOBs cannot be indexed. I can’t find > documentation > > on that though. Does anyone else recall the same thing and have a link, > or > > maybe someone can correct me? > > You might be remembering the limitations of Oracle. Other database > engines might also have this limitation too. But not SQLite. SQLite > allows any column to be indexed, including BLOB colums. > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Blob and Text columns: what's the difference?
I seem to remember that BLOBs cannot be indexed. I can’t find documentation on that though. Does anyone else recall the same thing and have a link, or maybe someone can correct me? Ben On Tue, Aug 28, 2018 at 6:50 AM Tim Streater wrote: > What is actually the difference between a column declared as TEXT and one > declared as BLOB in an SQLite database? What does SQLite do to textual data > that I ask it to put into a TEXT column? How does it know not to do that if > I want to send some binary data to a Text column? > > The reason I'm interested is that I have a table with a TEXT column. For > backwards compatibility reasons, I'd like not to change the column's type > or even add another column with BLOB type. Up to now I've assumed that the > data to go in the column was all ASCII or perhaps UTF-8. Now it seems that > it's legal for it to be a mixture of encodings. So I want to treat it as > binary and be able to just use the existing TEXT column. Anything I need to > look out for? > > > -- > Cheers -- Tim > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Return a column's contents to the free list
Oh interesting! Is that optimization documented anywhere (website, code, or otherwise)? Ben On Sat, Jun 9, 2018 at 10:36 AM Simon Slavin wrote: > On 9 Jun 2018, at 4:28pm, Jay Kreibich wrote: > > > If a full VACUUM is not feasible, you can simply copy the table after > the column is removed. > > Note that SQLite has special optimization for the command > > DELETE FROM oldTable > > with no "WHERE" clause. So the best way to do it would be > > BEGIN; > CREATE tempTable AS SELECT * FROM oldTable; > DELETE FROM oldTable; > INSERT INTO oldTable SELECT * FROM tempTable; > COMMIT; > > Of course, if this is the master table for a foreign key, you have > additional problems. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Return a column's contents to the free list
Good tip on sqlite_analyze thanks! In this particular case, it's one large table. The column we're obsoleting is relatively small (20-30 bytes per row), but for a given instance of this table, we could be freeing upwards of 20MB of data (across all rows in the table for this column). Therefore, copying this table to remove the small column isn't worth the trouble relative to the time and disk space required to do it, compared to just living with the empty column. Alternative, the ability to rename, and therefore repurpose, the column would also be welcome. I understand there are constraints there too. Ben On Sat, Jun 9, 2018 at 10:29 AM Jay Kreibich wrote: > > > On Jun 9, 2018, at 10:16 AM, Ben Asher wrote: > > > > To further clarify on VACUUM, we actually want to actively avoid this > > because it's expensive, and we'd be running these on small devices like > > iPhones and iPads with large-ish DBs. > > If a full VACUUM is not feasible, you can simply copy the table after the > column is removed. This may or may not be feasible, depending on if you > have a small number of large tables, or a large number of medium/small > tables. For smaller tables you can clear the column, then create a copy of > the table with a new name, use something like INSERT INTO…SELECT to copy > the data, then drop the old table, and finally rename the new one back to > the old name. You’ll want to do all that in a transaction which will lock > the database, but the individual tables are not huge that might be much > faster than a VACUUM. Of course, it requires two copies of the table to > exist in the database at the same time, but when you’re done, the new table > will be smaller and all the pages used by the old table will go into the > free list. > > I’d also suggest you get a copy of sqlite_analyze running, as it will tell > you stats about page usage and such. That will give you some insight into > if this process is worth the effort. > > -j > > > > > > > We'd also expect lots more writes in > > the future (after obsoleting the column), so it seems preferable to focus > > on making sure that the space that was used by the column is just > returned > > to be used by SQLite for those future writes. > > > > Ben > > > > On Sat, Jun 9, 2018 at 10:13 AM Simon Slavin > wrote: > > > >> On 9 Jun 2018, at 3:52pm, Ben Asher wrote: > >> > >>> we would like to make sure that the space occupied by the columns > >> contents is zeroed and returned to SQLite's free list ( > >> https://www.sqlite.org/faq.html#q12) to be re-used. Is setting the > >> column's contents to "" (for a TEXT column > >>> specifically) sufficient to do that? > >> > >> I recommend you use NULL instead: > >> > >> UPDATE myTable SET deadColumn = NULL WHERE [whatever]; > >> VACUUM; > >> > >> I have three reasons for preferring NULL: > >> > >> 1) NULL takes the minimum amount of space to store > >> 2) handing NULLs is much faster than handling strings > >> 3) NULL in SQL literally means "value missing" or "value unknown". > >> > >> [later seeing a followup] > >> > >> If you can't use NULL, use the integer 0. Even if the column has TEXT > >> affiliation. The values NULL, 0 and 1 each take no storage (they are > >> special value types) and all three values are handled very efficiently. > >> > >> VACUUM will do the actual returning of space and shrink the file size. > >> Doing it with VACUUM does it all at once at a time of your controlling > >> rather than slowing down subsequent operations. > >> > >> Simon. > >> ___ > >> sqlite-users mailing list > >> sqlite-users@mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >> > > > > > > -- > > Ben > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Return a column's contents to the free list
I see. Thanks for the detailed explanation! Given how expensive a full DB VACUUM can be (also seems impossible to determine how expensive without running VACUUM), could a table-level VACUUM make sense for this scenario? One could even make it a command as specific as CLEAR COLUMN, which has this side effect. But actually, I think that what you're saying is that when setting the column to 0, if each row fits into a page, there's nothing to do. If there were spill pages being used by that row, then those will be recovered? If I understand correctly then, the only benefit is recovering those spill pages if possible, and that will already happen by 0'ing the column if possible. If that's the case, I think I can be happy that some space will be recovered if possible, short of doing the full VACUUM. Ben On Sat, Jun 9, 2018 at 10:17 AM Jay Kreibich wrote: > > > On Jun 9, 2018, at 10:04 AM, Ben Asher wrote: > > > > Right we'll prefer NULL, but some columns are NON NULL. In this scenario, > > we're not worried about returning space to the OS, but good point out > > VACUUM. Thanks! > > Without digging through some detailed docs, I’m pretty sure empty string > and NULL require the same amount of storage space. If not, the difference > is maybe one byte. > > As for freeing the pages, the issue is that row values are stored in leaf > pages, kind of like a big array or list, packed together— not only are the > values in a row packed, the rows themselves are them packed into pages. If > you just set existing column value to NULL (or empty string) it is going to > re-pack and re-write the values in that page, but it isn’t going to > globally recover the space used by that column because it is intermixed > with all the other columns. The only case when a single column change > would trigger page recovery is if the value in that column is so big the > row overflows a single page and requires spill pages. Since a column clear > generally won’t free whole pages, there is nothing to put on the free list. > > The point of the VACUUM is not to release free pages back to the OS, but > to force the database to re-write (and therefore re-pack) all the rows, so > that the whole table will require fewer pages, and whatever is left over > can be freed (or in the case of a VACUUM, never re-written). > > It is different when you delete rows, since the the whole row record is > deleted and it tends to free up bigger chunks. But clearing the data out > of a column only clears values in the middle of row records, so it is > unlikely to free up pages by itself. > > -j > > > > > Ben > > > > On Sat, Jun 9, 2018 at 10:01 AM Jay Kreibich wrote: > > > >> > >>> On Jun 9, 2018, at 9:52 AM, Ben Asher wrote: > >>> > >>> Hi! I've read a lot of discussion about the constraints related to why > >>> SQLite doesn't have destructive column commands (MODIFY, RENAME, DROP, > >>> etc.). Despite that, we still have situations where our data model > >> changes, > >>> and a column becomes obsolete. Given the constraints, we've decided to > >>> create a column "graveyard" at the application level: basically a list > of > >>> columns that exist but are obsolete. While we cannot drop columns, we > >> would > >>> like to make sure that the space occupied by the columns contents is > >> zeroed > >>> and returned to SQLite's free list ( > https://www.sqlite.org/faq.html#q12) > >> to > >>> be re-used. Is setting the column's contents to "" (for a TEXT column > >>> specifically) sufficient to do that? > >> > >> That or NULL. You also have to vacuum the database to re-pack the > >> database pages and actually recover the disk space. Some of the space > may > >> be recovered if the TEXT records were really long (and required spill > >> pages) but if most of the column were a dozen bytes or so (more typical) > >> you’ll want to do a VACUUM. > >> > >> -j > >> > >> ___ > >> sqlite-users mailing list > >> sqlite-users@mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >> > > > > > > -- > > Ben > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Return a column's contents to the free list
That would be great! The 0/1 thing feels like a trick that, while I'm glad I learned it, could be abstracted away by SQLite. Ben On Sat, Jun 9, 2018 at 10:16 AM Simon Slavin wrote: > Reading the thread suggests to me that a future version of SQLite might > profit from a special value-type for the empty string, like there are > already special column types for 0 and 1. I wonder what proportion of > string values stored in SQLite databases are "". > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Return a column's contents to the free list
Thanks for that helpful tip on 0/1! We'll definitely use those then. To further clarify on VACUUM, we actually want to actively avoid this because it's expensive, and we'd be running these on small devices like iPhones and iPads with large-ish DBs. We'd also expect lots more writes in the future (after obsoleting the column), so it seems preferable to focus on making sure that the space that was used by the column is just returned to be used by SQLite for those future writes. Ben On Sat, Jun 9, 2018 at 10:13 AM Simon Slavin wrote: > On 9 Jun 2018, at 3:52pm, Ben Asher wrote: > > > we would like to make sure that the space occupied by the columns > contents is zeroed and returned to SQLite's free list ( > https://www.sqlite.org/faq.html#q12) to be re-used. Is setting the > column's contents to "" (for a TEXT column > > specifically) sufficient to do that? > > I recommend you use NULL instead: > > UPDATE myTable SET deadColumn = NULL WHERE [whatever]; > VACUUM; > > I have three reasons for preferring NULL: > > 1) NULL takes the minimum amount of space to store > 2) handing NULLs is much faster than handling strings > 3) NULL in SQL literally means "value missing" or "value unknown". > > [later seeing a followup] > > If you can't use NULL, use the integer 0. Even if the column has TEXT > affiliation. The values NULL, 0 and 1 each take no storage (they are > special value types) and all three values are handled very efficiently. > > VACUUM will do the actual returning of space and shrink the file size. > Doing it with VACUUM does it all at once at a time of your controlling > rather than slowing down subsequent operations. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Return a column's contents to the free list
And for TEXT records, it sounds like this isn't always the case and is dependent on original record size? Ben On Sat, Jun 9, 2018 at 10:04 AM Ben Asher wrote: > Right we'll prefer NULL, but some columns are NON NULL. In this scenario, > we're not worried about returning space to the OS, but good point out > VACUUM. Thanks! > > So for NON NULL columns, setting contents to empty should return the freed > space to internal free list? > > Ben > > On Sat, Jun 9, 2018 at 10:01 AM Jay Kreibich wrote: > >> >> > On Jun 9, 2018, at 9:52 AM, Ben Asher wrote: >> > >> > Hi! I've read a lot of discussion about the constraints related to why >> > SQLite doesn't have destructive column commands (MODIFY, RENAME, DROP, >> > etc.). Despite that, we still have situations where our data model >> changes, >> > and a column becomes obsolete. Given the constraints, we've decided to >> > create a column "graveyard" at the application level: basically a list >> of >> > columns that exist but are obsolete. While we cannot drop columns, we >> would >> > like to make sure that the space occupied by the columns contents is >> zeroed >> > and returned to SQLite's free list (https://www.sqlite.org/faq.html#q12) >> to >> > be re-used. Is setting the column's contents to "" (for a TEXT column >> > specifically) sufficient to do that? >> >> That or NULL. You also have to vacuum the database to re-pack the >> database pages and actually recover the disk space. Some of the space may >> be recovered if the TEXT records were really long (and required spill >> pages) but if most of the column were a dozen bytes or so (more typical) >> you’ll want to do a VACUUM. >> >> -j >> >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > > > -- > Ben > -- Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Return a column's contents to the free list
Right we'll prefer NULL, but some columns are NON NULL. In this scenario, we're not worried about returning space to the OS, but good point out VACUUM. Thanks! So for NON NULL columns, setting contents to empty should return the freed space to internal free list? Ben On Sat, Jun 9, 2018 at 10:01 AM Jay Kreibich wrote: > > > On Jun 9, 2018, at 9:52 AM, Ben Asher wrote: > > > > Hi! I've read a lot of discussion about the constraints related to why > > SQLite doesn't have destructive column commands (MODIFY, RENAME, DROP, > > etc.). Despite that, we still have situations where our data model > changes, > > and a column becomes obsolete. Given the constraints, we've decided to > > create a column "graveyard" at the application level: basically a list of > > columns that exist but are obsolete. While we cannot drop columns, we > would > > like to make sure that the space occupied by the columns contents is > zeroed > > and returned to SQLite's free list (https://www.sqlite.org/faq.html#q12) > to > > be re-used. Is setting the column's contents to "" (for a TEXT column > > specifically) sufficient to do that? > > That or NULL. You also have to vacuum the database to re-pack the > database pages and actually recover the disk space. Some of the space may > be recovered if the TEXT records were really long (and required spill > pages) but if most of the column were a dozen bytes or so (more typical) > you’ll want to do a VACUUM. > > -j > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Return a column's contents to the free list
Hi! I've read a lot of discussion about the constraints related to why SQLite doesn't have destructive column commands (MODIFY, RENAME, DROP, etc.). Despite that, we still have situations where our data model changes, and a column becomes obsolete. Given the constraints, we've decided to create a column "graveyard" at the application level: basically a list of columns that exist but are obsolete. While we cannot drop columns, we would like to make sure that the space occupied by the columns contents is zeroed and returned to SQLite's free list (https://www.sqlite.org/faq.html#q12) to be re-used. Is setting the column's contents to "" (for a TEXT column specifically) sufficient to do that? Thanks! Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proper parameter usage in FTS5 queries
I see. Yep that clears things up. Thanks again!
Ben
On Wed, May 23, 2018 at 11:36 AM, Dan Kennedy wrote:
> On 05/24/2018 01:27 AM, Ben Asher wrote:
>
>> Also one other question: with this method of using a parameter in the
>> MATCH
>> expression, do I still need to manually escape double quotes in the string
>> bound to the parameter by replacing them with a pair of double quotes (for
>> FTS5)?
>>
>
> Maybe. What do you need this to do?
>
> If you execute:
>
> SELECT rowid FROM test_fts_index WHERE text MATCH '"' || ? || '"*'
>
> then the core evaluates the SQL expression ('"' || ? || '"*') and passes
> the results to FTS5. So if you bind the 5 byte string [a b c] to the
> variable, then your query is equivalent to:
>
> SELECT rowid FROM test_fts_index WHERE text MATCH '"a b c"*';
>
> If you bind the 3 byte string [a"c], then your query is as:
>
> SELECT rowid FROM test_fts_index WHERE text MATCH '"a"b"c"*';
>
> and FTS5 reports an error.
>
> Dan.
>
>
>
>
>
> I'm not seeing any errors, but now I'm second guessing myself.
>>
>> Ben
>>
>> On Wed, May 23, 2018 at 10:46 AM, Ben Asher wrote:
>>
>> Ah I see. Clever! The final solution ended up being:
>>>
>>> SELECT rowid FROM test_fts_index WHERE text MATCH '"' || ? || '"*'
>>>
>>> with || on either side of the ?. Does that sound right? Without that
>>> extra
>>> ||, sqlite returned a syntax error (near ?).
>>>
>>> As follow up, it'd be great to see this solution documented somewhere on
>>> the FTS5 page: https://sqlite.org/fts5.html. I'm glad there's a way to
>>> accomplish this, but the solution is a bit more clever than I would have
>>> come up with on my own.
>>>
>>> Thanks again for your help!
>>>
>>> Ben
>>>
>>> On Wed, May 23, 2018 at 1:15 AM, Dan Kennedy
>>> wrote:
>>>
>>> On 05/23/2018 06:02 AM, Ben Asher wrote:
>>>>
>>>> Hi there! I'm working with a FTS5 query like this:
>>>>>
>>>>> SELECT rowid FROM text_fts_index WHERE text MATCH ?
>>>>>
>>>>> The parameter is something like this: "user input"*
>>>>>
>>>>> The idea here is to do a MATCH prefix-type query in FTS5. The problem
>>>>> with
>>>>> this query is that it breaks if the user input contains double quotes.
>>>>> What
>>>>> I want to do is something like this:
>>>>>
>>>>> SELECT rowid FROM text_fts_index WHERE text MATCH "?"*
>>>>>
>>>>> Maybe:
>>>>
>>>>SELECT rowid FROM test_fts_index WHERE text MATCH '"' ? || '"*'
>>>>
>>>> Dan.
>>>>
>>>>
>>>> This 2nd version of the query doesn't appear to work though because the
>>>>> ?
>>>>> is escaped in the quotes and doesn't appear to be recognized as a
>>>>> variable.
>>>>> Is there a way I'm missing to do this kind of MATCH prefix query and
>>>>> only
>>>>> escape the user input?
>>>>>
>>>>> Thanks!
>>>>>
>>>>> Ben
>>>>> ___
>>>>> sqlite-users mailing list
>>>>> sqlite-users@mailinglists.sqlite.org
>>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>>>
>>>>>
>>>> ___
>>>> sqlite-users mailing list
>>>> sqlite-users@mailinglists.sqlite.org
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>>>
>>>
>>> --
>>> Ben
>>>
>>>
>>
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
Ben
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tsan Bug in WAL mode
Got it. I'll add that one to our blacklist as well then. Thanks again!
Ben
On Wed, May 23, 2018 at 11:29 AM, Dan Kennedy wrote:
> On 05/24/2018 01:01 AM, Ben Asher wrote:
>
>> I see. I think that makes sense! I've gone ahead and added these 2
>> functions to our local Tsan blacklist then. In researching this further, I
>> found one more Tsan issue here in walTryBeginRead:
>>
>> - One thread is here:
>> http://www.sqlite.org/src/artifact/aa9cffc7a2bad?ln=2558
>> - One thread is here:
>> http://www.sqlite.org/src/artifact/aa9cffc7a2bad?ln=2571
>>
>> Is this one expected as well? If so, I'll also add this function to our
>> blacklist.
>>
>
> It is expected, yes.
>
> The writer at line 2571 is holding an EXCLUSIVE posix lock when it writes
> the aReadMark[] entry. The reader at 2558 is not holding any lock, but
> later on in the same function it takes a SHARED lock and checks that the
> value is as it was earlier. And retries everything if it is not.
>
> http://www.sqlite.org/src/artifact/aa9cffc7a2bad?2589..2591
>
> Dan.
>
>
>
>
>
>
>
>> Thanks again!
>>
>> Ben
>>
>>
>> On Wed, May 23, 2018 at 1:24 AM, Dan Kennedy
>> wrote:
>>
>> On 05/23/2018 06:21 AM, Ben Asher wrote:
>>>
>>> Hi there! I believe I've found a SQLite bug that triggers TSAN. I'm
>>>> hoping
>>>> to either confirm this bug or learn what I'm doing wrong. Some
>>>> background:
>>>> in our code, we make sure to synchronize writes to our database such
>>>> that
>>>> only one write can happen at any given time for the same database in our
>>>> application (i.e. there's one synchronized writer connection). However,
>>>> we
>>>> allow any number of reads (readers create their own connections, which
>>>> are
>>>> similarly not shared with other threads) to happen on the same database
>>>> (with a cap on the number of connections to respect resource limits).
>>>> Additionally, the database has PRAGMA journal_mode=WAL; set. At the
>>>> moment
>>>> when TSAN is triggered, there is one reader and one writer each with
>>>> their
>>>> own connection to the same db performing a read and a write:
>>>>
>>>> - Reader is running: sqlite3WalFindFrame, specifically the line with
>>>> this
>>>> code (line 59679 in my sqlite.c):
>>>>
>>>> for(iKey=walHash(pgno); aHash[iKey]; iKey=walNextHash(iKey)){
>>>>
>>>>
>>>> - Writer is running walIndexAppend, specifically the line with this code
>>>> (line 57856 in my sqlite.c):
>>>>
>>>> Thanks for the thread-testing. This one is a known condition. Tsan can't
>>> see it, but we think it is safe. See the comment above the block in
>>> sqlite3WalFindFrame() for details:
>>>
>>>http://www.sqlite.org/src/artifact/aa9cffc7a2bad?ln=2859..2883
>>>
>>> Basically the race condition is only on the append-only hash-table. But
>>> the hash-table is only used to accelerate lookups on the aPgno[] array.
>>> And
>>> since the hash-table is append-only, it can only produce false-positives
>>> (which are filtered out when aPgno[] is inspected).
>>>
>>> Dan.
>>>
>>>
>>>
>>>
>>>
>>> aHash[iKey] = (ht_slot)idx;
>>>>
>>>>
>>>> I apologize if those line numbers aren't helpful, however I hope the
>>>> function names + code are unique enough to locate the lines in question.
>>>> This is sqlite3 version 3.23.1.
>>>>
>>>> Please let me know if there's any other information I can provide.
>>>> Thanks!
>>>>
>>>> Ben
>>>> ___
>>>> sqlite-users mailing list
>>>> sqlite-users@mailinglists.sqlite.org
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
Ben
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proper parameter usage in FTS5 queries
Also one other question: with this method of using a parameter in the MATCH expression, do I still need to manually escape double quotes in the string bound to the parameter by replacing them with a pair of double quotes (for FTS5)? I'm not seeing any errors, but now I'm second guessing myself. Ben On Wed, May 23, 2018 at 10:46 AM, Ben Asher wrote: > Ah I see. Clever! The final solution ended up being: > > SELECT rowid FROM test_fts_index WHERE text MATCH '"' || ? || '"*' > > with || on either side of the ?. Does that sound right? Without that extra > ||, sqlite returned a syntax error (near ?). > > As follow up, it'd be great to see this solution documented somewhere on > the FTS5 page: https://sqlite.org/fts5.html. I'm glad there's a way to > accomplish this, but the solution is a bit more clever than I would have > come up with on my own. > > Thanks again for your help! > > Ben > > On Wed, May 23, 2018 at 1:15 AM, Dan Kennedy > wrote: > >> On 05/23/2018 06:02 AM, Ben Asher wrote: >> >>> Hi there! I'm working with a FTS5 query like this: >>> >>> SELECT rowid FROM text_fts_index WHERE text MATCH ? >>> >>> The parameter is something like this: "user input"* >>> >>> The idea here is to do a MATCH prefix-type query in FTS5. The problem >>> with >>> this query is that it breaks if the user input contains double quotes. >>> What >>> I want to do is something like this: >>> >>> SELECT rowid FROM text_fts_index WHERE text MATCH "?"* >>> >> >> Maybe: >> >> SELECT rowid FROM test_fts_index WHERE text MATCH '"' ? || '"*' >> >> Dan. >> >> >>> This 2nd version of the query doesn't appear to work though because the ? >>> is escaped in the quotes and doesn't appear to be recognized as a >>> variable. >>> Is there a way I'm missing to do this kind of MATCH prefix query and only >>> escape the user input? >>> >>> Thanks! >>> >>> Ben >>> ___ >>> sqlite-users mailing list >>> sqlite-users@mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > Ben > -- Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tsan Bug in WAL mode
I see. I think that makes sense! I've gone ahead and added these 2
functions to our local Tsan blacklist then. In researching this further, I
found one more Tsan issue here in walTryBeginRead:
- One thread is here:
http://www.sqlite.org/src/artifact/aa9cffc7a2bad?ln=2558
- One thread is here:
http://www.sqlite.org/src/artifact/aa9cffc7a2bad?ln=2571
Is this one expected as well? If so, I'll also add this function to our
blacklist.
Thanks again!
Ben
On Wed, May 23, 2018 at 1:24 AM, Dan Kennedy wrote:
> On 05/23/2018 06:21 AM, Ben Asher wrote:
>
>> Hi there! I believe I've found a SQLite bug that triggers TSAN. I'm hoping
>> to either confirm this bug or learn what I'm doing wrong. Some background:
>> in our code, we make sure to synchronize writes to our database such that
>> only one write can happen at any given time for the same database in our
>> application (i.e. there's one synchronized writer connection). However, we
>> allow any number of reads (readers create their own connections, which are
>> similarly not shared with other threads) to happen on the same database
>> (with a cap on the number of connections to respect resource limits).
>> Additionally, the database has PRAGMA journal_mode=WAL; set. At the moment
>> when TSAN is triggered, there is one reader and one writer each with their
>> own connection to the same db performing a read and a write:
>>
>> - Reader is running: sqlite3WalFindFrame, specifically the line with this
>> code (line 59679 in my sqlite.c):
>>
>> for(iKey=walHash(pgno); aHash[iKey]; iKey=walNextHash(iKey)){
>>
>>
>> - Writer is running walIndexAppend, specifically the line with this code
>> (line 57856 in my sqlite.c):
>>
>
> Thanks for the thread-testing. This one is a known condition. Tsan can't
> see it, but we think it is safe. See the comment above the block in
> sqlite3WalFindFrame() for details:
>
> http://www.sqlite.org/src/artifact/aa9cffc7a2bad?ln=2859..2883
>
> Basically the race condition is only on the append-only hash-table. But
> the hash-table is only used to accelerate lookups on the aPgno[] array. And
> since the hash-table is append-only, it can only produce false-positives
> (which are filtered out when aPgno[] is inspected).
>
> Dan.
>
>
>
>
>
>> aHash[iKey] = (ht_slot)idx;
>>
>>
>> I apologize if those line numbers aren't helpful, however I hope the
>> function names + code are unique enough to locate the lines in question.
>> This is sqlite3 version 3.23.1.
>>
>> Please let me know if there's any other information I can provide. Thanks!
>>
>> Ben
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
Ben
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proper parameter usage in FTS5 queries
Ah I see. Clever! The final solution ended up being: SELECT rowid FROM test_fts_index WHERE text MATCH '"' || ? || '"*' with || on either side of the ?. Does that sound right? Without that extra ||, sqlite returned a syntax error (near ?). As follow up, it'd be great to see this solution documented somewhere on the FTS5 page: https://sqlite.org/fts5.html. I'm glad there's a way to accomplish this, but the solution is a bit more clever than I would have come up with on my own. Thanks again for your help! Ben On Wed, May 23, 2018 at 1:15 AM, Dan Kennedy wrote: > On 05/23/2018 06:02 AM, Ben Asher wrote: > >> Hi there! I'm working with a FTS5 query like this: >> >> SELECT rowid FROM text_fts_index WHERE text MATCH ? >> >> The parameter is something like this: "user input"* >> >> The idea here is to do a MATCH prefix-type query in FTS5. The problem with >> this query is that it breaks if the user input contains double quotes. >> What >> I want to do is something like this: >> >> SELECT rowid FROM text_fts_index WHERE text MATCH "?"* >> > > Maybe: > > SELECT rowid FROM test_fts_index WHERE text MATCH '"' ? || '"*' > > Dan. > > >> This 2nd version of the query doesn't appear to work though because the ? >> is escaped in the quotes and doesn't appear to be recognized as a >> variable. >> Is there a way I'm missing to do this kind of MATCH prefix query and only >> escape the user input? >> >> Thanks! >> >> Ben >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Tsan Bug in WAL mode
Hi there! I believe I've found a SQLite bug that triggers TSAN. I'm hoping
to either confirm this bug or learn what I'm doing wrong. Some background:
in our code, we make sure to synchronize writes to our database such that
only one write can happen at any given time for the same database in our
application (i.e. there's one synchronized writer connection). However, we
allow any number of reads (readers create their own connections, which are
similarly not shared with other threads) to happen on the same database
(with a cap on the number of connections to respect resource limits).
Additionally, the database has PRAGMA journal_mode=WAL; set. At the moment
when TSAN is triggered, there is one reader and one writer each with their
own connection to the same db performing a read and a write:
- Reader is running: sqlite3WalFindFrame, specifically the line with this
code (line 59679 in my sqlite.c):
for(iKey=walHash(pgno); aHash[iKey]; iKey=walNextHash(iKey)){
- Writer is running walIndexAppend, specifically the line with this code
(line 57856 in my sqlite.c):
aHash[iKey] = (ht_slot)idx;
I apologize if those line numbers aren't helpful, however I hope the
function names + code are unique enough to locate the lines in question.
This is sqlite3 version 3.23.1.
Please let me know if there's any other information I can provide. Thanks!
Ben
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Proper parameter usage in FTS5 queries
Hi there! I'm working with a FTS5 query like this: SELECT rowid FROM text_fts_index WHERE text MATCH ? The parameter is something like this: "user input"* The idea here is to do a MATCH prefix-type query in FTS5. The problem with this query is that it breaks if the user input contains double quotes. What I want to do is something like this: SELECT rowid FROM text_fts_index WHERE text MATCH "?"* This 2nd version of the query doesn't appear to work though because the ? is escaped in the quotes and doesn't appear to be recognized as a variable. Is there a way I'm missing to do this kind of MATCH prefix query and only escape the user input? Thanks! Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
