Re: [sqlite] Sqlite3 Multi-process handling with continuous begin and commit in one thread cause error for other thread sqlite3_exec.

Hi, Thanks a lot for this suggestion. I have reduced the delay in the busy handler from 10ms to 1ms and added a delay of 2ms after "commit" and before "begin". With this it is working fine now. After reading you input we got to know the root cause is because of the CPU context switch betwee

Re: [sqlite] How to handle such situation

Perhaps I need to be more explicit: If the transaction fails because of a constraint violation, then using either the ROLLBACK, IGNORE, or REPLACE conflict resolution strategies would commit or roll back the transaction and automatically close it. If you add conflict clauses to table schemata,

Re: [sqlite] Sqlite3 Multi-process handling with continuous begin and commit in one thread cause error for other thread sqlite3_exec.

On 5 Oct 2017, at 3:17am, Hegde, Deepakakumar (D.) wrote: > 2) Both the link are added the busy handler and busy handler function is > retries for 1 times with 10ms second of delay. > sqlite3_busy_handler(psRaceSqlite->sSqliteInfo.pSqlHandle, > dbm_sqlite_busy_handler,psRaceSql

Re: [sqlite] Sqlite3 Multi-process handling with continuous begin and commit in one thread cause error for other thread sqlite3_exec.

On 10/4/2017 10:17 PM, Hegde, Deepakakumar (D.) wrote: There is a problem we are facing with the multiple DB process write operation. Following the procedure: 1) Open the Database in the two process as: sqlite3_open_v2 (pcDbName, &psRaceSqlite->sSqliteInfo.pSqlHandle, (SQLITE_OPEN_R

Re: [sqlite] How to handle such situation

Hi, On Wed, Oct 4, 2017 at 6:19 PM, J. King wrote: > See also: > This is good to know but I don't think it is applicable in this case as I explicitly said that this is inside transaction which fails. Thank you. > > On October 4, 2017 6:15:55 PM EDT, Jens

[sqlite] Sqlite3 Multi-process handling with continuous begin and commit in one thread cause error for other thread sqlite3_exec.

Hi All, There is a problem we are facing with the multiple DB process write operation. Following the procedure: 1) Open the Database in the two process as: sqlite3_open_v2 (pcDbName, &psRaceSqlite->sSqliteInfo.pSqlHandle, (SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FUL

Re: [sqlite] How to handle such situation

Hi, Jens, On Wed, Oct 4, 2017 at 6:15 PM, Jens Alfke wrote: > > >> On Oct 4, 2017, at 2:20 PM, Igor Korot wrote: >> >> If I start transaction, all queries were successful, but issuing "COMMIT" >> fails. >> On such failure I am going to present an error, but then what happens >> if I go and try

Re: [sqlite] eponymous vtables, xBestIndex, and required parameters...

On 10/4/17, dave wrote: > 1) is there an orthodox method of indicating that a query plan request from > xBestIndex is a no-go, Give that plan a huge estimatedCost. As a backup, in the exceedingly unlikely event that SQLite chooses your no-go plan in spite of the huge estimatedCost, also provide

[sqlite] eponymous vtables, xBestIndex, and required parameters...

Folks; I am building a system which uses the virtual tables facilityto join some non-relational data (coming from system APIs) to relational data. I am using the virtual table mechanism in sqlite, and in particular am creating 'eponymous' virtual tables to create 'table valued functions'. I ha

Re: [sqlite] PRAGMA optimize; == no busy handler?

On 5 Oct 2017, at 12:07am, Toby Dickenson wrote: > How does PRAGMA optimize or ANALYSE affect concurrency? Is there any > risk that it might leave the database locked for the full duration of > the scan? ANALYZE does this every time. It’s not possible to analyze a database while it’s being c

Re: [sqlite] PRAGMA optimize; == no busy handler?

How does PRAGMA optimize or ANALYSE affect concurrency? Is there any risk that it might leave the database locked for the full duration of the scan? On 4 October 2017 at 23:29, Jens Alfke wrote: > > >> On Oct 4, 2017, at 10:30 AM, Richard Hipp wrote: >> >> The PRAGMA optimize command is our effo

Re: [sqlite] PRAGMA optimize; == no busy handler?

> On Oct 4, 2017, at 10:30 AM, Richard Hipp wrote: > > The PRAGMA optimize command is our effort to move further in the > direction of a "smart" SQLite that always automatically "does the > right thing" with respect to gathering and using database statistics. That’s a great move. Along the sam

Re: [sqlite] How to handle such situation

See also: On October 4, 2017 6:15:55 PM EDT, Jens Alfke wrote: > > >> On Oct 4, 2017, at 2:20 PM, Igor Korot wrote: >> >> If I start transaction, all queries were successful, but issuing >"COMMIT" fails. >> On such failure I am going to present an error, b

Re: [sqlite] How to handle such situation

> On Oct 4, 2017, at 2:20 PM, Igor Korot wrote: > > If I start transaction, all queries were successful, but issuing "COMMIT" > fails. > On such failure I am going to present an error, but then what happens > if I go and try to close the connection? If COMMIT fails, you should execute a ROLLB

Re: [sqlite] PRAGMA optimize; == no busy handler?

The databases I make are pretty darned small and insignificant that even full table scans wouldn't show that much of a hit in performance. However, if there were an API or SQL code or something we can add to our code that would give you meaningful results (other than "Yes, it works") we could emai

[sqlite] How to handle such situation

Hi, list, Have a following question. I am writing an application in which I will be using transactions. At the end of the application I will close the connection. The application will verify every single call to SQLite for an error. If I start transaction, all queries were successful, but issuing

Re: [sqlite] Ordering a GROUP BY, or other concatenating tricks?

You could also write it even more clearly as: WITH IndexedLines AS (SELECT LineText FROM DocLines WHERE DocID = 10 ORDER BY LineIndex) SELECT group_concat(LineText, char(10)) FROM IndexedLines; That code will actually work. As it is not C, SQLite will not recognize the '\n' C escaped line feed i

Re: [sqlite] Ordering a GROUP BY, or other concatenating tricks?

On 10/4/17, sub sk79 wrote: > On Wed, Oct 4, 2017 at 12:29 PM, Richard Hipp wrote: > >> >> This restriction on the query flattener causes your example >> query above to do what you want. >> > > If subquery-flattening needs to be disabled explicitly, is using "LIMIT -1 > OFFSET 0 " the recommended

Re: [sqlite] Ordering a GROUP BY, or other concatenating tricks?

On Wed, Oct 4, 2017 at 12:29 PM, Richard Hipp wrote: > > This restriction on the query flattener causes your example > query above to do what you want. > If subquery-flattening needs to be disabled explicitly, is using "LIMIT -1 OFFSET 0 " the recommended way? > SQLite version 3.21.0 adds new

[sqlite] Segfault on query related to json_each in where clause

Oh interesting, I am seeing it with 3.19.3 contrary to the bug report… Mind you this is High Sierra so Apple may have messed with the build… /usr/bin/sqlite3 --version 3.19.3 2017-06-27 16:48:08 2b0954060fe10d6de6d479287dd88890f1bef6cc1beca11bc6cdb79f72e2377b > Thanks for the bug report. This pr

Re: [sqlite] Error in docs for WHERE clause in CREATE INDEX

Fixed in the draft documentation. On 10/4/17, Jens Alfke wrote: > From https://www.sqlite.org/partialindex.html > : > >> The WHERE clause may not contain subqueries, references to other tables, >> non-deterministic functions, or bound parameters. The LIK

[sqlite] Unlikely data security problem in sqlite_stat tables

The problem occurs only with non-default compilation settings, and depends on an incorrect assumption by the programmer, so it is unlikely that it would cause a problem under normal circumstances. It is related to the following commands: DROP TABLE TableName — removes related rows from sqlite_

[sqlite] Error in docs for WHERE clause in CREATE INDEX

From https://www.sqlite.org/partialindex.html : > The WHERE clause may not contain subqueries, references to other tables, > non-deterministic functions, or bound parameters. The LIKE, GLOB, MATCH, and > REGEXP operators in SQLite are implemented as fu

Re: [sqlite] Shell tool allows creation of column name ""

But without the quotes you couldn't have the empty string as a field name :) It also allows for spaces etc. and avoids needing logic to find out whether it really needs the quotes or not. And remember that the quotes aren't part of the field name, they're just there in the SQL text. In general t

Re: [sqlite] PRAGMA optimize; == no busy handler?

That ability to manually pre-populate the sqlite_stat1 table has been around for ages. But not many developers use it. Probably because it is tedious to do and developers have more important problems to work on. A few weeks ago, we got a report of a performance problem from a support customer.

Re: [sqlite] Shell tool allows creation of column name ""

I believe that as long as quoted identifiers are allowed, every value allowed as a regular character string should also be allowed as an identifier, including the empty string. (Length limits notwithstanding.) Some best practices may be against empty string names, but the system should allow

Re: [sqlite] PRAGMA optimize; == no busy handler?

http://www.sqlite.org/optoverview.html section 6.2 "Manual Control Of Query Plans Using SQLITE_STAT Tables" SQLite provides the ability for advanced programmers to exercise control over the query plan chosen by the optimizer. One method for doing this is to fudge the ANALYZE results in the sqli

[sqlite] Shell tool allows creation of column name ""

Given a .csv file which starts like this: ID,code,name,sortOrder,,AlternativeName 1,RAH,Robert A. Heinlein,"Heinlein, Robert A.",Real Name, 2,IA,Isaac Asimov,"Asimov, Isaac",Real Name, 3,HH,Harry Harrison,"Harrison, Harry",, Shell tool of this version SQLite version 3.19.3 2017-06-27 16:48:08 C

Re: [sqlite] Ordering a GROUP BY, or other concatenating tricks?

On 10/4/17, Doug Nebeker wrote: > Is it just a matter of using sqlite3_create_function to register a function > that guarantees it will concatenate in the order rows are received? Would > that guarantee that your example works, or is order no longer guaranteed > once they leave the inner select?

Re: [sqlite] PRAGMA optimize; == no busy handler?

On 4 Oct 2017, at 5:06pm, Jens Alfke wrote: > That’s an interesting idea. I’ve been assuming that ANALYZE was dependent on > the exact contents of the database, but if its results can be canned and > applied to new databases, that’s a good optimization. It works and I’ve seen it suggested el

Re: [sqlite] Ordering a GROUP BY, or other concatenating tricks?

On 4 Oct 2017, at 4:06pm, Doug Nebeker wrote: > Is it just a matter of using sqlite3_create_function to register a function > that guarantees it will concatenate in the order rows are received? Would > that guarantee that your example works, or is order no longer guaranteed once > they leav

Re: [sqlite] PRAGMA optimize; == no busy handler?

Simon Slavin wrote: > > It is not expected that you’ll try to run ANALYZE while a database is in use. > It’s intended for offline-maintenance. “Offline maintenance” is for servers 😝 I suspect it’s not relevant to the majority* of use cases for SQLite, like user-facing applications and embedde

Re: [sqlite] Ordering a GROUP BY, or other concatenating tricks?

Is it just a matter of using sqlite3_create_function to register a function that guarantees it will concatenate in the order rows are received? Would that guarantee that your example works, or is order no longer guaranteed once they leave the inner select? SELECT group_concat(LineText, '\n') F

Re: [sqlite] Segfault on query related to json_each in where clause

Thanks for the bug report. This problem was previously described by ticket https://sqlite.org/src/info/b899b6 and was fixed by check-in https://sqlite.org/src/info/c7f9f47b23 on 2017-09-04. The re-release snapshot on the https://sqlite.org/download.html download page contains this fix (among othe

[sqlite] Segfault on query related to json_each in where clause

Crashes on 3.19, 3.20, but not on 3.15: $ sqlite3 SQLite version 3.20.0 2017-08-01 13:24:15 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table t(json JSON); sqlite> select * from t WHERE(EXISTS(S

Re: [sqlite] Ordering a GROUP BY, or other concatenating tricks?

On 04/10/2017 02:16, Simon Slavin wrote: The differences between SQLite and (a.o.) MySQL versions of "group_concat" are a recurrent topic. Since I often need to specify "distinct", "order by", "order direction" and "separator", I have written a simple UDF class that simulates the MySQL full ve

Re: [sqlite] PRAGMA optimize; == no busy handler?

>From my personal experience, performance instability of SQLite queries >(drastically jumping from milliseconds to seconds and back) I haven't seen that sort of instability. We found some queries were consistently poor until we ran ANALYZE, due to nature of the data, indexes and queries. Alterin

Re: [sqlite] PRAGMA optimize; == no busy handler?

>On your development system This is impractical for our case >It’s intended for offline-maintenance. The documentation doesn't say that. In fact the only related comment is https://sqlite.org/pragma.html#pragma_optimize ...This pragma is usually a no-op or nearly so and is very fast. However if