Re: [sqlite] How does one block a reader connection?
On 26 March 2017 at 14:17, Keith Medcalfwrote: > If you do not specify your own custom busy handler (to display flying ball > bearings, etc, or do your own exponential sleeping, etc) then the default > busy_handler is used. The default busy handler does its own exponential > backoff algorithm but does not display whirling ball bearings or anything > else, it just sleeps silently. Nitpick: the "default" busy_handler is in fact not used by default. That is, if you open a connection and don't specify a busy handler or timeout then you will get SQLITE_BUSY returned immediately if a lock cannot be acquired. There are essentially three busy handler states: (1) Normal settings: no busy handler is active (2) sqlite3_busy_timeout()/PRAGMA timeout invoked: a sleep() with exponential backoff busy handler is used (3) sqlite3_busy_handler() invoked: the user defined busy handler is used Sqlite implements the timeout feature by installing a busy_handler behind the scenes (which is called the "default" busy handler in the code). There's only one busy_handler per connection though, so whichever is called last out of sqlite3_busy_handler() or sqlite3_busy_timeout() will take precedence. -Rowan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] table-naming-expression impact on sqlite3_prepare
On 27 Mar 2017, at 1:52am, Kees Nuytwrote: > It's something that can be done by any host language. No need to implement > that in SQL. Also, you’re scripting a shell tool. So write a text file with your SQL commands in and feed it to the shell tool whole. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] table-naming-expression impact on sqlite3_prepare
On Sun, 26 Mar 2017 15:34:22 -0700, peternwrote: > Here is your suggestion with matched brackets and quotes and assuming > mytable has a column [tablename]: > > select eval(printf('create table %s (a,b,c)',tablename)) from mytables; I think you mean: eval(printf('create table %s (a,b,c)',(select tablename from mytables))); It's something that can be done by any host language. No need to implement that in SQL. -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] table-naming-expression impact on sqlite3_prepare
27 mrt 2017, petern: In general I've been thinking about materializing data dependent temporary tables and even using them in CTE's. The tremendous expressive economy of TCL and somewhat built-in support within SQLite got me thinking. Consider the problem of pivot table function for creating temp.crosstab_* summary tables... I left out the example command line script from you reply. This clarifies your somewahat abstract requirement. Now I also see why earlier on you wished eval() to return query result. But, as eval() does ddl too I just make it define a temp view. That can be selected from afterwards. See below. Thanks for the TCL example that helps me learn. Best regards, E. Pasma, sponsor of https://facebook.com/RadioParadijs .version SQLite 3.18.0 2017-03-06 20:44:13 ec529bf11b16c801ea438e57d208ff7e4cedf1f9 select load_extension('eval'); drop table if exists sales; create table sales(year integer,fruit text,tons integer); insert into sales values(2017,'mangos',3),(2016,'peaches',2),(2017,'apples',7), (2017,'peaches',6),(2016,'mangos',12); --select * from sales; select ddl, eval(ddl) from( select 'create temp view vttt as select year, ' || group_concat('sum(case when fruit=''' || fruit || ''' then tons end) as ' || fruit) || ' from sales group by year' as ddl from( select fruit from sales group by fruit) ) ; create temp view vttt as select year, sum(case when fruit='apples' then tons end) as apples,sum(case when fruit='mangos' then tons end) as mangos,sum(case when fruit='peaches' then tons end) as peaches from sales group by year| .header on select * from vttt; year|apples|mangos|peaches 2016||12|2 2017|7|3|6 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Syntax. table-function-name vs table-naming-function-name
Richard, thank you for your reply. I really appreciate it. The fact that you have carefully thought about how to cross the FROM clause barrier with expressions is itself a useful fact. If you say the current implementation is painted into a corner on this issue I believe you. It would be impossible to deduce this fact by simply studying documentation or source code. Thank you very much for taking my question. At some point I may try what you suggest and see what I can learn. In the meantime, the CSV virtual table example appears to be closest to where I'd like to go next. https://sqlite.org/csv.html That example allows the caller to specify a creation schema. Expanding on the schema specifier, perhaps there is a way to make xBestIndex() and xFilter() work more generally in the problem domain of lightly encapsulated dynamic tables. On Sun, Mar 26, 2017 at 11:44 AM, Richard Hippwrote: > On 3/25/17, petern wrote: > > > > Why can't we have a parallel syntax branch for scalar valued > > "table-naming-function-name"? In other words, why not have support for > > simply naming an existing table or view by return value of a scalar > > function? > > > > The easiest way for me to answer this is to ask you to provide a > sample implementation. After you've work on the problem for a while, > I think you will begin to understand why it is not possible. I can't > seem to come up with words to help make that realization any easier. > > -- > 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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] table-naming-expression impact on sqlite3_prepare
That you for your kind comment about my table-naming-expression proposal. Here is your suggestion with matched brackets and quotes and assuming mytable has a column [tablename]: select eval(printf('create table %s (a,b,c)',tablename)) from mytables; Significant credit should also go to the built in printf function. In general I've been thinking about materializing data dependent temporary tables and even using them in CTE's. The tremendous expressive economy of TCL and somewhat built-in support within SQLite got me thinking. Consider the problem of pivot table function for creating temp.crosstab_* summary tables... - $ tclsh % load ./tcldbshell.so sqlite3 % sqlite3 db :memory: % proc createpivotsum {table row column value} { set outputtable "temp.crosstab\_$table\_$row\_$column\_$value"; set q [list "select $row"]; db eval "SELECT DISTINCT ($column)col FROM $table" cols {lappend q "sum(case when $column='$cols(col)' then $value else 0 end)$cols(col)"}; db eval "DROP TABLE IF EXISTS $outputtable; CREATE TABLE $outputtable AS [join $q ,] FROM $table GROUP BY $row;"; return $outputtable; }; % db function pivotsum -deterministic -argcount 4 createpivotsum; % #Wouldn't it be great if this TCL function could be called from the regular shell? % #Standard libtclsqlite can't invoke shell.c yet so I added a shell call to libtclsqlite. % db shell; sqlite> --Let's run a fruit stand. sqlite> drop table if exists sales; create table sales(year integer,fruit text,tons integer); insert into sales values(2017,'mangos',3),(2016,'peaches',2),(2017,'apples',7),(2017,'peaches',6),(2016,'mangos',12); sqlite> select * from sales; year,fruit,tons 2017,mangos,3 2016,peaches,2 2017,apples,7 2017,peaches,6 2016,mangos,12 sqlite> --What are current sales by year and type of fruit? select pivotsum('sales','year','fruit','tons'); "pivotsum('sales','year','fruit','tons')" temp.crosstab_sales_year_fruit_tons sqlite> select * from crosstab_sales_year_fruit_tons; year,mangos,peaches,apples 2016,12,2,0 2017,3,6,7 sqlite> --Try doing pivot stuff using in CTA noting that pivotsum is deterministic! with t as (select (select pivotsum('sales','year','fruit','tons')),* from crosstab_sales_year_fruit_tons) ...> select * from t; Error: database table is locked sqlite> --So much for CTE's and deterministic column functions... sqlite> .exit % #now back in tclsh % exit $ - For those that are interested I used slightly modified versions of tclsqlite.c and shell.c to build libtclsqlite. About 10 lines changed including the #ifndef TCL_DB_SHELL statements. On Sun, Mar 26, 2017 at 10:36 AM, E.Pasmawrote: > 26-03-2017 petern : > > > The table-naming-expression, if > > normal expressions are allowed, would obviously require sqlite3_prepare > to > > consult the database in situations where the name string expression > depended > > on a SQL statement being evaluated. Is this the main problem with > allowing > > dynamically named tables SQLite? > > Hi, in an earliar post you mentioned the loadable extension eval.c that > adds > the eval() SQL function. Just for my understanding, may I assume that you > currently use that as a work around. E.g. > select eval(printf("create table %s (a,b,c)', tablename) from mytables; > > Anyway thanks for mentioning the existense of eval(). E. Pasma > > ___ > 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] Syntax. table-function-name vs table-naming-function-name
On 3/25/17, peternwrote: > > Why can't we have a parallel syntax branch for scalar valued > "table-naming-function-name"? In other words, why not have support for > simply naming an existing table or view by return value of a scalar > function? > The easiest way for me to answer this is to ask you to provide a sample implementation. After you've work on the problem for a while, I think you will begin to understand why it is not possible. I can't seem to come up with words to help make that realization any easier. -- 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] table-naming-expression impact on sqlite3_prepare
26-03-2017 petern : > The table-naming-expression, if > normal expressions are allowed, would obviously require sqlite3_prepare to > consult the database in situations where the name string expression depended > on a SQL statement being evaluated. Is this the main problem with allowing > dynamically named tables SQLite? Hi, in an earliar post you mentioned the loadable extension eval.c that adds the eval() SQL function. Just for my understanding, may I assume that you currently use that as a work around. E.g. select eval(printf("create table %s (a,b,c)', tablename) from mytables; Anyway thanks for mentioning the existense of eval(). E. Pasma ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] table-naming-expression impact on sqlite3_prepare
My thanks to everyone who responded to my read blocking transaction isolation question. Further to my other question/proposal with no responses, what would be the impact on sqlite3_prepare to introduce a new branch called table-naming-expression into the syntax graph at: https://www.sqlite.org/syntax/table-or-subquery.html ? [I hope everybody had a chance to see my original narrower proposal called table-naming-function-name. I didn't see my message posted to the board that time but it was definitely sent.] The table-naming-expression, if normal expressions are allowed, would obviously require sqlite3_prepare to consult the database in situations where the name string expression depended on a SQL statement being evaluated. Is this the main problem with allowing dynamically named tables SQLite? Is there a design goal which states that sqlite3_prepare shall never return a lock related error message such as SQLITE_BUSY? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How does one block a reader connection?
Keith, I understand your point. The timescale of polling is between 1 and 10 seconds by sleep loop depending on operational objectives. This range of sleep loop will have a corresponding latency of between 0.5 and 5 seconds for single commands with a uniform arrival time distribution. The idea was to get near the 0.5 second latency of the 1 second sleep loop while actually polling in a very gentle 10 second sleep loop. Thanks for the colorful explanation of custom busy handlers. On Sat, Mar 25, 2017 at 11:17 PM, Keith Medcalfwrote: > Saturday, 25 March, 2017 23:44. petern > wrote: > > > Can anybody explain the purpose of > > http://sqlite.org/c3ref/busy_handler.html > > ? It seems the only practical use would be to allow the caller to give > > the engine a suggested lock deadline before SQLITE_BUSY is returned by > > sqlite3_exec or sqlite3_step Then, if the calling thread would prefer > to > > wait longer, it can just retry with progressively smaller lock deadline > > until it's time to do something else. > > The busy handler is used if you have set a timeout to obtain a lock. If > you try to do > > BEGIN IMMEDIATE > > on a database that is not in WAL mode, then in order to obtain a lock > there must be no active readers or writers. If there are, the lock cannot > be obtained an SQLITE_BUSY is returned and you try again later. If you set > a timeout with PRAMGA timeout, that specifies the amount of time to wait > for a lock before returning SQLITE_BUSY. The busy handler is used to > "handle" the "busy" state (that is, rather than return SQLITE_BUSY to the > caller, your busy handler is called. You do something (like wait a while > using sleep, then return, perhaps displaying flying ball bearings or other > annoyances to indicate to the user that the program is waiting). Lather > rinse repeat until the specified timeout expires at which time SQLITE_BUSY > is returned to the caller. > > If you do not specify your own custom busy handler (to display flying ball > bearings, etc, or do your own exponential sleeping, etc) then the default > busy_handler is used. The default busy handler does its own exponential > backoff algorithm but does not display whirling ball bearings or anything > else, it just sleeps silently. > > > In my application it is more ideal if commands in the command table are > > known to the readers immediately but without the overhead of rapid > polling > > by the readers. Setting a suggested wait time and then waiting again if > > SQLITE_BUSY comes back early and there's nothing else to do would be just > > fine. > > Again, you have specified "RAPID POLLING". Can you please define what you > mean by "RAPID POLLING"? To me, "RAPID POLLING" means using the immensely > stupid spinlock type implementation such as: > > open connection > while (nothing to do) >query database for something to do > do what needs doing > > If you need things to go "RAPIDLY" then you need to use some IPC > notification mechanism. > > Please state in explicit terms your definition of "RAPIDLY". Using terms > without defining them makes it difficult for anyone to understand what you > are talking about. One persons "RAPIDLY" may mean "after the MoC is filled > out and endorsed, operations windows are arranged, and the board operator > gives the final go ahead -- in other words, two weeks to six months". > Another person's definition of "RAPIDLY" may be "within 250 milliseconds". > Please specify what you definition of "RAPIDLY" is, giving minimum and > maximum bounds in common time units. > > If RAPIDLY means "in less than one second, 33% of the time, within two > seconds 75% of the time, and no more than three seconds ever" then you are > talking about rather SLOW POLLING. > > open connection > while (nothing to do) >sleep 1 second >query database for something to do > do what needs doing > > This type of SLOW POLLING will use about 1% of the available CPU on a 4.77 > Mhz 8086 CPU while in the polling loop. This means that you can have about > 100 such processes polling at the same time on a 4.77 Mhz 8086 equipped > computer. On a modern CPU with a couple of cores and about 2Ghz per code, > you should be able to run many tens of thousands of such processes > simultaneously and STILL have resources left over. (though the OS > scheduler will probably consume more CPU than the polling loops do, and the > process table and OS resources are likely to become exhausted long before > you hit any reasonable limit on the resource limits for the simultaneously > polling processes themselves.) > > > [Regarding the suggestion of synchronizing the readers with another > > concurrency object, I could do that. But if the database connection > > itself > > can coordinate the same thing, the reader program is a lot simpler and > > more > > portable.] > > > I'm not sure I understand Simon's comments how using EXCLUSIVE > TRANSACTION > >
Re: [sqlite] 3.17.0 bug report: FTS5 insertion puts a wrong value in last_insert_rowid
* Gwendal Roué: > I have found a regression in SQLite 3.17.0. In the following SQL statements: > > CREATE VIRTUAL TABLE t1 USING FTS5(content); > INSERT INTO t1(content) VALUES ('some text'); > SELECT last_insert_rowid(); // 10 (wrong) > SELECT rowid FROM t1; // 1 > > The expected value of the the SQL function last_insert_rowid() > function is 1, not 10. Same for the C function > sqlite3_last_insert_rowid(). I think this is a known issue. SQLite 3.18 adds a sqlite3_set_last_insert_rowid() function and uses it in “the new interface in the FTS3, FTS4, and FTS5 extensions to ensure that the sqlite3_last_insert_rowid() interface always returns reasonable values”. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 3.17.0 bug report: FTS5 insertion puts a wrong value in last_insert_rowid
Hello, I have found a regression in SQLite 3.17.0. In the following SQL statements: CREATE VIRTUAL TABLE t1 USING FTS5(content); INSERT INTO t1(content) VALUES ('some text'); SELECT last_insert_rowid(); // 10 (wrong) SELECT rowid FROM t1; // 1 The expected value of the the SQL function last_insert_rowid() function is 1, not 10. Same for the C function sqlite3_last_insert_rowid(). This bug was not present in 3.16.2. This bug is very similar to http://www.sqlite.org/src/tktview?name=13137dccf3, which affected FTS3. Cheers, Gwendal Roué ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How does one block a reader connection?
> On Mar 25, 2017, at 3:52 PM, peternwrote: > > So finally, here is the question. Is there a SQLite API way for reader > connections to block and wait for a meaningful change, like a new row, in > the 'cmd' table instead of madly polling and using up database concurrency > resources? [Block with timeout would be even more desirable of course.] No; I’ve asked about this myself. Alternatives to polling are either (a) using a platform-specific filesystem notification API to inform you when the database file(s) have been changed; or (b) using a platform-specific cross-process notification API to let the writer process signal that there are new changes. If you only want to know about specific types of changes, then option (b) sounds best. —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How does one block a reader connection?
Saturday, 25 March, 2017 23:44. peternwrote: > Can anybody explain the purpose of > http://sqlite.org/c3ref/busy_handler.html > ? It seems the only practical use would be to allow the caller to give > the engine a suggested lock deadline before SQLITE_BUSY is returned by > sqlite3_exec or sqlite3_step Then, if the calling thread would prefer to > wait longer, it can just retry with progressively smaller lock deadline > until it's time to do something else. The busy handler is used if you have set a timeout to obtain a lock. If you try to do BEGIN IMMEDIATE on a database that is not in WAL mode, then in order to obtain a lock there must be no active readers or writers. If there are, the lock cannot be obtained an SQLITE_BUSY is returned and you try again later. If you set a timeout with PRAMGA timeout, that specifies the amount of time to wait for a lock before returning SQLITE_BUSY. The busy handler is used to "handle" the "busy" state (that is, rather than return SQLITE_BUSY to the caller, your busy handler is called. You do something (like wait a while using sleep, then return, perhaps displaying flying ball bearings or other annoyances to indicate to the user that the program is waiting). Lather rinse repeat until the specified timeout expires at which time SQLITE_BUSY is returned to the caller. If you do not specify your own custom busy handler (to display flying ball bearings, etc, or do your own exponential sleeping, etc) then the default busy_handler is used. The default busy handler does its own exponential backoff algorithm but does not display whirling ball bearings or anything else, it just sleeps silently. > In my application it is more ideal if commands in the command table are > known to the readers immediately but without the overhead of rapid polling > by the readers. Setting a suggested wait time and then waiting again if > SQLITE_BUSY comes back early and there's nothing else to do would be just > fine. Again, you have specified "RAPID POLLING". Can you please define what you mean by "RAPID POLLING"? To me, "RAPID POLLING" means using the immensely stupid spinlock type implementation such as: open connection while (nothing to do) query database for something to do do what needs doing If you need things to go "RAPIDLY" then you need to use some IPC notification mechanism. Please state in explicit terms your definition of "RAPIDLY". Using terms without defining them makes it difficult for anyone to understand what you are talking about. One persons "RAPIDLY" may mean "after the MoC is filled out and endorsed, operations windows are arranged, and the board operator gives the final go ahead -- in other words, two weeks to six months". Another person's definition of "RAPIDLY" may be "within 250 milliseconds". Please specify what you definition of "RAPIDLY" is, giving minimum and maximum bounds in common time units. If RAPIDLY means "in less than one second, 33% of the time, within two seconds 75% of the time, and no more than three seconds ever" then you are talking about rather SLOW POLLING. open connection while (nothing to do) sleep 1 second query database for something to do do what needs doing This type of SLOW POLLING will use about 1% of the available CPU on a 4.77 Mhz 8086 CPU while in the polling loop. This means that you can have about 100 such processes polling at the same time on a 4.77 Mhz 8086 equipped computer. On a modern CPU with a couple of cores and about 2Ghz per code, you should be able to run many tens of thousands of such processes simultaneously and STILL have resources left over. (though the OS scheduler will probably consume more CPU than the polling loops do, and the process table and OS resources are likely to become exhausted long before you hit any reasonable limit on the resource limits for the simultaneously polling processes themselves.) > [Regarding the suggestion of synchronizing the readers with another > concurrency object, I could do that. But if the database connection > itself > can coordinate the same thing, the reader program is a lot simpler and > more > portable.] > I'm not sure I understand Simon's comments how using EXCLUSIVE TRANSACTION > writer isolation is subverting something subject to change if that > isolation level is desired. Reader would still be querying the command > table but in committed read mode with EXCLUSIVE isolation. Why would this > work differently on different systems? From > http://www.sqlite.org/lockingv3.html > "EXCLUSIVE An EXCLUSIVE lock is needed in order to write to the > database file. Only one EXCLUSIVE lock is allowed on the file and no other > locks of any kind are allowed to coexist with an EXCLUSIVE lock. In order > to maximize concurrency, SQLite works to minimize the amount of time that > EXCLUSIVE locks are held." > "If the same database is being read and written using two different >