Re: [sqlite] select within transaction
On 15/6/19 2:22 AM, Roman Fleysher wrote: > I have a transaction consisting of two commands: update and select. The idea > is to get new state after update: > > PRAGMA busy_timeout = 50; > BEGIN EXCLUSIVE; > UPDATE OR ROLLBACK t SET c = 5 WHERE ...; > SELECT d FROM t WHERE c = 5 AND ...; > COMMIT; > > Is this what will happen: > > 1. Wait for the EXCLUSIVE lock. If not enough time, exit with error. > 2. If lock obtained, attempt to update table t to set c=5. > 3. Regardless (!?) if step 2 was successful or not, execute SELECT to obtain > d. If update failed, then c will not be 5 (it will be old value, different > from 5) and output of SEELCT will be empty. > > Since ROLLBACK is not an error, I want SELECT to be executed only will update > actually happened (not rollback). Because of EXCLUSIVE, I want it to be in > one transaction and thus I need some indicator if SELECT was after successful > update, not rollback. > > Is this what changes() is for? "Successful update" is rather vague. In some contexts, "no rows changed, but no error thrown either" might be considered successful. So there are actually *three* scenarios for your existing code here: 1. UPDATE touches zero rows (WHERE clause matches nothing) -- SELECT happens. 2. UPDATE touches one or more rows (WHERE clause matches something) -- SELECT happens. 3. UPDATE touches one or more rows, but triggers a constraint violation in the process -- ROLLBACK kicks in, SELECT doesn't happen. If you actually want the SELECT to *not* happen in scenario 1, and you *must* use the SQLite shell instead of a proper language binding like the Tcl API (https://sqlite.org/tclsqlite.html), then I think you're stuck. You can sorta get what you want by changing your SELECT statement as follows: SELECT d FROM t WHERE changes() > 0 AND c = 5; which still runs the SELECT, but returns nothing in scenario 1. It's just not very efficient, especially for large tables. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select within transaction
On 15/6/19 3:06 AM, Jose Isaias Cabrera wrote: > Jose Isaias Cabrera, on Friday, June 14, 2019 02:50 PM, wrote... > >> Yes, and no. From what I understand, and have been using it, if >> something was written to the DB, it will give you a 1. Otherwise >> a 0. But, it is not the amount of fields, just a write. ie. > This is wrong information. It does give you the amount of fields updated. Ie. changes() returns the number of *rows* modified, not fields. See https://sqlite.org/c3ref/changes.html for the base API function documentation, which also reveals important details on how it counts changes in various environments (e.g. triggers, multithreaded updates). ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select within transaction
Thank you! I did not know (or forgot) about ".bail on" Roman From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of David Raymond [david.raym...@tomtom.com] Sent: Friday, June 14, 2019 3:05 PM To: SQLite mailing list Subject: Re: [sqlite] select within transaction How are you sending the commands to the cli? If you're doing... sqlite3 myfile.sqlite ".read somefile.sql" ...then you can start the sql file with... .bail on ...and as soon as it hits an error it will stop there and not continue processing lines. So if you get rid of the "or rollback" then you'll get the error message and won't have to worry about it continuing on to the next lines in the input file despite there having been an error. And since you explicitly started a transaction it will leave the transaction open, and then when the CLI closes it will rollback the uncommitted transaction. -Original Message- From: sqlite-users On Behalf Of Roman Fleysher Sent: Friday, June 14, 2019 2:23 PM To: General Discussion of SQLite Database Subject: [sqlite] select within transaction Dear SQLiters, I am using sqlite3 shell. I have a transaction consisting of two commands: update and select. The idea is to get new state after update: PRAGMA busy_timeout = 50; BEGIN EXCLUSIVE; UPDATE OR ROLLBACK t SET c = 5 WHERE ...; SELECT d FROM t WHERE c = 5 AND ...; COMMIT; Is this what will happen: 1. Wait for the EXCLUSIVE lock. If not enough time, exit with error. 2. If lock obtained, attempt to update table t to set c=5. 3. Regardless (!?) if step 2 was successful or not, execute SELECT to obtain d. If update failed, then c will not be 5 (it will be old value, different from 5) and output of SEELCT will be empty. Since ROLLBACK is not an error, I want SELECT to be executed only will update actually happened (not rollback). Because of EXCLUSIVE, I want it to be in one transaction and thus I need some indicator if SELECT was after successful update, not rollback. Is this what changes() is for? Thank you, Roman ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7Ca0e391ba075f446ff99e08d6f0fb5884%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636961359675816752sdata=R2QsP5EZEMtjFyMMJc3xLCeLztLVGohigXa4PLLDBBA%3Dreserved=0 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7Ca0e391ba075f446ff99e08d6f0fb5884%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636961359675816752sdata=R2QsP5EZEMtjFyMMJc3xLCeLztLVGohigXa4PLLDBBA%3Dreserved=0 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select within transaction
Jose Isaias Cabrera, on Friday, June 14, 2019 02:50 PM, wrote... > Yes, and no. From what I understand, and have been using it, if > something was written to the DB, it will give you a 1. Otherwise > a 0. But, it is not the amount of fields, just a write. ie. This is wrong information. It does give you the amount of fields updated. Ie. sqlite> create table a (a, b, c); sqlite> insert into a values (1, 2, 3); sqlite> insert into a values (2, 3, 4); sqlite> insert into a values (3, 4, 5); sqlite> select changes(); 1 sqlite> select total_changes(); 3 sqlite> update a set a=4 where a = 1 or a = 2 or a = 3; sqlite> select changes(); -- all changes made on the table 3 sqlite> select total_changes(); 6 sqlite> Sorry for the bad data. josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select within transaction
How are you sending the commands to the cli? If you're doing... sqlite3 myfile.sqlite ".read somefile.sql" ...then you can start the sql file with... .bail on ...and as soon as it hits an error it will stop there and not continue processing lines. So if you get rid of the "or rollback" then you'll get the error message and won't have to worry about it continuing on to the next lines in the input file despite there having been an error. And since you explicitly started a transaction it will leave the transaction open, and then when the CLI closes it will rollback the uncommitted transaction. -Original Message- From: sqlite-users On Behalf Of Roman Fleysher Sent: Friday, June 14, 2019 2:23 PM To: General Discussion of SQLite Database Subject: [sqlite] select within transaction Dear SQLiters, I am using sqlite3 shell. I have a transaction consisting of two commands: update and select. The idea is to get new state after update: PRAGMA busy_timeout = 50; BEGIN EXCLUSIVE; UPDATE OR ROLLBACK t SET c = 5 WHERE ...; SELECT d FROM t WHERE c = 5 AND ...; COMMIT; Is this what will happen: 1. Wait for the EXCLUSIVE lock. If not enough time, exit with error. 2. If lock obtained, attempt to update table t to set c=5. 3. Regardless (!?) if step 2 was successful or not, execute SELECT to obtain d. If update failed, then c will not be 5 (it will be old value, different from 5) and output of SEELCT will be empty. Since ROLLBACK is not an error, I want SELECT to be executed only will update actually happened (not rollback). Because of EXCLUSIVE, I want it to be in one transaction and thus I need some indicator if SELECT was after successful update, not rollback. Is this what changes() is for? Thank you, Roman ___ 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] select within transaction
Roman Fleysher, on Friday, June 14, 2019 02:22 PM, wrote... > > Since ROLLBACK is not an error, I want SELECT to be executed only will update > actually happened (not rollback). Because of EXCLUSIVE, I want it to be in > one transaction and thus I need some indicator if SELECT was after successful > update, not rollback. > > Is this what changes() is for? Yes, and no. From what I understand, and have been using it, if something was written to the DB, it will give you a 1. Otherwise a 0. But, it is not the amount of fields, just a write. ie. sqlite> create table a (a, b, c); sqlite> create table b (a, d, e); sqlite> insert into a values (1, 2, 3); sqlite> insert into a values (2, 3, 4); sqlite> insert into a values (3, 4, 5); sqlite> select changes(); -- this is for the last write 1 sqlite> select total_changes(); -- this is for the total amount of writes 3 sqlite> insert into a values (4, 5, 6); sqlite> select changes(); 1 sqlite> select total_changes(); 4 I hope this helps. josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] select within transaction
Dear SQLiters, I am using sqlite3 shell. I have a transaction consisting of two commands: update and select. The idea is to get new state after update: PRAGMA busy_timeout = 50; BEGIN EXCLUSIVE; UPDATE OR ROLLBACK t SET c = 5 WHERE ...; SELECT d FROM t WHERE c = 5 AND ...; COMMIT; Is this what will happen: 1. Wait for the EXCLUSIVE lock. If not enough time, exit with error. 2. If lock obtained, attempt to update table t to set c=5. 3. Regardless (!?) if step 2 was successful or not, execute SELECT to obtain d. If update failed, then c will not be 5 (it will be old value, different from 5) and output of SEELCT will be empty. Since ROLLBACK is not an error, I want SELECT to be executed only will update actually happened (not rollback). Because of EXCLUSIVE, I want it to be in one transaction and thus I need some indicator if SELECT was after successful update, not rollback. Is this what changes() is for? Thank you, Roman ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT INTO ... ON CONFLICT(...) DO UPDATE ...
On 6/14/19, Olivier Mascia wrote: > Dear, > > Assuming no explicit transaction, do statements like: > > INSERT INTO ... ON CONFLICT(...) DO UPDATE ... > > are treated completely within a _single_ implicit transaction? Yes > And is this _single_ implicit transaction of type IMMEDIATE? Yes. -- 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
[sqlite] INSERT INTO ... ON CONFLICT(...) DO UPDATE ...
Dear, Assuming no explicit transaction, do statements like: INSERT INTO ... ON CONFLICT(...) DO UPDATE ... are treated completely within a _single_ implicit transaction? And is this _single_ implicit transaction of type IMMEDIATE? — Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten Grüßen, Olivier Mascia ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
On 6/14/19 7:15 AM, R Smith wrote: > > On 2019/06/14 4:23 AM, Richard Damon wrote: >> On 6/13/19 10:51 AM, R Smith wrote: >>> On 2019/06/13 4:44 PM, Doug Currie wrote: > Except by the rules of IEEE (as I understand them) > > -0.0 < 0.0 is FALSE, so -0.0 is NOT "definitely left of true zero" > Except that 0.0 is also an approximation to zero, not "true zero." Consider that 1/-0.0 is -inf whereas 1/0.0 is +int >>> >>> I do not know if this is the result case in any of the programming >>> languages, but in Mathematical terms that is just not true. >>> >>> 1/0.0 --> Undefined, doesn't exist, cannot be computed, Should error >>> out. Anything returning +Inf or -Inf is plain wrong. >>> I posit the same holds true for 1/-0.0 >> Yes, 1.0/0.0 is undefined in the Field of Real numbers, but IEEE isn't >> the field of Real Numbers. First, as pointed out, it has limited >> precision, but secondly it have values that are not in the field of Real >> Numbers, namely NaN and +/-Inf. >> >> Note, that with a computer, you need to do SOMETHING when asked for >> 1.0/0.0, it isn't good to just stop (and traps/exceptions are hard to >> define for general compution systems), so defining the result is much >> better than just defining that anything could happen. It could have been >> defined as just a NaN, but having a special 'error' value for +Inf or >> -Inf turns out to be very useful in some fields. > > I wasn't advocating to do something weird when the value -0.0 exists > in memory - the display of that is what the greater idea behind this > thread is[**]. > > What I was objecting to, is claiming (in service of suggesting the > use-case for -0.0), that the mathematical result of 1/-0.0 IS in fact > "-Inf" and so computers should conform, when it simply isn't, it's an > error and SHOULD be shown so. Neither is the mathematical result of > 0/-1 = -0.0. It simply isn't mathematically true (or rather, it isn't > distinct from 0.0), and I maintain that any system that stores -0.0 as > the result of the computation of 0/-1 is simply doing so by virtue of > the computational method handling the sign-bit separate from the > division and being able to store it like so by happenstance of IEEE754 > allowing -0.0 as a distinct value thanks to that same sign bit, and > not because it ever was mathematically necessary to do so. > > I'll be happy to eat my words if someone can produce a mathematical > paper that argued for the inclusion of -0.0 in IEEE754 to serve a > mathematical concept. It's a fault, not a feature. > > > [** As to the greater question of representation - In fact I'm now a > bit on the fence about it. It isn't mathematical, but it does help > represent true bit-data content. I'm happy with it both ways.] I was pointing out that it depends on WHICH type of mathematics you are talking about what is the proper result of 1/0. If you have your mind wrapped around the idea the 'Floating Point' == 'Real Numbers', then it doesn't make sense, but it is a best a rough approximation, expressing not all of the Reals, but also expressing some things that are outside the domain of the Reals. A simple example, 1.0 / 3.0 * 3.0 - 1.0 should be exactly 0.0 in the domain of real numbers. It will NOT be in the domain of Floating Point numbers (because 1.0 / 3.0 can not be exactly represented). You can't even say the results will be 'close' to zero, as there is no expressible tolerance based just on the final expected answer, as you could replace the 1.0 with a billion, or a billionth, and get very different values, all of which would need to be considered 'close'. My understanding is that IEEE COULD have defined 1/0 as NaN instead, but there were significant areas of numerical calculation where remembering the infinity, and then using the fact that n / inf is 0 gave meaningful answers in some cases. (or the atan(inf) = pi/2). -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
> I'll be happy to eat my words if someone can produce a mathematical paper that argued for the inclusion of -0.0 in IEEE754 to serve a mathematical concept. It's a fault, not a feature. There are indeed very few use cases. The most common one is dealing with water temperature. You can have water at 0 C and ice at 0 C, both states differ only by latent heat. It's one of the rare cases where you could 0 and -0 to distinguish between phases. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
On Fri, Jun 14, 2019 at 7:16 AM R Smith wrote: > > What I was objecting to, is claiming (in service of suggesting the > use-case for -0.0), [...] > > I'll be happy to eat my words if someone can produce a mathematical > paper that argued for the inclusion of -0.0 in IEEE754 to serve a > mathematical concept. It's a fault, not a feature. > David Goldberg's classic paper "What Every Computer Scientist Should Know About Floating-Point Arithmetic" has a section on this topic, 2.2.3 Slgned Zero, with a few use cases. W. Kahan's early papers on standardizing floating point uses the term "affine mode" to describe when signed zeros and infinities matter (as opposed to "projective mode"). E.g., ON A PROPOSED FLOATING-POINT STANDARD W. Kahan University of California, Berkeley J. Palmer INTEL Corporation, Aloha, Oregon October 1, 1979 e ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?
On 2019/06/14 4:23 AM, Richard Damon wrote: On 6/13/19 10:51 AM, R Smith wrote: On 2019/06/13 4:44 PM, Doug Currie wrote: Except by the rules of IEEE (as I understand them) -0.0 < 0.0 is FALSE, so -0.0 is NOT "definitely left of true zero" Except that 0.0 is also an approximation to zero, not "true zero." Consider that 1/-0.0 is -inf whereas 1/0.0 is +int I do not know if this is the result case in any of the programming languages, but in Mathematical terms that is just not true. 1/0.0 --> Undefined, doesn't exist, cannot be computed, Should error out. Anything returning +Inf or -Inf is plain wrong. I posit the same holds true for 1/-0.0 Yes, 1.0/0.0 is undefined in the Field of Real numbers, but IEEE isn't the field of Real Numbers. First, as pointed out, it has limited precision, but secondly it have values that are not in the field of Real Numbers, namely NaN and +/-Inf. Note, that with a computer, you need to do SOMETHING when asked for 1.0/0.0, it isn't good to just stop (and traps/exceptions are hard to define for general compution systems), so defining the result is much better than just defining that anything could happen. It could have been defined as just a NaN, but having a special 'error' value for +Inf or -Inf turns out to be very useful in some fields. I wasn't advocating to do something weird when the value -0.0 exists in memory - the display of that is what the greater idea behind this thread is[**]. What I was objecting to, is claiming (in service of suggesting the use-case for -0.0), that the mathematical result of 1/-0.0 IS in fact "-Inf" and so computers should conform, when it simply isn't, it's an error and SHOULD be shown so. Neither is the mathematical result of 0/-1 = -0.0. It simply isn't mathematically true (or rather, it isn't distinct from 0.0), and I maintain that any system that stores -0.0 as the result of the computation of 0/-1 is simply doing so by virtue of the computational method handling the sign-bit separate from the division and being able to store it like so by happenstance of IEEE754 allowing -0.0 as a distinct value thanks to that same sign bit, and not because it ever was mathematically necessary to do so. I'll be happy to eat my words if someone can produce a mathematical paper that argued for the inclusion of -0.0 in IEEE754 to serve a mathematical concept. It's a fault, not a feature. [** As to the greater question of representation - In fact I'm now a bit on the fence about it. It isn't mathematical, but it does help represent true bit-data content. I'm happy with it both ways.] ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
