Re: [sqlite] Report misprint in Documentation
Klaas Van B. wrote: " Indeed, in the page you refer to is missing the operator "<>" documented here: " " https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fsqlite.org%2Frowvalue.html%23syntax&data=02%7C01%7CStephan.Mueller%40microsoft.com%7C1581cbb9304944c7d4d208d496d91ad4%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636299306582232473&sdata=d8k9bPft6izHZBmEPqIgn2%2F%2FgWEcXkoXtPo06spwxGY%3D&reserved=0 " " Juan Francisco Benítez López wrote on Tue, 9 May 2017 12:35:38 +0200: " ">... ">"SQLite version 3 has the usual set of SQL comparison operators including ">"=", "==", "<", "<=", ">", ">=", "!=", "", "IN", "NOT IN", "BETWEEN", "IS", ">and "IS NOT", ." ">After the operator '!=' doesn't appear any operator. I suppose that the ">empty one is the operator '<>'. ">... I believe Klaas and Juan are referring to different pages. On the page Juan cites (https://sqlite.org/datatype3.html), there is indeed an empty "" after "!=" -- perhaps the text <> was interpreted as an empty HTML tag at some processing step? The page Klaas references (http://sqlite.org/rowvalue.html#syntax) does include <>, but is missing == and !=. Mind you, Juan's page doesn't claim an exhaustive list -- just that the comparison operators are those "including" the given list (but the empty "" certainly seems unintentional.) Perhaps some minor edits to both pages are worthwhile. stephan(); ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Clarification on "No Isolation On Same Db Connection"
Igor wrote: " On 9/8/2016 1:15 PM, Stephan Mueller wrote: " > " > I'd prefer to commit after each update " > " You can't commit on a single connection either, while there's an " > " unfinalized SELECT statement traversal going on. So you aren't gaining " > " anything by trying to interleave SELECT and updates on the same connection. " > " > This is the current situation that I need to correct. My SQLite is old enough " > that it doesn't prevent committing in this case " " Check for errors. I predict your COMMIT statement fails. In my experience, the COMMITs have been succeeding. I do check errors; any SQLite error raises an exception (I don't trap it) using the "RaiseError" option in perl's DBI. I have received errors about not being able to rollback while select/fetch loop is in progress, but these have been easy to deal with -- I finish the select loop before rolling back. (And before, the process exited in that un-rolled back state; next invocation succeeded in rolling back at startup because no active select existed then. Updating from SQLite 3.6.22 to 3.8.4.1 (my choices are limited to available PPM packages for my stable but old perl release) seems to work the same way -- permitting updates, succeeding commits with no ill-effects yet ever seen. thanks, stephan(); ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Clarification on "No Isolation On Same Db Connection"
Thanks Keith, for the detailed response. My plan now is to update SQLite to a WAL-capable version, enable WAL mode, use two connections instead of one and wrap the SELECT in a transaction. With luck, I improve overall performance (because of WAL mode) as well as gaining correctness. Many thanks, stephan(); -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf Sent: Wednesday, September 07, 2016 7:15 PM To: SQLite mailing list Subject: Re: [sqlite] Clarification on "No Isolation On Same Db Connection" There is only cursor stability level isolation between queries on the same connection. The query is executed as a descending series of loops. There is a "current record" pointer based on the location of the "current record" in the query for each table. If you modify an entry, that modification is made immediately. Then the next "step" in the descending loops is made. Your modification may, however, have changed the btree's and suchlike "out from under" your query. When your query continues (does the next step) it is using the "location pointers" of where it was and following them "as it is now". This make it appear that the behaviour is undefined, however this is only an appearance. The behaviour is entire rational, predictable, and deterministic. The "proper" way to do this is to engage WAL and use a separate connection for each (one for updating, one for reading). When WAL is in effect, read operations WITHIN A TRANSACTION have Repeatable Read isolation. That means that all READ operations within the transaction are repeatable and will return the exact same results until the transaction is ended. That is if you do: Connection 1: create table x ( x ); insert into x values (1); BEGIN; select x from x; Connection 2 BEGIN; UPDATE x SET x=2; select x from x; COMMIT; select x from x; SELECT x from x; select x from x; COMMIT; select x from x; All the selects in connection 1 within the transaction will return 1, while the selects in connection 2 will return 2. The last select in connection 1 (taking place in a new automagic transaction) will return 2. In other words, the data READ from a connection between BEGIN ... COMMIT is REPEATABLE -- hence the name Repeatable Read. The "normal" isolation level is Cursor Stability (which means that the pointers to the "current rows" remain "current" in the face of updates, but that the results of the query may perturbate as a result of concomitant updates. While the transaction is open on connection 1 (and in a transaction), you can do as many transactions as you like on connection 2. Those effects will not be seen until the changes are committed on connection 2 *AND* the Repeatable Read isolation level is ended by committing connection 1 (thus allowing you to see changes made to the database). > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Stephan Mueller > Sent: Wednesday, 7 September, 2016 16:12 > To: sqlite-users@mailinglists.sqlite.org > Subject: [sqlite] Clarification on "No Isolation On Same Db Connection" > > Hello all; > > I have a scenario that is well covered by the text at > https://na01.safelinks.protection.outlook.com/?url=https%3a%2f%2fwww.sqlite.org%2fisolation.html&data=02%7c01%7cStephan.Mueller%40microsoft.com%7cae7bf70882484868a25608d3d78e0a69%7c72f988bf86f141af91ab2d7cd011db47%7c1%7c0%7c636088977467764750&sdata=IwtWC5g%2fnAvKak4Qlrzmmf5q1AdmLWgJjENIWjZXOHc%3d > in the section "No Isolation Between > Operations On The Same Database Connection" (relevant paragraphs at end > for convenience) but am hoping for a bit more clarity. Here's an overview > of my scenario. I'm trying to find the right level of detail; if I've > failed on the side of too little, I'm happy to provide more. > > I have a non-trivial SELECT simulating an outer join > (https://na01.safelinks.protection.outlook.com/?url=http%3a%2f%2fstackoverflow.com%2fquestions%2f1923259%2ffull-outer-join-with-sqlite&data=02%7c01%7cStephan.Mueller%40microsoft.com%7cae7bf70882484868a25608d3d78e0a69%7c72f988bf86f141af91ab2d7cd011db47%7c1%7c0%7c636088977467764750&sdata=diT3pTDxbLc0lGoyjg8sV4wPi%2fJZ9uhTDCexWz2KCic%3d) > with the overall structure > > SELECT columns FROM t1 LEFT JOIN t2 ON t1.column = t2.column > WHERE conditions > UNION ALL > SELECT columns FROM t2 LEFT JOIN t1 ON t2.column = t1.column > WHERE t1.column IS NULL > AND more_conditions >
Re: [sqlite] Clarification on "No Isolation On Same Db Connection"
Thanks Kevin. I had considered the temporary table approach, but with potentially millions of updates, I am concerned about the time spent writing the 100MB+ of temp data, spoiling the interactive user experience. However, I may try it (as you say, it should be simple) to see the actual performance, now that my "even cheaper", "hacky" proposal (testing returned row keys for monotonic increase) has been explained to be "incorrect". Fortunately, it looks like updating to a newer SQLite and using WAL mode and a transaction round the SELECT will be straightforward. stephan(); -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Kevin O'Gorman Sent: Thursday, September 08, 2016 10:07 AM To: SQLite mailing list Subject: Re: [sqlite] Clarification on "No Isolation On Same Db Connection" It seems to me that the simplest, most portable approach for this sort of thing would to be having the SELECT create a temporary table of the desired actions, and not apply them until after the select has concluded. This would work in any database -- it does not depend on precise semantics of WAL, for instance. Of course, it could be that this is inefficient for some reason, and that might cause you to take a different course, but all the alternatives are going to be more troublesome to understand and maintain. It's up to you whether this is worth it. On Wed, Sep 7, 2016 at 7:24 PM, Igor Tandetnik wrote: > On 9/7/2016 6:11 PM, Stephan Mueller wrote: > >> I understand that a way to ensure "SELECT is unperturbed" semantics is to >> use separate connections for SELECT and updates. >> > > If you go down that route, make sure you are using WAL journaling mode; it > won't work otherwise. > > This is undesirable since I'd have to (IIUC) do all my updates (possibly >> millions) in a single transaction. >> > > I don't see how this follows. > > I'd prefer to commit after each update >> > > You can't commit on a single connection either, while there's an > unfinalized SELECT statement traversal going on. So you aren't gaining > anything by trying to interleave SELECT and updates on the same connection. > > That is, if I ever receive a record that ought to have arrived earlier >> because of ORDER BY, it must be a since-SELECT-began update, and should be >> ignored. >> > > When data is modified under SELECT's feet, phantom rows are just one > problem; it's also possible for the statement to skip rows it would have > otherwise returned, and to return rows containing stale data. Basically, > undefined behavior is undefined. > -- > Igor Tandetnik > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > https://na01.safelinks.protection.outlook.com/?url=http%3a%2f%2fmailinglists.sqlite.org%2fcgi-bin%2fmailman%2flistinfo%2fsqlite-users&data=02%7c01%7cStephan.Mueller%40microsoft.com%7c312a3320ef4e4ae2b39d08d3d80aa5d0%7c72f988bf86f141af91ab2d7cd011db47%7c1%7c0%7c636089512652659928&sdata=wI43xdwOejNfxRdlJYbO8e1K6AoglRxoTmLECUF6nb0%3d > -- #define QUESTION ((bb) || (!bb)) /* Shakespeare */ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org https://na01.safelinks.protection.outlook.com/?url=http%3a%2f%2fmailinglists.sqlite.org%2fcgi-bin%2fmailman%2flistinfo%2fsqlite-users&data=02%7c01%7cStephan.Mueller%40microsoft.com%7c312a3320ef4e4ae2b39d08d3d80aa5d0%7c72f988bf86f141af91ab2d7cd011db47%7c1%7c0%7c636089512652659928&sdata=wI43xdwOejNfxRdlJYbO8e1K6AoglRxoTmLECUF6nb0%3d ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Clarification on "No Isolation On Same Db Connection"
Igor wrote: " On 9/7/2016 6:11 PM, Stephan Mueller wrote: " > I understand that a way to ensure "SELECT is unperturbed" semantics is to " > use separate connections for SELECT and updates. " If you go down that route, make sure you are using WAL journaling mode; " it won't work otherwise. I see that now -- only WAL provides snapshot isolation. " > This is undesirable since I'd have to (IIUC) do all my updates (possibly " > millions) in a single transaction. " I don't see how this follows. My thinking (in the absence of WAL) was that committing earlier would result in my SELECT connection potentially seeing the updates. " > I'd prefer to commit after each update " You can't commit on a single connection either, while there's an " unfinalized SELECT statement traversal going on. So you aren't gaining " anything by trying to interleave SELECT and updates on the same connection. This is the current situation that I need to correct. My SQLite is old enough that it doesn't prevent committing in this case (nor does it support WAL mode). My next step is to update my SQLite. " > That is, if I ever receive a record that ought to have arrived earlier " > because of ORDER BY, it must be a since-SELECT-began update, and " > should be ignored. " When data is modified under SELECT's feet, phantom rows are just one " problem; it's also possible for the statement to skip rows it would have " otherwise returned, and to return rows containing stale data. Basically, " undefined behavior is undefined. So I'd better address this, to minimize chance of my hard drive being reformatted :-) Many thanks, stephan(); ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Clarification on "No Isolation On Same Db Connection"
I sent this long-winded question.
thanks,
stephan();
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Bob McFarlane
Sent: Wednesday, September 07, 2016 5:10 PM
To: 'SQLite mailing list'
Subject: Re: [sqlite] Clarification on "No Isolation On Same Db Connection"
Please reply if you sent this. Thanks.
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Stephan Mueller
Sent: Wednesday, September 7, 2016 6:12 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Clarification on "No Isolation On Same Db Connection"
Hello all;
I have a scenario that is well covered by the text at
https://na01.safelinks.protection.outlook.com/?url=https%3a%2f%2fwww.sqlite.org%2fisolation.html&data=02%7c01%7cStephan.Mueller%40microsoft.com%7caac768f3187b45a8bcac08d3d77cb845%7c72f988bf86f141af91ab2d7cd011db47%7c1%7c0%7c636088903072498265&sdata=BZwTiTyff3pGJo2x8aFpee%2fUupIC2%2bNQvZy00F3kLpg%3d
in the section "No Isolation Between
Operations On The Same Database Connection" (relevant paragraphs at end for
convenience) but am hoping for a bit more clarity. Here's an overview of my
scenario. I'm trying to find the right level of detail; if I've failed on
the side of too little, I'm happy to provide more.
I have a non-trivial SELECT simulating an outer join
(https://na01.safelinks.protection.outlook.com/?url=http%3a%2f%2fstackoverflow.com%2fquestions%2f1923259%2ffull-outer-join-with-sqlite&data=02%7c01%7cStephan.Mueller%40microsoft.com%7caac768f3187b45a8bcac08d3d77cb845%7c72f988bf86f141af91ab2d7cd011db47%7c1%7c0%7c636088903072498265&sdata=Q%2fvs8BRYmXMByVxwdEByRiV9ntDE2CfNBle4h1UyGpA%3d)
with the overall structure
SELECT columns FROM t1 LEFT JOIN t2 ON t1.column = t2.column
WHERE conditions
UNION ALL
SELECT columns FROM t2 LEFT JOIN t1 ON t2.column = t1.column
WHERE t1.column IS NULL
AND more_conditions
ORDER BY column
The application fetches a row at a time, doing an UPDATE or INSERT or DELETE
into t1 (henceforth referred to just as 'updates') for each row fetched. I
_want_ the semantics to be that the SELECT is unperturbed by my updates.
(Details: there are actually three tables. The joined-on column is UNIQUE
(indeed, the PRIMARY KEY) in each table. The query conditions are such that
column is UNIQUE in the result set when not doing any updates while
iterating.)
I understand that a way to ensure "SELECT is unperturbed" semantics is to
use separate connections for SELECT and updates. This is undesirable since
I'd have to (IIUC) do all my updates (possibly millions) in a single
transaction. I'd prefer to commit after each update (to avoid redoing any
work in case of unnatural program abort, for example). The compromise for
performance is to commit every N (4096, currently) updates, but potentially
millions seems right out.
Another way is to fetch all the results into a temporary table tt, then
iterate over tt while modifying t1. This is easy to code, and SQLite can
fill tt with a single INSERT INTO tt SELECT . With potentially
millions of rows, however, I'm rather an approach that doesn't spoil the
interactive user experience by adding seconds spent on creating the
temporary table, if that can be avoided.
I'm thus led to the following notion (apologies for my pseudo-code, which is
neither SQL, nor C, nor the Perl I'm actually coding in):
prepare SELECT from above
execute()
$last = "";
while (fetchnext) {
if $fetched.column <= $last(1)
skip to next iteration of loop (2)
$last = $fetched.column (3)
update-or-insert-or-delete
}
That is, if I ever receive a record that ought to have arrived earlier
because of ORDER BY, it must be a since-SELECT-began update, and should be
ignored.
I'm trying to not think in terms of potential implementation, and stick with
"what must ORDER BY guarantee?" I'd imagine if it ever returns a recent
update, it would do so at the proper point in sort order. If it's decided
that an "alphabetically-prior-to-where-we-have-been-returning-records recent
update" record must be returned, it would be returned ASAP -- as if the
ORDER BY guarantee was that "all remaining records are sorted, and the
lowest of those returned next".
Is my thinking sound? -- are the numbered lines in my pseudo-code necessary
and/or sufficient to achieve the goal of ignoring updates in this case, in a
righteously precise use of SQL?
Thanks in advance for your thoughts!
Here are selections from the relevant SQLite documentation paragraphs to
provide context, with co
[sqlite] Clarification on "No Isolation On Same Db Connection"
Hello all;
I have a scenario that is well covered by the text at
https://www.sqlite.org/isolation.html in the section "No Isolation Between
Operations On The Same Database Connection" (relevant paragraphs at end for
convenience) but am hoping for a bit more clarity. Here's an overview of my
scenario. I'm trying to find the right level of detail; if I've failed on the
side of too little, I'm happy to provide more.
I have a non-trivial SELECT simulating an outer join
(http://stackoverflow.com/questions/1923259/full-outer-join-with-sqlite) with
the overall structure
SELECT columns FROM t1 LEFT JOIN t2 ON t1.column = t2.column
WHERE conditions
UNION ALL
SELECT columns FROM t2 LEFT JOIN t1 ON t2.column = t1.column
WHERE t1.column IS NULL
AND more_conditions
ORDER BY column
The application fetches a row at a time, doing an UPDATE or INSERT or DELETE
into t1 (henceforth referred to just as 'updates') for each row fetched. I
_want_ the semantics to be that the SELECT is unperturbed by my updates.
(Details: there are actually three tables. The joined-on column is UNIQUE
(indeed, the PRIMARY KEY) in each table. The query conditions are such that
column is UNIQUE in the result set when not doing any updates while iterating.)
I understand that a way to ensure "SELECT is unperturbed" semantics is to use
separate connections for SELECT and updates. This is undesirable since I'd
have to (IIUC) do all my updates (possibly millions) in a single transaction.
I'd prefer to commit after each update (to avoid redoing any work in case of
unnatural program abort, for example). The compromise for performance is to
commit every N (4096, currently) updates, but potentially millions seems right
out.
Another way is to fetch all the results into a temporary table tt, then iterate
over tt while modifying t1. This is easy to code, and SQLite can fill tt with
a single INSERT INTO tt SELECT . With potentially millions of rows,
however, I'm rather an approach that doesn't spoil the interactive user
experience by adding seconds spent on creating the temporary table, if that can
be avoided.
I'm thus led to the following notion (apologies for my pseudo-code, which is
neither SQL, nor C, nor the Perl I'm actually coding in):
prepare SELECT from above
execute()
$last = "";
while (fetchnext) {
if $fetched.column <= $last(1)
skip to next iteration of loop (2)
$last = $fetched.column (3)
update-or-insert-or-delete
}
That is, if I ever receive a record that ought to have arrived earlier because
of ORDER BY, it must be a since-SELECT-began update, and should be ignored.
I'm trying to not think in terms of potential implementation, and stick with
"what must ORDER BY guarantee?" I'd imagine if it ever returns a recent
update, it would do so at the proper point in sort order. If it's decided that
an "alphabetically-prior-to-where-we-have-been-returning-records recent update"
record must be returned, it would be returned ASAP -- as if the ORDER BY
guarantee was that "all remaining records are sorted, and the lowest of those
returned next".
Is my thinking sound? -- are the numbered lines in my pseudo-code necessary
and/or sufficient to achieve the goal of ignoring updates in this case, in a
righteously precise use of SQL?
Thanks in advance for your thoughts!
Here are selections from the relevant SQLite documentation paragraphs to
provide context, with comments/queries interspersed.
No Isolation Between Operations On The Same Database Connection
... What if a SELECT statement is started and the
sqlite3_step() interface steps through
roughly half of its output, then some
UPDATE statements are run by the
application that modify the table that the SELECT statement is reading, then
more calls to sqlite3_step() are made
to finish out the SELECT statement? Will the later steps of the SELECT
statement see the changes made by the UPDATE or not? The answer is that this
behavior is undefined. In particular, whether or not the SELECT statement sees
the concurrent changes depends on which release of SQLite is running, the
schema of the database file, whether or not
ANALYZE has been run, and the details
of the query. In some cases, it might depend on the content of the database
file, too. There is no good way to know whether or not a SELECT statement will
see changes that were made to the database by the same database connection
after the SELECT statement was started. And hence, developers should diligently
avoid writing applications that make assumptions about what will occur in that
circumsta
Re: [sqlite] Bug in CREATE INDEX
Kevin asks: " Does anybody know where the actual defaults and controlling environment " variables are documented, by operating system? Or are they? I believe Section 5.0. near the end of https://www.sqlite.org/tempfiles.html describes what you're looking for. thanks, stephan(); ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Creating a stable database
> On 9/25/15, Aaron Digulla wrote: >> >> I was wondering if it was possible to create the same database (= no binary >> difference) twice with sqlite. I trust the output of sqldiff, usually with the --primarykey option, to tell me two dbs are equivalent (subject to the caveats listed in http://sqlite.org/sqldiff.html). Great tool -- thanks DRH and other SQLiters! Understood this doesn't directly answer the original question, and I can easily imagine cases where identical dbs are needed, but this discussion reminded me of sqldiff, and this aside: it'd be greatly appreciated (and maybe increase uptake?) if sqldiff binaries were available on the http://sqlite.org/download.html page along with the other tools. stephan($0.02);
