Re: [sqlite] sqlite3_progress_handler(D,N,X,P)

[x]

Thanks for your input Ryan. I do think milliseconds would be more intuitive 
rather than having to guess the number of VMI’s that will be computed. The 
problem in the case I quote though is that there is only a few VMI’s for 
“select count(*) from Tbl” one of which is a call to the special count() 
function (see “explain select count(*) from Tbl”). Once it enters the count() 
function there is no opportunity to cancel. I.e. either you get the count or 
you don’t.



Gunter, just seen your interesting post. Do you know if that would work while 
the count() function was being executed?




From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
R Smith <ryansmit...@gmail.com>
Sent: Thursday, May 24, 2018 5:03:14 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] sqlite3_progress_handler(D,N,X,P)


On 2018/05/24 5:41 PM, x wrote:
> The parameter N is the approximate number of virtual machine 
> instructions<https://sqlite.org/opcode.html> that are evaluated between 
> successive invocations of the callback X.
>
> Would it not have been better if N was the number of milliseconds between 
> invocations? It’s not much use if you want to abort from a lengthy “select 
> count(*) from Tbl” as there are only a couple of virtual machine instructions 
> whereas “select count(*) from Tbl,Tbl” might have billions of VMI’s.

I think VM steps generally happen in quick succession, never minutes apart.

Either way, milliseconds elapsed is equally useless - plus what could
possibly be easier than measuring time yourself?

Pseudo code example, but it will work for near any platform with some
numeric representation of "time".
...
curtime = now;
elapsed = curtime - prevtime;
prevtime = curtime;
...
print elapsed;
...


What you cannot do, is deduce VM steps like that, so indeed you are
better off with the progress handler reporting VM steps (which you
cannot deduce) rather than elapsed time (which you can).

What we REALLY want however, is to know the final intended VM steps so
that curstep/finalstep will give us some idea of the progress, but it is
impossible to know (even in principle) before the actual query result
have been produced at least once.

So at this point we are condemned to reporting to the user something
like "Look mate, it's still busy, but at least the current VM steps >
previous VM steps, so it ain't hung. Shall we wait some more, or
cancel?" - to which the good user hopefully do not cancel prematurely
(though that is hard to expect with zero confidence in which exact VM
milestone constitutes "premature").
Luckily this is less of a problem in custom system code, since you can
test every query, even on big data. It gets hard when you make a DB
manager of sorts in which allowing ANY query is a necessity.

Point being: There is no good answer to this.


___
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] sqlite3_progress_handler(D,N,X,P)

[R Smith]

On 2018/05/24 5:41 PM, x wrote:

The parameter N is the approximate number of virtual machine 
instructions that are evaluated between 
successive invocations of the callback X.

Would it not have been better if N was the number of milliseconds between 
invocations? It’s not much use if you want to abort from a lengthy “select 
count(*) from Tbl” as there are only a couple of virtual machine instructions 
whereas “select count(*) from Tbl,Tbl” might have billions of VMI’s.


I think VM steps generally happen in quick succession, never minutes apart.

Either way, milliseconds elapsed is equally useless - plus what could 
possibly be easier than measuring time yourself?


Pseudo code example, but it will work for near any platform with some 
numeric representation of "time".

...
curtime = now;
elapsed = curtime - prevtime;
prevtime = curtime;
...
print elapsed;
...


What you cannot do, is deduce VM steps like that, so indeed you are 
better off with the progress handler reporting VM steps (which you 
cannot deduce) rather than elapsed time (which you can).


What we REALLY want however, is to know the final intended VM steps so 
that curstep/finalstep will give us some idea of the progress, but it is 
impossible to know (even in principle) before the actual query result 
have been produced at least once.


So at this point we are condemned to reporting to the user something 
like "Look mate, it's still busy, but at least the current VM steps > 
previous VM steps, so it ain't hung. Shall we wait some more, or 
cancel?" - to which the good user hopefully do not cancel prematurely 
(though that is hard to expect with zero confidence in which exact VM 
milestone constitutes "premature").
Luckily this is less of a problem in custom system code, since you can 
test every query, even on big data. It gets hard when you make a DB 
manager of sorts in which allowing ANY query is a necessity.


Point being: There is no good answer to this.


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

[sqlite] sqlite3_progress_handler(D,N,X,P)

[x]

The parameter N is the approximate number of virtual machine 
instructions that are evaluated between 
successive invocations of the callback X.

Would it not have been better if N was the number of milliseconds between 
invocations? It’s not much use if you want to abort from a lengthy “select 
count(*) from Tbl” as there are only a couple of virtual machine instructions 
whereas “select count(*) from Tbl,Tbl” might have billions of VMI’s.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_progress_handler do not seem to be called byvacuum

[Domingo Alvarez Duarte]

Hello !  

Also a related question, if a database already has stats tables do we need to
run analize after a vacuum ? Or vacuum is smart enough to see the stats
tables and run analize by itself ?  

Cheers !  
>  Thu Mar 17 2016 08:51:11 PM CET from "Richard Hipp"  
>Subject: Re: [sqlite] sqlite3_progress_handler do not seem to be called
>byvacuum
>
>  On 3/17/16, Domingo Alvarez Duarte  wrote:
>  
>>Hello !
>> 
>> Hello I have an application that uses sqlite3_progress_handler to update a
>> gui application and when we do a "vacuum" on a big database the gui get
>> frozen, maybe vacuum should use different parameters to call
>> sqlite3_progress_handler I'm using actually:
>> 
>> db.progress_handler(1000, sqlite3_progress_handler, this.weakref());
>> 
>> And with a 1000 as parameter it works fine for select.
>> 
>> Sqlite3 is really calling sqlite3_progress_handler ? Or it's not
>> implemented
>> there ?
>> 
>> 

>  Vacuum works by running a bunch of secondary SQL statements. Each of
> these secondaries has its own progress counter. So, in other words,
> the progress counter is not cumulative for a VACUUM. Maybe your 1000
> is set to high and none of the many secondary SQL statements ever hit
> the limit.
> -- 
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] sqlite3_progress_handler do not seem to be called byvacuum

[Domingo Alvarez Duarte]

Hello !  

Thanks for reply !  

That's why I said that vacuum and maybe other operations need to interpret
the parameter passed to? sqlite3_progress_handler differently otherwise a
setting that works on general case do not work with then, maybe a mix of
number of operations/time ?  

 Cheers !

>  Thu Mar 17 2016 08:51:11 PM CET from "Richard Hipp"  
>Subject: Re: [sqlite] sqlite3_progress_handler do not seem to be called
>byvacuum
>
>  On 3/17/16, Domingo Alvarez Duarte  wrote:
>  
>>Hello !
>> 
>> Hello I have an application that uses sqlite3_progress_handler to update a
>> gui application and when we do a "vacuum" on a big database the gui get
>> frozen, maybe vacuum should use different parameters to call
>> sqlite3_progress_handler I'm using actually:
>> 
>> db.progress_handler(1000, sqlite3_progress_handler, this.weakref());
>> 
>> And with a 1000 as parameter it works fine for select.
>> 
>> Sqlite3 is really calling sqlite3_progress_handler ? Or it's not
>> implemented
>> there ?
>> 
>> 

>  Vacuum works by running a bunch of secondary SQL statements. Each of
> these secondaries has its own progress counter. So, in other words,
> the progress counter is not cumulative for a VACUUM. Maybe your 1000
> is set to high and none of the many secondary SQL statements ever hit
> the limit.
> -- 
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] sqlite3_progress_handler do not seem to be called by vacuum

[Domingo Alvarez Duarte]

Hello !  

Hello I have an application that uses sqlite3_progress_handler to update a
gui application and when we do a "vacuum" on a big database the gui get
frozen, maybe vacuum should use different parameters to call
sqlite3_progress_handler I'm using actually:  

db.progress_handler(1000, sqlite3_progress_handler, this.weakref());  

And with a 1000 as parameter it works fine for select.  

Sqlite3 is really calling sqlite3_progress_handler ? Or it's not implemented
there ?  

Cheers !

[sqlite] sqlite3_progress_handler do not seem to be called by vacuum

[Simon Slavin]

On 17 Mar 2016, at 7:34pm, Domingo Alvarez Duarte  wrote:

> Hello I have an application that uses sqlite3_progress_handler to update a
> gui application and when we do a "vacuum" on a big database the gui get
> frozen

VACUUM gets translated into just one virtual machine instruction.  Since it's 
all one long instruction the progress handler can't see any breakdown of the 
many things it does.

Try EXPLAIN VACUUM to see what's going on.

Simon.

[sqlite] sqlite3_progress_handler do not seem to be called byvacuum

[Richard Hipp]

On 3/17/16, Domingo Alvarez Duarte  wrote:
> Hello !
>
> Also a related question, if a database already has stats tables do we need
> to
> run analize after a vacuum ? Or vacuum is smart enough to see the stats
> tables and run analize by itself ?

VACUUM does not change the stats, so it does not matter.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] sqlite3_progress_handler do not seem to be called by vacuum

[Richard Hipp]

On 3/17/16, Domingo Alvarez Duarte  wrote:
> Hello !
>
> Hello I have an application that uses sqlite3_progress_handler to update a
> gui application and when we do a "vacuum" on a big database the gui get
> frozen, maybe vacuum should use different parameters to call
> sqlite3_progress_handler I'm using actually:
>
> db.progress_handler(1000, sqlite3_progress_handler, this.weakref());
>
> And with a 1000 as parameter it works fine for select.
>
> Sqlite3 is really calling sqlite3_progress_handler ? Or it's not
> implemented
> there ?
>

Vacuum works by running a bunch of secondary SQL statements.  Each of
these secondaries has its own progress counter.  So, in other words,
the progress counter is not cumulative for a VACUUM.  Maybe your 1000
is set to high and none of the many secondary SQL statements ever hit
the limit.
-- 
D. Richard Hipp
drh at sqlite.org

Re: [sqlite] sqlite3_progress_handler callback behavior change

[Andreas Stieger]

Hi,

> On 13 Mar 2014, at 13:21, big stone  wrote:
> Does it mean we may have SQLite3.8.4.1 in imminent Python 3.4 release ?
> 
> http://bugs.python.org/issue20901

This question did come up when building/packaging that and other versions of 
Python with SQLite 3.8.4(.1), where that caused a test failure. I was told that 
Python source does not contain a specific version of SQLite, while binaries 
will.
As such the answer clarifies that the Python tests needed to be adjusted to 
support 3.8.4, and binaries may or may not contain it at some point.

Many thanks for the replies. 
Andreas

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

Re: [sqlite] sqlite3_progress_handler callback behavior change

[big stone]

Hello Andreas,

Does it mean we may have SQLite3.8.4.1 in imminent Python 3.4 release ?

http://bugs.python.org/issue20901
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_progress_handler callback behavior change

[Richard Hipp]

On Wed, Mar 12, 2014 at 5:27 PM, Andreas Stieger wrote:

> Hello,
>
> I noticed a change in behavior of sqlite3_progress_handler for CREATE
> TABLE, or rather the calls to the callbacks, and just wanted to get
> clarification if this was intentional.
>
> Specifically, given the code below for a callback that prints one "."
> for each code for this: (full code below)
> sqlite3_progress_handler(db, 1, progress_callback, NULL);
> sqlite3_exec(db, "create table foo(a,b)", callback, 0, );
> \n
> sqlite3_progress_handler(db, 2, progress_callback, NULL);
> sqlite3_exec(db, "create table bar(a,b)", callback, 0, );
>
> I get the following:
> 3.7.17> ./test
> ..
> ...
> 3.8.3.1> ./test
> 
> ...
> 3.8.4> ./test
> .
> .
> 3.8.4.1> ./test
> .
> .
>
> Is this intentional, a side effect of  optimization or unintended
> behavior? Documentation does day the callbacks are approximate...
>

Side-effect of optimization.

The progress-callback is now only checked at jump opcodes, not after every
opcode, since checking after every opcode uses a measurable fraction of CPU
cycles for a feature that is very rarely used.   The reduced checking
frequency makes not different if you put a reasonable number into the
progress callback, like say "100" or "1000".  It only shows up with a
callback interval of 1 or 2.

The second fact is that newer versions of SQLite use fewer VDBE opcodes to
accomplish the same task.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_progress_handler callback behavior change

[Andreas Stieger]

Hello,

I noticed a change in behavior of sqlite3_progress_handler for CREATE
TABLE, or rather the calls to the callbacks, and just wanted to get
clarification if this was intentional.

Specifically, given the code below for a callback that prints one "."
for each code for this: (full code below)
sqlite3_progress_handler(db, 1, progress_callback, NULL);
sqlite3_exec(db, "create table foo(a,b)", callback, 0, );
\n
sqlite3_progress_handler(db, 2, progress_callback, NULL);
sqlite3_exec(db, "create table bar(a,b)", callback, 0, );

I get the following:
3.7.17> ./test
..
...
3.8.3.1> ./test

...
3.8.4> ./test
.
.
3.8.4.1> ./test
.
.

Is this intentional, a side effect of  optimization or unintended
behavior? Documentation does day the callbacks are approximate...


Thanks,
Andreas

test code follows...


#include 
#include 

static int callback(void *NotUsed, int argc, char **argv, char **azColName){
  int i;
  for(i=0; i

Re: [sqlite] Sqlite3_progress_handler and automatic rollback

[Max Vlasov]

On Thu, Oct 28, 2010 at 2:03 PM, Max Vlasov  wrote:

>
> The problem is that (tested with 3.6.10 and 3.7.2) if :
> - I interrupt a query from Sqlite3_progress_handler
> - I didn't invoke this error-processing rollback,
>
> ...sqlite after that does the following:
> - sqlite3_get_autocommit starts to return 0 (not in auto-commit mode) now
> and forever. (But the journal file after interrupted query is not longer
> present, so automatic rollback was actually fired by sqlite)
> - my 'begin transaction' queries executes successfully, but every following
> query that was noticeably fast with normal logic, now slows down like
> there's no transaction at all.
>
>
>
Sorry, I must admit this was a bug in my internal implementation of "nested"
transactions. Rollback that checked for sqlite3_get_autocommit bypassed not
just rollback itself, but also the counter of the BEGIN END calls so the
side effects I initially interpreted as sqlite' were actually mine.

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

[sqlite] Sqlite3_progress_handler and automatic rollback

[Max Vlasov]

Hi,
I try to use Sqlite3_progress_handler in my program and everything seems
work fine, except for some unexplained things related to rollback and
Sqlite3_get_autocommit function.

I assume that everything that is related to sqlite3_interrupt can be applied
to interrupting from Sqlite3_progress_handler, right?

First, I saw the behavior that officially explained in the docs. It's about
automatic rollback that occurs when SQLITE_INTERRUPT was returned by the
step function as a result from sqlite3_interrupt or
Sqlite3_progress_handler. The docs says that I either can do rollback with
an error (in my case it was "Sqlite logic or missing database") and it's ok
or rely on the result of sqlite3_get_autocommit function. As long as I
understand If my code invoked 'begin transaction' and this functions returns
non-zero (indicating auto commit is back) then an automatic rollback was
invoked by sqlite itself before and there's no need to do it manually.

The problem is that (tested with 3.6.10 and 3.7.2) if :
- I interrupt a query from Sqlite3_progress_handler
- I didn't invoke this error-processing rollback,

...sqlite after that does the following:
- sqlite3_get_autocommit starts to return 0 (not in auto-commit mode) now
and forever. (But the journal file after interrupted query is not longer
present, so automatic rollback was actually fired by sqlite)
- my 'begin transaction' queries executes successfully, but every following
query that was noticeably fast with normal logic, now slows down like
there's no transaction at all.


So, there are several options:
- it Is a bug somewhere and sqlite just didn't return some variable so after
some fix we still can rely on no-op of rolling back interrupted queries.
- rollback is necessary (even if the query was interrupted). In this case I
see two things: documentation that possible should address this and second
the error that sqlite makes when rollback applied to an interrupted query.
If rollback is necessary, I think there should be no error or maybe some
special error (not so widely spread "Sqlite logic or missing...") in order
to detect this special case in my code

Thanks,

Max Vlasov
maxerist.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_progress_handler, sqlite3_commit_hook

[Micha Bieber]

@list

Following the documentation, the two functions have been declared
'experimental'. Does someone know, how reliable these calls are actually
? There are specific serious open issues, rough estimates when to fix
them, etc. ?

Micha

Re: [sqlite] sqlite3_progress_handler

[drh]

Marco Bambini <[EMAIL PROTECTED]> wrote:
> From the documentation: "If the progress callback returns a result  
> other than 0, then the current query is immediately terminated...",  
> so next time I call sqlite3_step what is the error returned?  
> SQLITE_ERROR or SQLITE_ABORT or something else?
> 

SQLITE_ABORT.

--
D. Richard Hipp <[EMAIL PROTECTED]>

[sqlite] sqlite3_progress_handler

[Marco Bambini]

From the documentation: "If the progress callback returns a result  
other than 0, then the current query is immediately terminated...",  
so next time I call sqlite3_step what is the error returned?  
SQLITE_ERROR or SQLITE_ABORT or something else?


Thanks a lot.
---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/