Re: [sqlite] Getting query results as new records are added
Actually it makes perfect sense. Yranslate fetch onto step and the update where cirrwnt of cirsor to updare the roww just read using the rowid. your subsequent fwtxjes will nwver again return a row you alreadu reteiebwd. in pther qprds tjeu arw tje same Sent from Samsung MobileIgor Tandetnik wrote: ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting query results as new records are added
On Fri, Sep 14, 2012 at 8:57 AM, Igor Tandetnik wrote: > On 9/14/2012 10:36 AM, Pavel Ivanov wrote: >> >> On Fri, Sep 14, 2012 at 5:58 AM, Igor Tandetnik >> wrote: >>> >>> Wait a minute. If "statement-level read consistency" only applies to two >>> queries in different transactions, what bearing, if any, does it have on >>> your example of "open, fetch, update, fetch"? There, everything happens in >>> the same transaction. I'm thoroughly confused. >> >> >> I'm sorry, I made that example before I carefully re-read Petite's >> email and understood what you are concerned about. > > > I'm not actually concerned about anything. It is difficult for me to > possibly care less about the intricacies of Oracle's transaction management. > It's just that Petite asked whether SQLite supports something called > "statement-level read consistency", and I've never heard of such a beast, > and was confused by the formal definition thereof that Petite cited. > > Let me see if I understand it. Oracle (optionally?) provides transaction > isolation levels weaker than "serializable", whereby transaction A may see > changes made by another transaction B, committed after A started. This weaker isolation level is (or at least was when I worked with Oracle databases) set by default. "Serializable" was optional as it imposes more problems for db administrators and requires more careful implementations on programmers side. > However, > it guarantees that any individual statement within A, during its lifetime, > will see data either as it existed before B committed, or as it exists > afterward, but never a mix of the two. Whereas two identical statements > executed one after another may very well observe different data. This is > what's called "statement-level read consistency". > > Since SQLite only provides serializable transaction isolation level, no > statement in transaction A will ever see any changes made by transaction B. > This is a strictly stronger guarantee than "statement-level read > consistency", rendering moot the question of whether SQLite supports the > same. > > In addition to, and independently of, all of the above, Oracle provides a > similar guarantee (whose name was not mentioned in this thread) that a > statement A within a transaction will see all changes by another statement B > within the same transaction if B completes before A starts; and will not see > any changes made by yet third statement C, still within the same > transaction, if C completes after A starts (even if C starts while A was in > progress). SQLite only provides half of that guarantee: statement A will see > the changes made by B; it may see none, some, or all changes made by C, in > an unpredictable way. > > Thus, in SQLite it is best to avoid modifying the same data that an > outstanding SELECT statement is iterating over, as the outcome of such a > modification is unpredictable. > > > Does this sound right to you? Yes, that's right. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting query results as new records are added
On 9/14/2012 10:36 AM, Pavel Ivanov wrote: On Fri, Sep 14, 2012 at 5:58 AM, Igor Tandetnik wrote: Wait a minute. If "statement-level read consistency" only applies to two queries in different transactions, what bearing, if any, does it have on your example of "open, fetch, update, fetch"? There, everything happens in the same transaction. I'm thoroughly confused. I'm sorry, I made that example before I carefully re-read Petite's email and understood what you are concerned about. I'm not actually concerned about anything. It is difficult for me to possibly care less about the intricacies of Oracle's transaction management. It's just that Petite asked whether SQLite supports something called "statement-level read consistency", and I've never heard of such a beast, and was confused by the formal definition thereof that Petite cited. Let me see if I understand it. Oracle (optionally?) provides transaction isolation levels weaker than "serializable", whereby transaction A may see changes made by another transaction B, committed after A started. However, it guarantees that any individual statement within A, during its lifetime, will see data either as it existed before B committed, or as it exists afterward, but never a mix of the two. Whereas two identical statements executed one after another may very well observe different data. This is what's called "statement-level read consistency". Since SQLite only provides serializable transaction isolation level, no statement in transaction A will ever see any changes made by transaction B. This is a strictly stronger guarantee than "statement-level read consistency", rendering moot the question of whether SQLite supports the same. In addition to, and independently of, all of the above, Oracle provides a similar guarantee (whose name was not mentioned in this thread) that a statement A within a transaction will see all changes by another statement B within the same transaction if B completes before A starts; and will not see any changes made by yet third statement C, still within the same transaction, if C completes after A starts (even if C starts while A was in progress). SQLite only provides half of that guarantee: statement A will see the changes made by B; it may see none, some, or all changes made by C, in an unpredictable way. Thus, in SQLite it is best to avoid modifying the same data that an outstanding SELECT statement is iterating over, as the outcome of such a modification is unpredictable. Does this sound right to you? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting query results as new records are added
On Fri, Sep 14, 2012 at 5:58 AM, Igor Tandetnik wrote: > Pavel Ivanov wrote: >> There's another way how it could make sense - if you read that excerpt >> in context. ;-) The "statement-level read consistency" definition >> cited by Petite is the transaction isolation level. I.e. it defines >> what each statement executed in one transaction can see related to the >> changes done in another transaction. > > Well, in this case, if you only ever execute one statement per transaction in > SQLite, then it does support "statement-level read consistency." Though I > fail to see how it materially differs from "serializable". Am I missing > something obvious? For one statement per transaction "statement-level consistency" and "serializable" do not differ, you are right. But IIRC Oracle doesn't have an auto-commit mode of operation and multi-statement transactions are more common, especially when we talk about stored procedures, triggers and things like that. And if you ever call "begin transaction" or execute multi-statement transactions then serializable will make sure that your transaction sees only changes committed before "begin", in "statement-level" you can see changes committed after "begin". The worst problem of this: with "statement-level consistency" two identical select queries executed inside the same transaction can return different results. >> This isolation level doesn't >> define visibility of changes done in the same transaction. These >> visibility rules are defined in different place in the documentation >> and don't depend on transaction isolation level. >> >> So returning to my example "statement-level read consistency" means >> that cursor can see myfield=2 if update is done in another transaction >> before cursor is opened but after begin is executed. > > Wait a minute. If "statement-level read consistency" only applies to two > queries in different transactions, what bearing, if any, does it have on your > example of "open, fetch, update, fetch"? There, everything happens in the > same transaction. I'm thoroughly confused. I'm sorry, I made that example before I carefully re-read Petite's email and understood what you are concerned about. Plus I was taking your example as a base for mine. :) But for statements inside one transaction Oracle uses the same rules as in statement-level consistency too. Although it's probably named a little bit differently and its definition doesn't include "committed data" (it would be "finished statements" or something like that). Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting query results as new records are added
Pavel Ivanov wrote: > There's another way how it could make sense - if you read that excerpt > in context. ;-) The "statement-level read consistency" definition > cited by Petite is the transaction isolation level. I.e. it defines > what each statement executed in one transaction can see related to the > changes done in another transaction. Well, in this case, if you only ever execute one statement per transaction in SQLite, then it does support "statement-level read consistency." Though I fail to see how it materially differs from "serializable". Am I missing something obvious? > This isolation level doesn't > define visibility of changes done in the same transaction. These > visibility rules are defined in different place in the documentation > and don't depend on transaction isolation level. > > So returning to my example "statement-level read consistency" means > that cursor can see myfield=2 if update is done in another transaction > before cursor is opened but after begin is executed. Wait a minute. If "statement-level read consistency" only applies to two queries in different transactions, what bearing, if any, does it have on your example of "open, fetch, update, fetch"? There, everything happens in the same transaction. I'm thoroughly confused. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting query results as new records are added
On Thu, Sep 13, 2012 at 9:29 PM, Igor Tandetnik wrote: > Pavel Ivanov wrote: >> On Thu, Sep 13, 2012 at 3:36 PM, Igor Tandetnik wrote: >>> On 9/13/2012 4:39 PM, Petite Abeille wrote: On Sep 13, 2012, at 10:24 PM, Igor Tandetnik wrote: >> In other words, a select should only ever see what existed at the >> beginning of the query, and that is that. > > > Again, the concept of isolation level applies to transactions, not to > individual queries. Let me rephrase that, by paraphrasing some other DB doc [1]: "statement-level read consistency. This guarantees that all the data returned by a single query comes from a single point in time—the time that the query began. Therefore, a query never sees dirty data or any of the changes made by transactions that commit during query execution. As query execution proceeds, only data committed before the query began is visible to the query. The query does not see changes committed after statement execution begins." So… SQLite always provide statement-level read consistency, yes? >>> >>> >>> In SQLite, a query cannot see changes made by other transactions (whether >>> committed or otherwise) that weren't already committed by the time the >>> current transaction started. But it may observe changes made by the current >>> transaction - the transaction the query is running in. If that's what the >>> definition means by "dirty data", then no, SQLite doesn't provide >>> statement-level read consistency. >>> >>> But I can't help but wonder. Consider this scenario: >>> >>> begin; >>> update mytable set myfield=1; >>> commit; >>> >>> begin; >>> update mytable set myfield=2; >>> select myfield from mytable; >>> commit; >>> >>> Are you saying that in Oracle, the SELECT statement would observe myfield=1? >>> That seems to follow from the definition you quote, but I can't imagine how >>> such behavior could be useful. In SQLite, the SELECT statement would >>> certainly see myfield=2. >> >> No, Oralce will return myfield=2 in this case. > > So then it doesn't support statement-level read consistency, as defined in > its own documentation? The claim was that, among other things, "as query > execution proceeds, only data committed before the query began is visible to > the query." But setting myfield to 2 has not yet been committed by the time > the query began. > > The only way it makes sense is if the word "commit" means something other > than "commit a transaction" - something like "a data manipulation statement > successfully runs to completion". There's another way how it could make sense - if you read that excerpt in context. ;-) The "statement-level read consistency" definition cited by Petite is the transaction isolation level. I.e. it defines what each statement executed in one transaction can see related to the changes done in another transaction. This isolation level doesn't define visibility of changes done in the same transaction. These visibility rules are defined in different place in the documentation and don't depend on transaction isolation level. So returning to my example "statement-level read consistency" means that cursor can see myfield=2 if update is done in another transaction before cursor is opened but after begin is executed. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting query results as new records are added
Pavel Ivanov wrote: > On Thu, Sep 13, 2012 at 3:36 PM, Igor Tandetnik wrote: >> On 9/13/2012 4:39 PM, Petite Abeille wrote: >>> >>> >>> On Sep 13, 2012, at 10:24 PM, Igor Tandetnik wrote: >>> > In other words, a select should only ever see what existed at the > beginning of the query, and that is that. Again, the concept of isolation level applies to transactions, not to individual queries. >>> >>> >>> Let me rephrase that, by paraphrasing some other DB doc [1]: >>> >>> "statement-level read consistency. This guarantees that all the data >>> returned by a single query comes from a single point in time—the time that >>> the query began. Therefore, a query never sees dirty data or any of the >>> changes made by transactions that commit during query execution. As query >>> execution proceeds, only data committed before the query began is visible to >>> the query. The query does not see changes committed after statement >>> execution begins." >>> >>> So… SQLite always provide statement-level read consistency, yes? >> >> >> In SQLite, a query cannot see changes made by other transactions (whether >> committed or otherwise) that weren't already committed by the time the >> current transaction started. But it may observe changes made by the current >> transaction - the transaction the query is running in. If that's what the >> definition means by "dirty data", then no, SQLite doesn't provide >> statement-level read consistency. >> >> But I can't help but wonder. Consider this scenario: >> >> begin; >> update mytable set myfield=1; >> commit; >> >> begin; >> update mytable set myfield=2; >> select myfield from mytable; >> commit; >> >> Are you saying that in Oracle, the SELECT statement would observe myfield=1? >> That seems to follow from the definition you quote, but I can't imagine how >> such behavior could be useful. In SQLite, the SELECT statement would >> certainly see myfield=2. > > No, Oralce will return myfield=2 in this case. So then it doesn't support statement-level read consistency, as defined in its own documentation? The claim was that, among other things, "as query execution proceeds, only data committed before the query began is visible to the query." But setting myfield to 2 has not yet been committed by the time the query began. The only way it makes sense is if the word "commit" means something other than "commit a transaction" - something like "a data manipulation statement successfully runs to completion". -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting query results as new records are added
On Thu, Sep 13, 2012 at 3:36 PM, Igor Tandetnik wrote: > On 9/13/2012 4:39 PM, Petite Abeille wrote: >> >> >> On Sep 13, 2012, at 10:24 PM, Igor Tandetnik wrote: >> In other words, a select should only ever see what existed at the beginning of the query, and that is that. >>> >>> >>> Again, the concept of isolation level applies to transactions, not to >>> individual queries. >> >> >> Let me rephrase that, by paraphrasing some other DB doc [1]: >> >> "statement-level read consistency. This guarantees that all the data >> returned by a single query comes from a single point in time—the time that >> the query began. Therefore, a query never sees dirty data or any of the >> changes made by transactions that commit during query execution. As query >> execution proceeds, only data committed before the query began is visible to >> the query. The query does not see changes committed after statement >> execution begins." >> >> So… SQLite always provide statement-level read consistency, yes? > > > In SQLite, a query cannot see changes made by other transactions (whether > committed or otherwise) that weren't already committed by the time the > current transaction started. But it may observe changes made by the current > transaction - the transaction the query is running in. If that's what the > definition means by "dirty data", then no, SQLite doesn't provide > statement-level read consistency. > > But I can't help but wonder. Consider this scenario: > > begin; > update mytable set myfield=1; > commit; > > begin; > update mytable set myfield=2; > select myfield from mytable; > commit; > > Are you saying that in Oracle, the SELECT statement would observe myfield=1? > That seems to follow from the definition you quote, but I can't imagine how > such behavior could be useful. In SQLite, the SELECT statement would > certainly see myfield=2. No, Oralce will return myfield=2 in this case. Oracle's statement isolation level (which is not supported by SQLite) is for cases like this: begin; open cursor c as select myfield from mytable; fetch next row from c; fetch next row from c; update mytable set myfield=2 where ...; fetch next row from c; fetch next row from c; commit; In this case cursor will never observe myfield=2 and this behavior is well defined, documented and guaranteed. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting query results as new records are added
On 13 Sep 2012, at 11:36pm, Igor Tandetnik wrote: >> "statement-level read consistency. This guarantees that all the data >> returned by a single query comes from a single point in time—the time that >> the query began. Therefore, a query never sees dirty data or any of the >> changes made by transactions that commit during query execution. As query >> execution proceeds, only data committed before the query began is visible to >> the query. The query does not see changes committed after statement >> execution begins." > > But I can't help but wonder. Consider this scenario: > > begin; > update mytable set myfield=1; > commit; > > begin; > update mytable set myfield=2; > select myfield from mytable; > commit; > > Are you saying that in Oracle, the SELECT statement would observe myfield=1? > That seems to follow from the definition you quote, but I can't imagine how > such behavior could be useful. In SQLite, the SELECT statement would > certainly see myfield=2. Worse still, consider begin; update mytable set myfield=2; select myfield,anotherfield from mytable where myfield=1; commit; It's possible that you might get a row which didn't have myfield=1 at SELECT time ? Oh dear. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting query results as new records are added
On 9/13/2012 4:39 PM, Petite Abeille wrote: On Sep 13, 2012, at 10:24 PM, Igor Tandetnik wrote: In other words, a select should only ever see what existed at the beginning of the query, and that is that. Again, the concept of isolation level applies to transactions, not to individual queries. Let me rephrase that, by paraphrasing some other DB doc [1]: "statement-level read consistency. This guarantees that all the data returned by a single query comes from a single point in time—the time that the query began. Therefore, a query never sees dirty data or any of the changes made by transactions that commit during query execution. As query execution proceeds, only data committed before the query began is visible to the query. The query does not see changes committed after statement execution begins." So… SQLite always provide statement-level read consistency, yes? In SQLite, a query cannot see changes made by other transactions (whether committed or otherwise) that weren't already committed by the time the current transaction started. But it may observe changes made by the current transaction - the transaction the query is running in. If that's what the definition means by "dirty data", then no, SQLite doesn't provide statement-level read consistency. But I can't help but wonder. Consider this scenario: begin; update mytable set myfield=1; commit; begin; update mytable set myfield=2; select myfield from mytable; commit; Are you saying that in Oracle, the SELECT statement would observe myfield=1? That seems to follow from the definition you quote, but I can't imagine how such behavior could be useful. In SQLite, the SELECT statement would certainly see myfield=2. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting query results as new records are added
On Sep 13, 2012, at 10:24 PM, Igor Tandetnik wrote: >> In other words, a select should only ever see what existed at the beginning >> of the query, and that is that. > > Again, the concept of isolation level applies to transactions, not to > individual queries. Let me rephrase that, by paraphrasing some other DB doc [1]: "statement-level read consistency. This guarantees that all the data returned by a single query comes from a single point in time—the time that the query began. Therefore, a query never sees dirty data or any of the changes made by transactions that commit during query execution. As query execution proceeds, only data committed before the query began is visible to the query. The query does not see changes committed after statement execution begins." So… SQLite always provide statement-level read consistency, yes? [1] http://docs.oracle.com/cd/B28359_01/server.111/b28318/consist.htm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting query results as new records are added
On 9/13/2012 4:14 PM, Petite Abeille wrote: On Sep 13, 2012, at 9:30 PM, Igor Tandetnik wrote: If records are added by the same transaction that runs the SELECT statements, then they may or may not be observed (imagine for example that the statement has an ORDER BY clause, and a new row is inserted that comes before the row that the statement is currently positioned at). H… doesn't SQLite provide read consistency by default (i.e. isolation level SERIALIZABLE [1])? Yes it does. Note that the concept of "isolation level" applies to different transactions, not to statements executed within the same transaction. Within a single transaction, you certainly want to see changes in SELECT that you've just made with INSERT or UPDATE. In other words, a select should only ever see what existed at the beginning of the query, and that is that. Again, the concept of isolation level applies to transactions, not to individual queries. Or perhaps are you talking about 'dirty reads', aka 'read uncommitted' [2]? No I do not. This allows one connection to observe changes made by another, under certain circumstances. I'm talking about a connection observing changes it itself made. Or something else altogether? Yes. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting query results as new records are added
On Sep 13, 2012, at 9:30 PM, Igor Tandetnik wrote: > If records are added by the same transaction that runs the SELECT statements, > then they may or may not be observed (imagine for example that the statement > has an ORDER BY clause, and a new row is inserted that comes before the row > that the statement is currently positioned at). H… doesn't SQLite provide read consistency by default (i.e. isolation level SERIALIZABLE [1])? In other words, a select should only ever see what existed at the beginning of the query, and that is that. Or perhaps are you talking about 'dirty reads', aka 'read uncommitted' [2]? Or something else altogether? [1] http://en.wikipedia.org/wiki/Isolation_(database_systems)#Serializable [2] http://www.sqlite.org/pragma.html#pragma_read_uncommitted ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting query results as new records are added
If you use rowid correctly (always incrementing rowid by using AUTOINCREMENT) you can always query records > lastrowid. That's probably easier. http://www.sqlite.org/autoinc.html So something like: startrowid = 0; lastrowid = select max(rowid) from mytable; select * from mytable where rowid >= startrowid and .; startrowid = lastrowid + 1; Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Paul Vercellotti [pverce...@yahoo.com] Sent: Thursday, September 13, 2012 2:20 PM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] Getting query results as new records are added Hi there, I'm wondering if there's a way to get "incremental" results to a query after it's started, that includes new records added after the query began? That is, we've got a UI view that's showing query results, while a background task is adding records to the database, some of which may match our query. We'd like update the query results view with new records as they're added, without having to repeat the whole query and weed out the results we're already showing? Any suggestions are appreciated. -Paul ___ 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] Getting query results as new records are added
On 9/13/2012 3:20 PM, Paul Vercellotti wrote: I'm wondering if there's a way to get "incremental" results to a query after it's started, that includes new records added after the query began? If records are added by the same transaction that runs the SELECT statements, then they may or may not be observed (imagine for example that the statement has an ORDER BY clause, and a new row is inserted that comes before the row that the statement is currently positioned at). If records are added by a different transaction (possible in WAL mode), then they definitely will not be observed - that's the whole point of transaction isolation. That is, we've got a UI view that's showing query results, while a background task is adding records to the database, some of which may match our query. We'd like update the query results view with new records as they're added, without having to repeat the whole query and weed out the results we're already showing? Put a timestamp on each row, rerun the query with "timestamp > :lastSeenTimestamp" condition. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Getting query results as new records are added
Hi there, I'm wondering if there's a way to get "incremental" results to a query after it's started, that includes new records added after the query began? That is, we've got a UI view that's showing query results, while a background task is adding records to the database, some of which may match our query. We'd like update the query results view with new records as they're added, without having to repeat the whole query and weed out the results we're already showing? Any suggestions are appreciated. -Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
