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

[Hegde, Deepakakumar (D.)]

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 between the process. whenever busy handle start executing 
another process would have started the "begin" causing the DB lock.


Thanks again.


Thanks and Regards

Deepak


From: sqlite-users  on behalf of 
Simon Slavin 
Sent: Thursday, October 5, 2017 8:00:31 AM
To: SQLite mailing list
Subject: 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,psRaceSqlite);

Instead of this, just set an timeout, which in your case would be 100ms.  
This may look big but is an appropriate value.



SQLite’s own timeout routine knows more about how SQLite works than yours do.  
It implements exponential backoff and other clever things.  Do not try to use 
both at once: strip out your use of sqlite3_busy_handler() .

Remember that you need to set timeout for every connection separately, in other 
words not only for the connections doing IHSERT but for the other processes 
which are reading or doing individual INSERT commands.

> 3) In one thread there is a continuous record insertion with begin and commit 
> operation. This begin and commit will be done with for every 200 record 
> insertion. time for a single begin to the commit is about 1 second.

Use BEGIN EXCLUSIVE.

After each COMMIT, pause for at least 20ms.  If that doesn’t improve things you 
may need more — anything up to 1000ms could help.  You could set that number 
for testing then reduce it until it stops working.

Thank you for your detailed description and timings which help us make good 
suggestions.

Simon.
___
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] How to handle such situation

[J. King]

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, it wouldn't even require changes to the logic of 
your application. 

On October 4, 2017 10:20:01 PM EDT, Igor Korot  wrote:
>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 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 to close the connection?
>>>
>>>If COMMIT fails, you should execute a ROLLBACK to abort the
>>>transaction, then continue with failure handling. Otherwise yes, the
>>>transaction remains open and will cause trouble when you try to close
>>>the connection.
>>>
>>>—Jens
>>>
>>>___
>>>sqlite-users mailing list
>>>sqlite-users@mailinglists.sqlite.org
>>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>> --
>> Sent from my Android device with K-9 Mail. Please excuse my brevity.
>> ___
>> 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

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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,psRaceSqlite);

Instead of this, just set an timeout, which in your case would be 100ms.  
This may look big but is an appropriate value.



SQLite’s own timeout routine knows more about how SQLite works than yours do.  
It implements exponential backoff and other clever things.  Do not try to use 
both at once: strip out your use of sqlite3_busy_handler() .

Remember that you need to set timeout for every connection separately, in other 
words not only for the connections doing IHSERT but for the other processes 
which are reading or doing individual INSERT commands.

> 3) In one thread there is a continuous record insertion with begin and commit 
> operation. This begin and commit will be done with for every 200 record 
> insertion. time for a single begin to the commit is about 1 second.

Use BEGIN EXCLUSIVE.

After each COMMIT, pause for at least 20ms.  If that doesn’t improve things you 
may need more — anything up to 1000ms could help.  You could set that number 
for testing then reduce it until it stops working.

Thank you for your detailed description and timings which help us make good 
suggestions.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Igor Tandetnik]

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_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX),  NULL);

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,psRaceSqlite);


3) In one thread there is a continuous record insertion with begin and commit 
operation. This begin and commit will be done with for every 200 record 
insertion. time for a single begin to the commit is about 1 second.


Use BEGIN IMMEDIATE or BEGIN EXCLUSIVE here. For rationale, see

http://sqlite.org/c3ref/busy_handler.html

the paragraph that mentions "deadlock". Also 
http://sqlite.org/lang_transaction.html and http://sqlite.org/lockingv3.html

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to handle such situation

[Igor Korot]

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 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 to close the connection?
>>
>>If COMMIT fails, you should execute a ROLLBACK to abort the
>>transaction, then continue with failure handling. Otherwise yes, the
>>transaction remains open and will cause trouble when you try to close
>>the connection.
>>
>>—Jens
>>
>>___
>>sqlite-users mailing list
>>sqlite-users@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
> ___
> 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

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

[Hegde, Deepakakumar (D.)]

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_FULLMUTEX),  NULL);

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,psRaceSqlite);


3) In one thread there is a continuous record insertion with begin and commit 
operation. This begin and commit will be done with for every 200 record 
insertion. time for a single begin to the commit is about 1 second.

4) If we try to write INSERT or UPDATE in the other thread using sqlite3_exec 
() then it is giving the error.

Following is the failure cases:
1) Busy handler is invoked, but it will not get success for 1 retry and 
then give error.
2) Some time direct error is given without invoking the the busy handler.

Please can anyone help us to understand the use of the multi-thread handling of 
the sqlite?

Thanks and Regards
Deepak

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to handle such situation

[Igor Korot]

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 to close the connection?
>
> If COMMIT fails, you should execute a ROLLBACK to abort the transaction, then 
> continue with failure handling. Otherwise yes, the transaction remains open 
> and will cause trouble when you try to close the connection.

And if the ROLLBACK fails?
Because it is possible for ROLLBACK to fail. Not just because COMMIT fails.

Thank you.

>
> —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

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

[Richard Hipp]

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 a
unique idxNum and if xFilter sees that idxNum, have xFilter throw an
error with error message text that is something like "query planner
could not find an acceptable solution".


> 2)  am I using the 'pIdxInfo->aConstraintUsage[nIdx].omit' variable
> appropriately?  My interpretation is that means 'the vtable can fully handle
> the constraint, sqlite does not need to do a double-check on it afterwards'.

Yes.  Correct.


> And I guess as a bonus 4th question:  What is the established orthodoxy in
> picking estimatedCost anyway?

It is not overly sensitive to the scale of your cost estimates.   For
best results, let 1.0 be the same time as required to do a single
b-tree lookup on a one-page b-tree.  In other words, the cost of
reading a single page of the database file from disk into cache and
then doing a little decoding work to perform a binary search among the
approximately 200 keys on that page.

You don't know how to estimate that?  Then guess.  As long as the
relative costs for other invocations of xBestIndex on the same virtual
table are in reasonable proportion, everything should work fine.

In the source tree, go to the ext/misc folder and grep for
"estimatedCost" to see how some of the extension virtual tables do it.

-- 
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] eponymous vtables, xBestIndex, and required parameters...

[dave]

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 have a problem that puzzles me regarding the appropriate implementation of
the xBestIndex method, in particular in dealing with constraints that are
required for the table.  In my case, it is required that there be a "where
PARAMCOL = 'xxx'" condition somewhere in the query, or the table valued
function makes no sense.  The problem arises where the sqlite engine asks
for advice during the query planning phase, and sometimes sqlite chooses to
take a plan where NO constraints are applied.  A concrete example may help
clarify:
 
Given:
  1)  vtable 'fsdirinfo' projecting columns PATH, FNAME
  'PATH' is a directory, and is required, because this virtual table
does a directory listing of files in a particular path, and you can't get a
directory listing without supplying the path.
  2)  vtable 'verinfo' projecting columns FQPATH, FILEVER, COMPANYNAME,
PRODUCT, COMMENTS
  'FQPATH' is required, because you can't open a file (to read it's
version information) without having a [fully qualified] path to that file.
 
Many times, for simple queries like "select * from fsdirinfo where path =
'c:\windows\system32';"
This works as expected:  xBestIndex is called, it is noticed that there is
an equality constraint on path (as required), and that column is marked
thusly:
 
  pIdxInfo->aConstraintUsage[nIdx].argvIndex = nArgvIndex;
  pIdxInfo->aConstraintUsage[nIdx].omit = 1; //meaning 'sqlite does not need
to help filter more'

(please verify my interpretation of these fields).
 
I also went as far as adding some validation logic where if the required
constraints are not met, I emit an error:
 
  //see if we have met our requirements; it is required that we have an
equality
  //operation on column 1 (PATH), otherwise we can cope
  if ( SQLITE_INDEX_CONSTRAINT_EQ != afsde[1]._nOp )
  {
   /*XXX apparently, this can happen regardless of user SQL
   //must have required columns in constraint list. must must must.
   sqlite3_free( pThis->zErrMsg );
   pThis->zErrMsg = sqlite3_mprintf( VTBLNAME": There must be a constraint
on PATH" );
   return SQLITE_CONSTRAINT; //XXX something better?
  */
  }

but as you can see I commented that out, because sometimes sqlite asks 'what
if I just gave you no constraints, what would you think about that', and
then the error I emit causes the whole query to fail, even though I have
already indicated a query plan that would work.  (I.e., sqlite asks once
"how about constraining on 'path'" to which I reply "yes please", and then
it asked "well, what about constraining on nothing", to which I reply
"error".  Sqlite does not back off and use the other plan that was
'approved'.)
 
As a work-around I masked the invalid unconstrained condition in my xFilter
by simply returning no rows.  However, that approach has problems, too.
Consider this query, which consists of a join between the two vtables I
mentioned:
 
  select * from fsdirinfo fs, verinfo ( fs.PATH || '\' || fs.FNAME ) vi 
  where 
   fs.path = 'C:\Windows\System32' 
   and ( fs.fname glob '*.exe' or fs.fname glob '*.dll' );
 
As it is presently, this returns No rows!  Huh!  Well, I stepped through
the code of course before writing in this question, and here's what happens:
1)  fsdirinfo is requested to query on path via equality and fname via glob,
and I can use both in this case
2)  verinfo is requested to query on fqpath via equality, and I can take
that
3)  verinfo gets requested a second time to query on nothing at all.  I have
no choice to say 'uhm, OK' and setup to return no rows, since my
communicating that is a error will abort the query
 
And sqlite then selects the item 3 approach.  Upon sqlite3_step(), fsdirinfo
is run through all the rows as expected, and verinfo is run through, but of
course gets no FQPATH value, and therefor cannot return any rows.  Net
result:  the join results in no rows.  (Incidentally, I have tried this
query functionally in three ways:  1) hidden columns for params (depicted
above), 2) constraints on projected columns (i.e. ye olde
cartesian-product-with-where-clause), 3) join syntax.  All three conventions
work fine, and in the same way.  Alas, that way yields no results, haha.)
 
OK, so with some context, now I can ask some questions (sorry for so much
exposition above).  Questions:
1)  is there an orthodox method of indicating that a query plan request from
xBestIndex is a no-go, for wahtever reason (in my case there must be
constraints on some columns), but does not abort the entire query, and
simply causes one of the other accepted plans to be used.  (I think this is
the single best option if it does exist).
2)  am I using the 'pIdxInfo->aConstraintUsage[nIdx].omit' variable
appropriately?  My inte

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

[Simon Slavin]

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 changed.

I’d expect "PRAGMA optimize" to do the same but I’m not sure.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Toby Dickenson]

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 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 same lines, it would be nice if SQLite could 
> vacuum once in a while without being asked (like a good housemate ;) What I’m 
> doing now is based on some advice I read in a blog post*:
>
> // If this fraction of the database is composed of free pages, vacuum 
> it
> static const float kVacuumFractionThreshold = 0.25;
> // If the database has many bytes of free space, vacuum it
> static const int64_t kVacuumSizeThreshold = 50 * MB;
>
> // After creating a new database:
> exec("PRAGMA auto_vacuum=incremental”);
>
> // Just before closing a database:
> exec("PRAGMA optimize");
> int64_t pageCount = intQuery("PRAGMA page_count");
> int64_t freePages = intQuery("PRAGMA freelist_count");
> if ((pageCount > 0 && (float)freePages / pageCount >= 
> kVacuumFractionThreshold)
> || (freePages * kPageSize >= kVacuumSizeThreshold)) {
> exec("PRAGMA incremental_vacuum");
> }
>
> (To forestall any retorts that “you don’t need to vacuum because SQLite will 
> reuse the free space later”: Yes, you do need to, on a space-constrained 
> device like a phone. Otherwise your app never reclaims any storage back to 
> the OS for use by other apps, and you get customer issues like “I deleted all 
> my old junk from the app but it’s still using 10GB of storage, please help my 
> phone is out of space”…)
>
> —Jens
>
> * https://blogs.gnome.org/jnelson/2015/01/06/sqlite-vacuum-and-auto_vacuum/
> ___
> 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] PRAGMA optimize; == no busy handler?

[Jens Alfke]

> 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 same lines, it would be nice if SQLite could 
vacuum once in a while without being asked (like a good housemate ;) What I’m 
doing now is based on some advice I read in a blog post*:

// If this fraction of the database is composed of free pages, vacuum it
static const float kVacuumFractionThreshold = 0.25;
// If the database has many bytes of free space, vacuum it
static const int64_t kVacuumSizeThreshold = 50 * MB;

// After creating a new database:
exec("PRAGMA auto_vacuum=incremental”);

// Just before closing a database:
exec("PRAGMA optimize");
int64_t pageCount = intQuery("PRAGMA page_count");
int64_t freePages = intQuery("PRAGMA freelist_count");
if ((pageCount > 0 && (float)freePages / pageCount >= 
kVacuumFractionThreshold)
|| (freePages * kPageSize >= kVacuumSizeThreshold)) {
exec("PRAGMA incremental_vacuum");
}

(To forestall any retorts that “you don’t need to vacuum because SQLite will 
reuse the free space later”: Yes, you do need to, on a space-constrained device 
like a phone. Otherwise your app never reclaims any storage back to the OS for 
use by other apps, and you get customer issues like “I deleted all my old junk 
from the app but it’s still using 10GB of storage, please help my phone is out 
of space”…)

—Jens

* https://blogs.gnome.org/jnelson/2015/01/06/sqlite-vacuum-and-auto_vacuum/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to handle such situation

[J. King]

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, but then what happens
>> if I go and try to close the connection?
>
>If COMMIT fails, you should execute a ROLLBACK to abort the
>transaction, then continue with failure handling. Otherwise yes, the
>transaction remains open and will cause trouble when you try to close
>the connection.
>
>—Jens
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to handle such situation

[Jens Alfke]

> 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 ROLLBACK to abort the transaction, then 
continue with failure handling. Otherwise yes, the transaction remains open and 
will cause trouble when you try to close the connection.

—Jens

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Stephen Chrzanowski]

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
email in, I'd be glad to add it in for debug executions.

On Wed, Oct 4, 2017 at 1:30 PM, Richard Hipp  wrote:

>
> But, I'd like to get more real-world feedback on the use of "PRAGMA
> optimize" and how well it works (or doesn't work) for a wider spectrum
> of developers before I start pushing it as a recommended solution.
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to handle such situation

[Igor Korot]

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 "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?

I think that the call to sqlite3_close() will fail since there is an
open transaction, right? But then there is no error at all.
Unless I misunderstands the nature of the connection close failure.

So, how do I differentiate such situation from a legitimate
sqlite3_close() failure?

TIA!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[petern]

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 in Simon's example. Special characters, like
char(10), must be generated by call out to an extension or values from a
table.

As was explained many times, despite malicious insistence on the
preservation of obtuse comments in the group_concat() docs, the output of
group_concat() IS directly controlled by the order of the supplied rowset
and one is free to specify that order directly.

Group_concat() itself is not idiosyncratically jumbling the output order.
If the optimizer's discretionary reordering is to be communicated in the
documents of built in functions then, for completeness, every aggregate
function should have the same absurd disclaimer of inexorable randomness.

Consider the aggregate round off error of the built in avg() function.  By
the same illogic isn't the roundoff error of the avg() output both
intractable and implacable because the optimizer might change the order of
the rows of an unordered query?  Yet, it is completely true that one is
free to specify a round off error minimizing order if one chooses to do so.

OK.  So everybody in favor of flawed documentation, raise their hand.  The
avg() function should also have the same obtuse remark suggesting, with a
wink and nod, that that using avg() comes with an inexorable and implacable
input ordering that one cannot control.





On Wed, Oct 4, 2017 at 2:12 AM, Jean-Luc Hainaut  wrote:

> 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 version.
> It is written in Python 2.7 through the standard SQLite3 interface but it
> should be easy to translate it in C:
>
> https://www.dropbox.com/s/ilpx8duppbus8u3/group_concat2.py?dl=0
>
> Hoping it will help!
>
> Jean-Luc Hainaut
>
> On 3 Oct 2017, at 11:13pm, Doug Nebeker  wrote:
>>
>> How can I select a document and get the complete sorted text back in a
>>> single row (so I can do a JOIN on a different table with additional
>>> information)?
>>>
>> There is a way which will probably work but the documentation adds a
>> careful note that it will not always work.
>>
>> 
>>
>> So you would want something like
>>
>> SELECT group_concat(LineText, '\n') FROM
>> (SELECT LineText FROM DocLines
>> WHERE DocID = 10
>> ORDER BY LineIndex)
>>
>> The problem is that the order of concatenation is arbitrary, in other
>> words you shouldn’t rely on this working.
>>
>> If you don’t want to use group_concat(), do it in your programming
>> language.  Use
>>
>> SELECT LineText FROM DocLines
>> WHERE DocID = 10
>> ORDER BY LineIndex
>>
>> and concatenate the retrieved values in your programming language.
>>
>> Simon.
>> ___
>> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

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 way?
>

Just LIMIT -1 is sufficient.

>
>
>> SQLite version 3.21.0 adds new restrictions on the query flattener
>> which allows the application to control whether expensive functions
>> (or subqueries) are run before or after sorting.
>>
>
> Is 'expensive' going to be a flag in fourth parameter to create_function
> like SQLITE_DETERMINISTIC is?
>

No.  "expensive_function()" is just a place-holder.  It might be a
large expression, involving one or more subquery expressions, or it
might be something that uses a lot of memory rather than a lot of CPU
cycles.  That is part of the problem of why the query planner has such
a hard time determining whether or not to make this optimization
automatically - it is difficult to pin down what the application is
likely to think is "expensive".
-- 
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] Ordering a GROUP BY, or other concatenating tricks?

[sub sk79]

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 restrictions on the query flattener
> which allows the application to control whether expensive functions
> (or subqueries) are run before or after sorting.
>

Is 'expensive' going to be a flag in fourth parameter to create_function
like SQLITE_DETERMINISTIC is?

Thanks,
SK
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Wout Mertens]

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 problem was previously described by ticket 
> https://sqlite.org/src/info/b899b6 and was fixed by check-in 
> https://sqlite.org/src/info/c7f9f47b23
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

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 LIKE, GLOB, MATCH,
>> and REGEXP operators in SQLite are implemented as functions by the same
>> name. Since functions are prohibited in the WHERE clause of a CREATE INDEX
>> statement, so too are the LIKE, GLOB, MATCH, and REGEXP operators.
>
> I believe the last sentence is false. The first sentence includes the
> qualifier “non-deterministic”, which was probably inserted when
> deterministic functions were introduced. But that invalidates the last
> sentence, since all the functions listed are deterministic.
>
> (If the last sentence is deleted, then of course the second sentence becomes
> pointless and should be deleted too.)
>
> —Jens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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] Unlikely data security problem in sqlite_stat tables

[Simon Slavin]

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_stat tables
DELETE FROM TableName — does not remove related rows from sqlite_stat tables
VACUUM — does not modify sqlite_stat tables

Given compilation setting SQLITE_ENABLE_STAT4 (which is not the default), 
there’s a possible data security problem relating to sqlite_stat3 and 
sqlite_stat4 tables.  These tables include columns named "sample" which contain 
copies of data from tables.

If a TABLE is DROPped from the database, then rows related to it are removed 
from the sqlite_stat tables.  This means there is no security problem.  (I’ve 
just realised I didn’t check that the same was done when DROPping individual 
INDEXes.)

However there is a convenient command, which sqlite has optimizations for, 
which has a similar effect.  This is the use of the DELETE FROM command with no 
WHERE clause.  This removes all data from the table, but it leaves any "sample" 
values in sqlite_stat tables.  A programmer who did the following

DELETE FROM HomePhoneNumbers;
VACUUM;

might reasonably think that this would remove all copies of the data from the 
database file.  Especially if they did the VACUUM with this specific objective.

If this is considered a security matter then a modification to the optimized 
DELETE without WHERE clause command, or to VACUUM, could remove the problem.  
I’m sure the dev team can pick an appropriate way to do it.  Or perhaps the 
documentation should remind people that DELETE without WHERE is just another 
DELETE command, and does not necessarily remove all copies of data from the 
database file.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jens Alfke]

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 functions by the same name. 
> Since functions are prohibited in the WHERE clause of a CREATE INDEX 
> statement, so too are the LIKE, GLOB, MATCH, and REGEXP operators.

I believe the last sentence is false. The first sentence includes the qualifier 
“non-deterministic”, which was probably inserted when deterministic functions 
were introduced. But that invalidates the last sentence, since all the 
functions listed are deterministic.

(If the last sentence is deleted, then of course the second sentence becomes 
pointless and should be deleted too.)

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[David Raymond]

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 though if it's "not 
a good idea to create a column with that name" then the user shouldn't have 
named the column that in their .csv file. The shell will at least give an error 
for duplicate column names if more than 1 column is named the empty string.

sqlite> pragma table_info(Authors);
cid,name,type,notnull,dflt_value,pk
0,ID,TEXT,0,,0
1,code,TEXT,0,,0
2,name,TEXT,0,,0
3,sortOrder,TEXT,0,,0
4,,TEXT,0,,0
5,AlternativeName,TEXT,0,,0

sqlite> select ID, code, name, sortOrder, AlternativeName from author;
ID  codenamesortOrderAlternativeName
--  --  --  ---  ---
1   RAH Robert A. Heinlein  Heinlein, Robert A.
2   IA  Isaac AsimovAsimov, Isaac
3   HH  Harry Harrison  Harrison, Harry

sqlite> select ID, "" from author;
ID
--  --
1   Real Name
2   Real Name
3

Header line changed to: ID,code,name,,,AlternativeName
sqlite> .import authors.txt Authors2
CREATE TABLE Authors2(...) failed: duplicate column name:

sqlite>


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Wednesday, October 04, 2017 12:51 PM
To: SQLite mailing list
Subject: [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

Creates a table with the following columns:

CREATE TABLE Authors(
  "ID" TEXT,
  "code" TEXT,
  "name" TEXT,
  "sortOrder" TEXT,
  "" TEXT,
  "AlternativeName" TEXT
);

I don’t know the externally-governed rules.  I don’t know what rules the 
development team want to follow.  But I’m questioning whether it’s a good idea 
to create a column with that name.  If the dev team think it’s okay, that’s 
fine with me.

I would actually prefer it didn’t include the quotes signs in the column names. 
 They’re not in the .csv file.  But that’s a different matter.

Simon.
___
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] PRAGMA optimize; == no busy handler?

[Richard Hipp]

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.  Looking at their database, we found that they had
created all of the tables, and most but not all of the indexes, added
exactly 1 row to each table, then run ANALYZE.  After that they filled
the database with content and started trying to use it.
Unfortunately, the degenerate sqlite_stat1 content created from the
one-row-per-table sample data seriously confused the query planner.
Rerunning ANALYZE fixed the problem.  They would have been better off
to have never run ANALYZE at all, I think.

That episode demonstrated an important point:  It is hard to know when
to run ANALYZE.  Furthermore, application developers ought not need to
become SQLite experts in order to get SQLite to function well in their
application.  SQLite should just figure out the right thing to do for
itself.  It should "just work".

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.
Rather than try to teach application developers a bunch of arcane
rules (which are really only heuristics) for when and how to run
ANALYZE, we just ask them to run "PRAGMA optimize" right before
closing the database connection.  The application developer has much
less to worry about.  They don't need to understand ANALYZE, how it
works or why it is important.  The "PRAGMA optimize" command will take
care of running ANALYZE for them, and do so in a way that is minimally
invasive (PRAGMA optimize only rarely runs ANALYZE in practice, and
when it does, it will only ANALYZE individual tables, not the entire
database).

We have been dog-fooding this technique as we are able.  The Fossil
system has been modified
(https://www.fossil-scm.org/fossil/artifact/616c0d?ln=1706) to run
"PRAGMA optimize" after every command and/or web-page visit, and that
has worked out quite well.

But, I'd like to get more real-world feedback on the use of "PRAGMA
optimize" and how well it works (or doesn't work) for a wider spectrum
of developers before I start pushing it as a recommended solution.

On 10/4/17, David Raymond  wrote:
> 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 sqlite_stat1, sqlite_stat3, and/or
> sqlite_stat4 tables. That approach is not recommended except for the one
> scenario described in the next paragraph.
>
> For a program that uses an SQLite database as its application file-format,
> when a new database instance is first created the ANALYZE command is
> ineffective because the database contain no data from which to gather
> statistics. In that case, one could construct a large prototype database
> containing typical data during development and run the ANALYZE command on
> this prototype database to gather statistics, then save the prototype
> statistics as part of the application. After deployment, when the
> application goes to create a new database file, it can run the ANALYZE
> command in order to create the statistics tables, then copy the precomputed
> statistics obtained from the prototype database into these new statistics
> tables. In that way, statistics from large working data sets can be
> preloaded into newly created application files.
>
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Jens Alfke
> Sent: Wednesday, October 04, 2017 12:06 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] PRAGMA optimize; == no busy handler?
>
>
>> So why not work around the whole process ?  On your development system,
>> create a database with plausible data in.  Run ANALYZE.  Then copy all the
>> tables named "sqlite_stat*" into a new database.
>
> 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.
>
> I’m just a bit wary because this isn’t documented anywhere, and generally
> one is warned against writing to `sqlite_*` tables. I’d rather have some
> official blessing of this technique before I consider using it.
>
> —Jens
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists

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

[Darren Duncan]

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 it.  Also serves as a good "default" name, like the good default character 
string value would be empty or the good default number value would be zero.


I also believe that database tables with zero columns should be allowed, but 
that's a separate matter.


-- Darren Duncan

On 2017-10-04 9:51 AM, Simon Slavin wrote:

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

Creates a table with the following columns:

CREATE TABLE Authors(
  "ID" TEXT,
  "code" TEXT,
  "name" TEXT,
  "sortOrder" TEXT,
  "" TEXT,
  "AlternativeName" TEXT
);

I don’t know the externally-governed rules.  I don’t know what rules the 
development team want to follow.  But I’m questioning whether it’s a good idea 
to create a column with that name.  If the dev team think it’s okay, that’s 
fine with me.

I would actually prefer it didn’t include the quotes signs in the column names. 
 They’re not in the .csv file.  But that’s a different matter.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[David Raymond]

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 sqlite_stat1, sqlite_stat3, and/or sqlite_stat4 
tables. That approach is not recommended except for the one scenario described 
in the next paragraph.

For a program that uses an SQLite database as its application file-format, when 
a new database instance is first created the ANALYZE command is ineffective 
because the database contain no data from which to gather statistics. In that 
case, one could construct a large prototype database containing typical data 
during development and run the ANALYZE command on this prototype database to 
gather statistics, then save the prototype statistics as part of the 
application. After deployment, when the application goes to create a new 
database file, it can run the ANALYZE command in order to create the statistics 
tables, then copy the precomputed statistics obtained from the prototype 
database into these new statistics tables. In that way, statistics from large 
working data sets can be preloaded into newly created application files.



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jens Alfke
Sent: Wednesday, October 04, 2017 12:06 PM
To: SQLite mailing list
Subject: Re: [sqlite] PRAGMA optimize; == no busy handler?


> So why not work around the whole process ?  On your development system, 
> create a database with plausible data in.  Run ANALYZE.  Then copy all the 
> tables named "sqlite_stat*" into a new database.

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.

I’m just a bit wary because this isn’t documented anywhere, and generally one 
is warned against writing to `sqlite_*` tables. I’d rather have some official 
blessing of this technique before I consider using it.

—Jens

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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

Creates a table with the following columns:

CREATE TABLE Authors(
  "ID" TEXT,
  "code" TEXT,
  "name" TEXT,
  "sortOrder" TEXT,
  "" TEXT,
  "AlternativeName" TEXT
);

I don’t know the externally-governed rules.  I don’t know what rules the 
development team want to follow.  But I’m questioning whether it’s a good idea 
to create a column with that name.  If the dev team think it’s okay, that’s 
fine with me.

I would actually prefer it didn’t include the quotes signs in the column names. 
 They’re not in the .csv file.  But that’s a different matter.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

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?
>
> SELECT group_concat(LineText, '\n') FROM
>   (SELECT LineText FROM DocLines
>   WHERE DocID = 10
>   ORDER BY LineIndex)

The group_concat() function has always concatenated rows in the order
they are received.  The problem is that the order they are received by
the function is not necessarily obvious from the input SQL, because
SQLite is prone doing some serious reorganizations of the input SQL in
its quest to come up with the fastest execution plan.

But, as it happens, we long ago added constraint 16 to the query
flattener (https://www.sqlite.org/draft/optoverview.html#flattening)
to prevent the flattener from running on queries like the one you show
above.  This restriction on the query flattener causes your example
query above to do what you want.

SQLite version 3.21.0 adds new restrictions on the query flattener
which allows the application to control whether expensive functions
(or subqueries) are run before or after sorting.  See
https://www.sqlite.org/draft/optoverview.html#deferred_work for
further information.  These new flattener restrictions, together with
the increased preference for using co-routines, are found in the
latest "Pre-release Snapshot".  Please try them out if you are able
to.

-- 
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] PRAGMA optimize; == no busy handler?

[Simon Slavin]

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 elsewhere though I’ve never used it.  
ANALYZE does not expect the stats tables to match the data.  It’s used to 
record tables lengths and 'chunkiness' to guide the optimizer.  And even if the 
stats tables are out of date, the optimizer is so good anyway that the 
difference it makes may not be great.

Users could do ANALYZE in a maintenance procedure perhaps once a year, or even 
just during a user-triggered "disk-check" routine, as is done in my PVR.  
'chunkiness' of data doesn’t change much over the life of a database.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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 leave the inner select?
> 
> SELECT group_concat(LineText, '\n') FROM
>   (SELECT LineText FROM DocLines
>   WHERE DocID = 10
>   ORDER BY LineIndex)

I’ve never tried it but I can’t think why it wouldn’t work.  Yes, you could 
register a function written in your favourite shimmed language.  All this 
really does is move the "concat_in_order" function from your own code into 
SQLite.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jens Alfke]

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 embedded 
systems. Personally, I’ve never used SQLite in circumstances where I could 
predict when the database would be idle, since it could be triggered by either 
user or network activity.

> So why not work around the whole process ?  On your development system, 
> create a database with plausible data in.  Run ANALYZE.  Then copy all the 
> tables named "sqlite_stat*" into a new database.

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.

I’m just a bit wary because this isn’t documented anywhere, and generally one 
is warned against writing to `sqlite_*` tables. I’d rather have some official 
blessing of this technique before I consider using it.

—Jens

* I have no idea what the breakdown of SQLite use cases is. (Does anyone?) But 
I know it’s ubiquitous in desktop apps (at least on MacOS), mobile apps, etc. 
which is where I’ve used it. And servers are probably using MySQL, Postgres, 
Couchbase, MongoDB, etc.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Doug Nebeker]

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') FROM
(SELECT LineText FROM DocLines
WHERE DocID = 10
ORDER BY LineIndex)

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

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 others).

On 10/4/17, Wout Mertens  wrote:
> 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(SELECT 1 FROM json_each(t.json,
> "$.foo") j where j.value = 'meep'));
> Segmentation fault: 11
>
> :-/
>
> Wout.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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] Segfault on query related to json_each in where clause

[Wout Mertens]

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(SELECT 1 FROM json_each(t.json,
"$.foo") j where j.value = 'meep'));
Segmentation fault: 11

:-/

Wout.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jean-Luc Hainaut]

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 version.
It is written in Python 2.7 through the standard SQLite3 interface but 
it should be easy to translate it in C:


https://www.dropbox.com/s/ilpx8duppbus8u3/group_concat2.py?dl=0

Hoping it will help!

Jean-Luc Hainaut


On 3 Oct 2017, at 11:13pm, Doug Nebeker  wrote:


How can I select a document and get the complete sorted text back in a single 
row (so I can do a JOIN on a different table with additional information)?

There is a way which will probably work but the documentation adds a careful 
note that it will not always work.



So you would want something like

SELECT group_concat(LineText, '\n') FROM
(SELECT LineText FROM DocLines
WHERE DocID = 10
ORDER BY LineIndex)

The problem is that the order of concatenation is arbitrary, in other words you 
shouldn’t rely on this working.

If you don’t want to use group_concat(), do it in your programming language.  
Use

SELECT LineText FROM DocLines
WHERE DocID = 10
ORDER BY LineIndex

and concatenate the retrieved values in your programming language.

Simon.
___
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] PRAGMA optimize; == no busy handler?

[Howard Kapustein]

>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. Altering SQL was insufficient to correct the problem.

I'm familiar with EXPLAIN QUERY PLAN. The query involves 5 tables. EXPLAIN 
QUERY PLAN shows table scans for 3 of them before ANALYZE vs after there's only 
1 table scan (and that's the table with 1 record so using an index wouldn't 
materially affect perf). This is probably a (the?) reason for the perf delta.


This doesn't answer my question -- logs show PRAGMA optimize; appears to skip 
the busy-handler and return SQLITE_BUSY. The documentation is silent re busy. 
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 
SQLite feels that performing database optimizations (such as running ANALYZE or 
creating new indexes) will improve the performance of future queries, then some 
database I/O may be done. Applications that want to limit the amount of work 
performed can set a timer that will invoke sqlite3_interrupt() if the pragma 
goes on for too long

SQLite's not performing too much work. It's performing NO work. Is this 
intended? How is optimize intended to interact with busy/locked conditions?

FYI ANALYZE docs (https://sqlite.org/lang_analyze.html) are equally silent 
regarding behavior in the face of locks and busy|locked conditions

I've looked over the source but we're into the deeper end of the complexity 
pool. I see hints of locks in a few spots but nothing definitive or clear.


How are PRAGMA optimize; and ANALYZE; expected to behave in the face of 
busy|locked conditions?
How are they expected to behave in relation to the busy-handler?

- Howard 


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Paul
Sent: Tuesday, October 3, 2017 10:34 PM
To: SQLite mailing list 
Subject: Re: [sqlite] PRAGMA optimize; == no busy handler?


From my personal experience, performance instability of SQLite queries 
(drastically jumping from milliseconds to seconds and back) that is fixable by 
running ANALYZE means that your queries are not optimal (some crucial indices 
are missing). The reason ANALYZE helps is because the info that planner 
receives helps to mitigate the absence of important indices by adapting the 
strategy. Say for example, you have a query that involves few tables being a 
JOIN-ed. There may be the case when SQLite has to scan one of the tables but is 
does not know which, so it uses some default logic that does not account table 
size (since SQLite does not know it). As the result, wrong table may be chosen 
(the largest one).

Why this happens exactly after the first record is inserted? Probably because 
Query Planner is smart and uses some meta info. Probably SQLite does not 
allocate a page for empty tables before the first record is inserted. Query 
Planner may take an advantage of this knowledge and optimizing query 
accordingly.

All of this is just a speculation, of course. What you definitely should do is 
run EXPLAIN QUERY PLAN on the sluggish query. Better yet, run EXPLAIN QUERY 
PLAN on *every* query, just to be sure.

-Paul


> >Don't do that
> Mostly sure, but there's some cases SQLite will skip the busyhandler 
> and immediately return, but they're not permanently-busy conditions. 
> We have multiple threads across multiple processes using the database
> 
> > why you’re doing "PRAGMA optimize" while other parts of your program 
> > are accessing the database
> Because we don't necessarily know there's other work in progress, and 
> we don't have a good opportunity to ensure we do it later but-no-later 
> than when we need it
> 
> >It makes more sense just to put ANALYZE into some sort of 
> >end-of-month procedure or a manual maintenance procedure your users 
> >run only when nobody else is using the system
> That's what we did but the real world isn't that simple
> 
> Last month we did...
> * ANALYZE when the staterepository service starts (effective boot 
> time) if it hasn't been done in a while (1 or 3 days, I forget)
> * ANALYZE at the end of a deployment operation if it hasn't been done 
> in a while (1min I think) @ 1st login there's 30+ operations in a short 
> window (1-2 minutes?) and folks are very anal^H^H^H^Hconscious re 1st login 
> perf. The workload is rather variable all told thus the 1min delay. We 
> usually wind up hitting this a couple of times @ 1st login. That primes 
> sqlite_stat1 sufficiently that statements execute in ms (at most) as 
> expected. .
> 
> We recently found a problem where you click on a hyperlink in Edge which 
> (among other things) queries the database and _sometimes_ takes 30s+ instead 
> of near-zero. But we

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

[Howard Kapustein]

>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 
SQLite feels that performing database optimizations (such as running ANALYZE or 
creating new indexes) will improve the performance of future queries, then some 
database I/O may be done. Applications that want to limit the amount of work 
performed can set a timer that will invoke sqlite3_interrupt() if the pragma 
goes on for too long...

I don't see it taking "too long". I see it not even getting to the point of 
doing i/o, apparently because it decided not to invoke the busyhandler but 
rather immediately return failure.

- Howard

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Tuesday, October 3, 2017 6:40 PM
To: SQLite mailing list 
Subject: Re: [sqlite] PRAGMA optimize; == no busy handler?



On 4 Oct 2017, at 2:23am, Howard Kapustein  
wrote:

> What do you think of an option to only analyze tables that have grown from 0 
> records to >0 e.g. PRAGMA optimize(0x10002) so 2=ANALYZE if beneficial and 
> 0x1=change the criteria from "increased by 25 times or more since the 
> last" to "increased from 0 records to 1+ record since the last?

ANALYZE builds a table of statistics which tells SQLite which search strategy 
will be optimal.  The table of statistics does not have to exactly match the 
data in your tables (in fact that’s very rare).  The big changes noted by 
ANALYZE are not to do with the number of rows in each table, they are to do 
with the 'chunkiness' of each indexed column: whether a column has only two 
values (indoor / outdoor) or thousands of different values (surname).  And 
'chunkiness' doesn’t change much once a database is big enough that search-time 
matters.

It is not expected that you’ll try to run ANALYZE while a database is in use.  
It’s intended for offline-maintenance.

So why not work around the whole process ?  On your development system, create 
a database with plausible data in.  Run ANALYZE.  Then copy all the tables 
named "sqlite_stat*" into a new database.

You can then copy those tables into the databases for your production system.  
They will 'tune' SQLite to pick strategies which work best for a typical 
dataset.  You never have to run ANALYZE or "PRAGMA optimize" on that system.

Yes, it won’t give absolutely the fastest possible operations on your 
production system(s).  But they will be very close to it, and you have the 
advantage of never having to lock the database up with a maintenance procedure.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7Choward.kapustein%40microsoft.com%7C26b587f8ebb3420dbc5508d50ac8ed06%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636426780457125699&sdata=5De2RQH4HM%2F%2BzisjcjwXA42VzKTVymtArf%2BP0ZIOk5g%3D&reserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users