Re: [sqlite] 18 minutes 41 seconds
> > There is no "year 0" between 1 BC and 1 AD. This is perhaps the most > common fencepost problem in existance. The "great renaming" of AD to CE > and doing away with BC by replacing them with "off by one" numbers less > than 1 does not change the fact that there was, in fact, no year 0. Obviously the character(s) responsible for dates etc were NOT C programmers! On Tue, 31 Dec 2019 at 14:45, Richard Damon wrote: > On 12/30/19 10:10 PM, Pierpaolo Bernardi wrote: > > On Tue, Dec 31, 2019 at 4:07 AM Keith Medcalf > wrote: > >> > >> On Monday, 30 December, 2019 19:29, Michael Falconer < > michael.j.falco...@gmail.com> wrote: > >> > >>> As we approach the end of yet another year ( and indeed decade ). > >> Technically, every year is the end of a decade, if one means the > immediately preceding ten years. > >> > >> However, if you mean the end of the second decade of the 21st century, > you will have to wait another year for that. January 1st, 0001 AD was the > first day of the year 1. The first decade ended at the end of December > 31st 0011 AD, not December 31st, 0010 AD. (if following the proleptic > Gregorian calendar). > > Languages don't work like this. > > > > https://www.collinsdictionary.com/dictionary/english/decade > > > > Cheers > > Its a difference between ordinals and numerals. The 20th century was > from the beginning of 1901 to the end of 2000. We also have the century > called the 1900's which went from 1900 to the end of 1999. > > Decade would work the same way, the 202st decade goes from 2011 to end > of 2020, but the 2010s go from 2010 to end of 2019. > > -- > Richard Damon > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Regards, Michael.j.Falconer. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 18 minutes 41 seconds
On 12/30/19 10:10 PM, Pierpaolo Bernardi wrote: On Tue, Dec 31, 2019 at 4:07 AM Keith Medcalf wrote: On Monday, 30 December, 2019 19:29, Michael Falconer wrote: As we approach the end of yet another year ( and indeed decade ). Technically, every year is the end of a decade, if one means the immediately preceding ten years. However, if you mean the end of the second decade of the 21st century, you will have to wait another year for that. January 1st, 0001 AD was the first day of the year 1. The first decade ended at the end of December 31st 0011 AD, not December 31st, 0010 AD. (if following the proleptic Gregorian calendar). Languages don't work like this. https://www.collinsdictionary.com/dictionary/english/decade Cheers Its a difference between ordinals and numerals. The 20th century was from the beginning of 1901 to the end of 2000. We also have the century called the 1900's which went from 1900 to the end of 1999. Decade would work the same way, the 202st decade goes from 2011 to end of 2020, but the 2010s go from 2010 to end of 2019. -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 18 minutes 41 seconds
On Tue, Dec 31, 2019 at 4:07 AM Keith Medcalf wrote: > > > On Monday, 30 December, 2019 19:29, Michael Falconer > wrote: > > > As we approach the end of yet another year ( and indeed decade ). > > Technically, every year is the end of a decade, if one means the immediately > preceding ten years. > > However, if you mean the end of the second decade of the 21st century, you > will have to wait another year for that. January 1st, 0001 AD was the first > day of the year 1. The first decade ended at the end of December 31st 0011 > AD, not December 31st, 0010 AD. (if following the proleptic Gregorian > calendar). Languages don't work like this. https://www.collinsdictionary.com/dictionary/english/decade Cheers ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 18 minutes 41 seconds
On Monday, 30 December, 2019 19:29, Michael Falconer wrote: > As we approach the end of yet another year ( and indeed decade ). Technically, every year is the end of a decade, if one means the immediately preceding ten years. However, if you mean the end of the second decade of the 21st century, you will have to wait another year for that. January 1st, 0001 AD was the first day of the year 1. The first decade ended at the end of December 31st 0011 AD, not December 31st, 0010 AD. (if following the proleptic Gregorian calendar). The day before January 1st 0001 AD was December 31st 0001 BC. The first day of the 21st century was January 1st, 2001; it was also the first day of the first decade of the 21st century. The first day of the 2nd decade of the 21st century was January 1st, 2011. Next year is the last year of the 2nd decade of the 21st century. The 3rd decade of the 21st century will commence at midnight January 1st, 2021. There is no "year 0" between 1 BC and 1 AD. This is perhaps the most common fencepost problem in existance. The "great renaming" of AD to CE and doing away with BC by replacing them with "off by one" numbers less than 1 does not change the fact that there was, in fact, no year 0. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Causal profiling
On 31 Dec 2019, at 2:21am, Alexander Vega wrote: > Could there be some compile time option to force Linux or Windows > statically? One of the problems with this is that there are a lot of utility libraries built around SQLite. These are libraries provided for general-purpose use, often a language shim to make it easy to call SQLite from some language which isn't C or C++. Compiler options which make SQLite behave significantly different generate fault reports (support calls, messages to this list, etc.). Because someone gets the 'different' version of SQLite and wonders why it doesn't do what the version they're used to does. If SQLite3 ever moves to SQLite4, a big change in behaviour may be acceptable: people are used to that in full version increases. But while SQLite remains version 3.x.x, an option like that would have to be thought through very carefully. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 18 minutes 41 seconds
Great work but pretty much what we have come to expect from DRH and the SQLite team. As we approach the end of yet another year ( and indeed decade ) can I indulge the list in a simple congratulations to all involved and to the outstanding support on offer when a member of this email list. SQLite is personally my favourite software release and IMHO right up there as the most useful and reliable software of all time! Thanks to all who have made it possible. On Tue, 31 Dec 2019 at 11:13, Richard Hipp wrote: > That's the total elapse time from me checking in a bug (check-in > https://www.sqlite.org/src/info/40d10e7aad5b8992) until Manuel > Rigger's fuzzer had located the bug and issued a ticket against it: > (ticket https://www.sqlite.org/src/info/892575cdba4e1e36). > > Well, at least the bisect didn't take very long! > > -- > 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 > -- Regards, Michael.j.Falconer. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Causal profiling
I watched the video and wondered about the virtual table calls within sqlite as well. Without them you would have no VFS though... so they are needed. Could there be some compile time option to force Linux or Windows statically? Maybe. I do not know the effect of collecting debug information using -g, but if using -O2 to compile sqlite with GCC the following optimizations from https://gcc.gnu.org/onlinedocs/gcc/Optimize-Options.html#Optimize-Options are enabled: -fdevirtualize -fdevirtualize-speculatively So depending on what compiler used and version etc, those virtual calls could all disappear anyway. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 18 minutes 41 seconds
That's the total elapse time from me checking in a bug (check-in https://www.sqlite.org/src/info/40d10e7aad5b8992) until Manuel Rigger's fuzzer had located the bug and issued a ticket against it: (ticket https://www.sqlite.org/src/info/892575cdba4e1e36). Well, at least the bisect didn't take very long! -- 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
Re: [sqlite] SQL help
x, on Saturday, December 28, 2019 10:46 AM, wrote... > Apologies if that’s not worded correctly. Scottish education could be > done gooder Wrong English! You should have said, "Scottish education could have been done gooder." Com'on! This is not even my first language! :-) josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug Report
Bigthing Do, on Friday, December 27, 2019 01:56 PM, wrote... > > Dear sqlite developers: > > We met an accidental crash in sqlite with the following sample: > > CREATE VIEW table1 ( col1 , col2 ) AS WITH aaa AS ( SELECT * FROM table1 > ) SELECT col2 FROM table1 ORDER BY 1 ; > WITH aaa AS ( SELECT * FROM table1 ) SELECT col1 , rank () OVER( ORDER BY > col1 DESC ) FROM table1 ; > > > We are using release version of sqlite: `SQLite version 3.30.1 2019-10-10 > 20:19:45` Also with 3.30.0... 16:41:27.70>sqlite3 SQLite version 3.30.0 2019-10-04 15:03:17 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE VIEW table1 ( col1 , col2 ) AS WITH aaa AS ( SELECT * FROM table1 ) SELECT col2 FROM table1 ORDER BY 1 ; sqlite> WITH aaa AS ( SELECT * FROM table1 ) SELECT col1 , rank () OVER( ORDER BY col1 DESC ) FROM table1 ; 16:42:07.53> josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Causal profiling
> On Dec 30, 2019, at 7:19 AM, Doug wrote: > > I am suggesting that if the we added the global calls to the underlying > functions to the API - that is, the functions that are called by the function > table indirection - then one could code the application to call the > underlying functions. What you're describing is basically the SQLITE_CONFIG_SINGLETHREAD compile option. In that configuration SQLite does not use any mutexes, and you're responsible for "call[ing] the underlying functions" (the mutex lock/unlock functions) yourself. However, in this configuration any client code that makes concurrent calls to SQLite — and there is a lot of code like that — will crash and burn. > If you are going to tell me that you need to maintain two versions of your > application if you run it in a single-thread environment or a multi-thread > environment I don't know if someone else was saying that; what I was pointing out earlier is that many clients of SQLite link to it as a shared library provided by the OS, either built-in (as on iOS and macOS) or installed by a central package manager (Linux). This means they don't specify compile-time options, they implicitly get the behavior configured by the OS or package vendor, which I'd guess is probably MULTITHREAD. If the SQLite they link with changes its behavior to avoid mutexes, many of these programs will break. > Thinking about it, I'm surprised that the C API isn't just a set of macros > already. What you're describing is basically the effect of link-time optimization (LTO). The optimizer runs over the entire program's compiled code (not just one compilation unit) and applies optimizations such as inlining. This can indeed have a big impact. —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Causal profiling
On 12/30/19 10:19 AM, Doug wrote: OK, I get that the definition of the SQLite API is a (large) set of C function calls. And that changing the way they work under the covers would break something. (Check out the IDEA below!) I'm wondering if your use case is - in the same application - that you dynamically change from option SQLITE_CONFIG_SINGLETHREAD to SQLITE_CONFIG_MULTITHREAD to SQLITE_CONFIG_SERIALIZED while your application is running? If that is the case, then your application knows which option to use dynamically; Otherwise, your application doesn't know and doesn't care which option is in effect. I am suggesting that if the we added the global calls to the underlying functions to the API - that is, the functions that are called by the function table indirection - then one could code the application to call the underlying functions. If the application knows it's single-thread, then code it that way and get a 25% improvement (see the talk). If the application makes the choice of thread option dynamically, then the penalty for single-thread is at least double (application choice, SQLite indirection), so calling the (new) underlying function once you made the choice, performs better for that path. I grant you that probably will see little improvement on the threaded path. If you are going to tell me that you need to maintain two versions of your application if you run it in a single-thread environment or a multi-thread environment, then let's define the (new) API to use a preprocessor macro to generate the right code for the option selected based on the values of SQLITE_CONFIG_SINGLETHREAD, el al. So now you have a single source but multiple executables corresponding to that source. And the choice of which executable to use becomes a configuration problem at application deployment time, or at application run time. IDEA! Thinking about it, I'm surprised that the C API isn't just a set of macros already. I can visualize a C API composed of a set of macro definitions _identical_ to the current C function calls. They just use the extra knowledge of SQLITE_THREADSAFE and other SQLite compiler options to decide what application code to generate. Then the formal API doesn't change from a coding point of view. The generated code calls a set of under-the-cover functions which are not part of the API. The change doesn't require a new layer of testing; presumably, we already have test cases that test the same code using different compiler options. What about that? Best, Doug One BIG reason the C API can't be made into a set of macros is that the C API is basically the multi-language API for using the SQLite shared library in other languages. This requires that the C API functions be REAL functions that provide entry points into the shared library. It also, perhaps as a much more minor point, prevents taking the address of those functions to use in the application either to pass SQLite routines as call backs or make build your own virtual functions (admittedly, I don't know of a case where you would really want to do that). I suspect that by far the vast majority of SQLite uses don't bundle the SQLite source code into the project, but link to it as an external resource. Yes, there is perhaps an option to provide some specific configuration macros to allow SQLite to be optimized when included statically in a project, but those options shouldn't change the API. -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Causal profiling
On Wed, Dec 25, 2019 at 4:25 PM Richard Hipp wrote: > > The video provided details on what they did. I could not find any > performance improvement by making mutexes direct calls instead of > indirect calls. This kind of optimization improves performance by reducing pressure on the CPU's branch branch-target address caching and prediction. > Maybe they are measuring performance differently than > me. (I use CPU cycle counts measured by valgrind.) > By default, valgrind doesn't model either branch predictors or branch-target address caches. Its model is somewhat primitive[0], but it is available through command-line option `--branch-sim` [1,2]. When you performance tested this change, did you enable that option? tool/run-speed-test.sh certainly doesn't. Of course, since those structures are shared globally, the performance benefit for de-virtualizing any given function call is highly context dependent. Sincerely, -- Jonathan Brandmeyer [0]: https://valgrind.org/docs/manual/cg-manual.html#branch-sim [1]: https://www.valgrind.org/docs/manual/cl-manual.html#cl-manual.options.simulation [2]: https://valgrind.org/docs/manual/cg-manual.html#cg-manual.cgopts ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Causal profiling
OK, I get that the definition of the SQLite API is a (large) set of C function calls. And that changing the way they work under the covers would break something. (Check out the IDEA below!) I'm wondering if your use case is - in the same application - that you dynamically change from option SQLITE_CONFIG_SINGLETHREAD to SQLITE_CONFIG_MULTITHREAD to SQLITE_CONFIG_SERIALIZED while your application is running? If that is the case, then your application knows which option to use dynamically; Otherwise, your application doesn't know and doesn't care which option is in effect. I am suggesting that if the we added the global calls to the underlying functions to the API - that is, the functions that are called by the function table indirection - then one could code the application to call the underlying functions. If the application knows it's single-thread, then code it that way and get a 25% improvement (see the talk). If the application makes the choice of thread option dynamically, then the penalty for single-thread is at least double (application choice, SQLite indirection), so calling the (new) underlying function once you made the choice, performs better for that path. I grant you that probably will see little improvement on the threaded path. If you are going to tell me that you need to maintain two versions of your application if you run it in a single-thread environment or a multi-thread environment, then let's define the (new) API to use a preprocessor macro to generate the right code for the option selected based on the values of SQLITE_CONFIG_SINGLETHREAD, el al. So now you have a single source but multiple executables corresponding to that source. And the choice of which executable to use becomes a configuration problem at application deployment time, or at application run time. IDEA! Thinking about it, I'm surprised that the C API isn't just a set of macros already. I can visualize a C API composed of a set of macro definitions _identical_ to the current C function calls. They just use the extra knowledge of SQLITE_THREADSAFE and other SQLite compiler options to decide what application code to generate. Then the formal API doesn't change from a coding point of view. The generated code calls a set of under-the-cover functions which are not part of the API. The change doesn't require a new layer of testing; presumably, we already have test cases that test the same code using different compiler options. What about that? Best, Doug > -Original Message- > From: sqlite-users > On Behalf Of Jens Alfke > Sent: Friday, December 27, 2019 2:22 PM > To: SQLite mailing list > Subject: Re: [sqlite] Causal profiling > > > > > On Dec 26, 2019, at 3:45 PM, Doug wrote: > > > > Can you explain what the API is that you use to "set concurrency > levels per connection", please? Is it a parameter on open() or its > own function call? And how would the API break, exactly? > > sqlite3_config(), specifically the SQLITE_CONFIG_SINGLETHREAD, > SQLITE_CONFIG_MULTITHREAD, SQLITE_CONFIG_SERIALIZED options. > > This API would break because configuring those options at runtime > would have no effect on behavior; the only thing that would change > threading behavior would be the compile-time flags > SQLITE_MUTEX_OMIT, etc. > > (This is actually global, not per-connection, but that doesn't > invalidate what I said.) > > > The talk suggested removing the SQLite virtual table of > functions (specifically the call to free a mutex). The user calls > the function directly. How does that break an API? > > If SQLite's implementation directly called the mutex lock/unlock > functions, instead of indirecting, then there would be no way to > control whether or not mutexes were used. In other words, it would > be impossible to change any of the above options at runtime. > > > The talk suggested removing the SQLite virtual table of > functions (specifically the call to free a mutex). The user calls > the function directly. How does that break an API? > > No, the user does not call those functions directly. The code > shown in the video is deep inside SQLite itself and not visible > through the API. (You say you're using a TCL wrapper … so you may > not be aware of what is or isn't in the C API. Trust me, I use the > C API a lot.) > > —Jens > > ___ > 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