Re: [sqlite] Causal profiling
I watched the video and wondered about the virtual table calls within sqlite as well. Without them you would have no VFS though... so they are needed. Could there be some compile time option to force Linux or Windows statically? Maybe. I do not know the effect of collecting debug information using -g, but if using -O2 to compile sqlite with GCC the following optimizations from https://gcc.gnu.org/onlinedocs/gcc/Optimize-Options.html#Optimize-Options are enabled: -fdevirtualize -fdevirtualize-speculatively So depending on what compiler used and version etc, those virtual calls could all disappear anyway. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is there a way for lemon to output its DFA with GOTO's for reductions?
Hey All, I have this state in my gram.out: State 6: mem ::= idlist ptr idlist * SEMI idlist ::= idlist * IDENT IDENT shift-reduce 3 idlist ::= idlist IDENT SEMI shift-reduce 2 mem ::= idlist ptr idlist SEMI The numbers above, 3 and 2, refer to rules listed at the bottom of the file. I am learning about lemon and compared this to tables found online about parsers but cannot understand where the DFA will go after the reduction. Other states within lemon show a shift AND the number is the state where it is going to next instead of a rule number. Here is the trace output from the parser when using the rule. It shows we would reduce by rule 2 and then go to state 3. debug:Shift 'SEMI', pending reduce 2 debug:Return. Stack=[typelist TYPEDEF STRUCT IDENT LBLOCK memlist idlist ptr idlist SEMI] debug:Input 'RBLOCK' with pending reduce 2 debug:Reduce 2 [mem ::= idlist ptr idlist SEMI], go to state 3. debug:... then shift 'mem', pending reduce 7 debug:Reduce 7 [memlist ::= memlist mem], go to state 1. debug:... then shift 'memlist', go to state 3 debug:Shift 'RBLOCK', go to state 9 debug:Return. Stack=[typelist TYPEDEF STRUCT IDENT LBLOCK memlist RBLOCK] Thank you for any help of insight. -Alex ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Endless loop possible with simultaneous SELECT and UPDATE?
So in the original code if I added a NOT INDEXED it would be valid? Also,
would an ORDER BY Auth_id ASC fix the issue, since I an not adding any new
rows the auth_ids would remain constant?
Wow I did not know that you could call open multiple times on the same
database! So the following is valid and safe (considering POSIX locking
etc.)
sqlite3_open("database1", );
sqlite3_open("database1", );
// loop start
// BEGIN TRANSACTION BOTH dbs
// do SELECT on db1
// do UPDATE on db2
// END TRANSACTION
// loop end
// close db1
// use db2 for rest of the program...
I have not seen this pattern within any sqlite3 code before so I think it
is not well known that this is a design pattern for searching and updating
at the same time.
If I were determined to stay within defined behavior AND to only use one
connection, would the following updated code attain that.
#define SELECT_EXPIRED_IDS \
"SELECT Auth_id FROM AuthTable WHERE Auth_id > ? AND expiration < ? " \
"ORDER BY Auth_id ASC LIMIT 128;"
#define UPDATE_SESID_EXPIRED \
"UPDATE AuthTable SET sesCookie=?, expiration=? WHERE Auth_id=?;"
static void
expire_sesid(void)
{
int auth_ids[128] = {0};
int i=0, j;
/* get raw current time */
current_time = get_current_db_time(false);
/* prepare SQL queries */
sqlite3_prepare_v2(db, SELECT_EXPIRED_IDS,
-1, _info, NULL);
sqlite3_prepare_v2(db, UPDATE_SESID_EXPIRED,
-1, _ses_expired, NULL);
do {
sqlite3_bind_int(expire_info, 1, auth_ids[i]);
sqlite3_bind_int(expire_info, 2, current_time);
/* while there is work to be done */
i=0;
while (sqlite3_step(expire_info) == SQLITE_ROW) {
auth_ids[i++] = sqlite3_column_int(expire_info, 0); /* auth_id */
}
sqlite3_reset(expire_info);
for (j=0; j < i; j++) {
/* generate new session id */
generate_ses_id(ses_id);
/* update ses_id and internal expiration to a year ahead, log in
will set it to a month for user log in */
sqlite3_bind_text(update_ses_expired, 1, ses_id,
16, SQLITE_STATIC);
sqlite3_bind_int64(update_ses_expired, 2, current_time_plus_year);
sqlite3_bind_int(update_ses_expired, 3, auth_ids[j]);
sqlite3_step(update_ses_expired);
sqlite3_reset(update_ses_expired);
}
} while ( i == 128 );
/* all work has completed */
sqlite3_finalize(expire_info);
sqlite3_finalize(update_ses_expired);
return;
}
On Sun, Sep 1, 2019 at 3:47 PM Keith Medcalf wrote:
>
> On Sunday, 1 September, 2019 11:12, Alexander Vega
> wrote:
>
> >Thank you Keith for your answer. It has led me to more questions.
>
> >"though you may or may not have visited all rows"
> >From the documentation I did not get the impression that you would
> >ever not visit ALL ROWS at least once. Is there a technical reason
> >for this? I would assume a full table scan is walking the un-ordered
> >leaf pages of the B*tree?
>
> How do you know that you are doing a table scan? This certainly cannot be
> assumed. Perhaps the AuthTable has 57 columns with a total length of
> several hundred bytes per row but there also happens to be an index on a
> subset of the columns that includes the two columns that you have asked
> for. Perhaps you are "table scanning" that covering index instead (because
> it is cheaper than reading the actual table)? There are ways to insist on
> a table scan (select ... from table NOT INDEXED ...) for example. However,
> you left it up to the database engine to choose the most cost effective way
> to answer your select (which is how SQL works ... it is a declarative
> language ... you declare what you want and the database figures out the
> best way to go about giving you what you asked for).
>
> As a result of updating the first such row thus received the index has now
> changed such that the row you are operating on became the last row in the
> index being scanned. Therefore there is no "next" row. You will have
> visited only one row, even though there might have been millions of rows in
> the table.
>
> >"Your outer query should probably be "select auth_id, expiration from
> >AuthTable where expiration <= ? order by +auth_id, +expiration" and
> >binding current_time as the parameter since there is no point in
> >retrieving rows that you will not be updating is there? "
>
> >You are correct that does make sense. I guess I was trying avoid any
> >ambiguities of a WHERE clause on the SELECT because I do not
> >understand its behavior in this circumstance.
>
> If you cannot understand the behaviour with a WHERE clause, then what
> would make you think that one without a WHERE clause would be any more
> transparent, especially given that all Relational Databases are designed to
> provide you the results you asked for as efficiently as possible? P
Re: [sqlite] Endless loop possible with simultaneous SELECT and UPDATE?
Thank you Keith for your answer. It has led me to more questions.
"though you may or may not have visited all rows"
From the documentation I did not get the impression that you would ever not
visit ALL ROWS at least once. Is there a technical reason for this? I would
assume a full table scan is walking the un-ordered leaf pages of the B*
tree?
"Your outer query should probably be "select auth_id, expiration from
AuthTable where expiration <= ? order by +auth_id, +expiration" and binding
current_time as the parameter since there is no point in retrieving rows
that you will not be updating is there? "
You are correct that does make sense. I guess I was trying avoid any
ambiguities of a WHERE clause on the SELECT because I do not understand its
behavior in this circumstance.
You mentioned two database connections to the same database. Is this going
to work if I am using Threadsafe mode = 0? Would the second connection be
done through an attach?
Does this conversation change if I wrap the whole select and updates in one
transaction? e.g. BEGIN...END
Thanks
On Sun, Sep 1, 2019 at 1:32 AM Keith Medcalf wrote:
>
> > Having read : https://www.sqlite.org/isolation.html
> > Specifically the line "And the application can UPDATE the current row
> > or any prior row, though doing so might cause that row to reappear in a
> > subsequent sqlite3_step()."
>
> > Is it possible to create and endless loop
>
> Eventually you will have no more rows to update and therefore the
> underlying structures become stable and the select loop will eventually run
> out of rows, though you may or may not have visited all rows, and may visit
> some rows two or more times (once before update and more than once after).
>
> If you change the outer query to "select auth_id, expiration from
> AuthTable order by +auth_id, +expiration" then you will PROBABLY never have
> a problem since the results will LIKELY be from a sorter and not from the
> underlying table, and therefore mutation of the underlying tables and
> indexes will not interfere with the result of the outer select, even if
> those mutations affect the AuthTable or the indexes on it. Some SQL
> varients use a FOR UPDATE clause on a SELECT to tell the query planner that
> you intend to dally-about with the underlying datastore without having the
> proper isolation in place. The SQLite way of doing this is by requesting a
> row sorter not dependent on indexes by using the +columnname syntax in an
> order by on the select.
>
> Your outer query should probably be "select auth_id, expiration from
> AuthTable where expiration <= ? order by +auth_id, +expiration" and binding
> current_time as the parameter since there is no point in retrieving rows
> that you will not be updating is there?
>
> The correct solution is, of course, to use separate connections so that
> you have isolation between the select and the updates.
>
> You SHOULD be executing the outer select on one connection and the updates
> on another connection. This will work for journal mode delete unless the
> number of changed pages is too large to fit in sqlite's cache, in which
> case you may get an error from the update statement when it needs to spill
> the cache, and you will need to kaibosh the whole thing and do the updates
> in smaller chunks by putting a limit on the outer select and looping the
> whole thing until there are no more rows to process. (or increase the
> cache_size to be sufficient).
>
> You can avoid that particular problem by having the database in
> journal_mode=WAL in which case you can even process each update in its own
> transaction if you wish (get rid of the db2.beginimmediate() and
> db2.commit(), though then you will have to handle the eventuality of
> getting errors on the UPDATE).
>
> db1 = Connection('database.db')
> db1.executescript('pragma journal_mode=WAL;')
> db2 = Connection('database.db')
> current_time = datetime.now()
> current_time_plus_one_year = current_time.add(years=1)
> sess_id = ... some constant ...
> db2.beginimmediate()
> for row in db1.execute('select auth_id, expiration from authtable where
> expiration <= ?;',
>(current_time,)):
> db2.execute('update authtable set sesCookie = ?, expiration = ? where
> auth_id = ?;',
> (generate_ses_id(sess_id), current_time_plus_one_year,
> row.auth_id,))
> db2.commit()
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Endless loop possible with simultaneous SELECT and UPDATE?
Having read : https://www.sqlite.org/isolation.html
Specifically the line "And the application can UPDATE the current row or
any prior row, though doing so might cause that row to reappear in a
subsequent sqlite3_step()."
Is it possible to create and endless loop with the following (pseudo)code?
#define SELECT_EXPIRE_INFO \
"SELECT Auth_id, expiration FROM AuthTable;"
#define UPDATE_SESID_EXPIRED \
"UPDATE AuthTable SET sesCookie=?, expiration=? WHERE Auth_id=?;"
static void
expire_sesid(void)
{
/* get raw current time */
current_time = get_current_db_time(false);
/* prepare SQL queries */
sqlite3_prepare_v2(db, SELECT_EXPIRE_INFO,
-1, _info, NULL);
sqlite3_prepare_v2(db, UPDATE_SESID_EXPIRED,
-1, _ses_expired, NULL);
/* while there is work to be done */
while (sqlite3_step(expire_info) == SQLITE_ROW) {
auth_id = sqlite3_column_int(expire_info, 0); /* auth_id */
expiration_time = sqlite3_column_int64(expire_info, 1); /* expiration
*/
/* if the session is expired, today is greater than expiration date */
if ( current_time > expiration_time ) {
/* generate new invalid session id */
generate_ses_id(ses_id);
/* invalidate ses_id and set internal expiration to a year ahead, log
in
will set it to a month for user log in */
sqlite3_bind_text(update_ses_expired, 1, ses_id,
16, SQLITE_STATIC);
sqlite3_bind_int64(update_ses_expired, 2, current_time_plus_year);
sqlite3_bind_int(update_ses_expired, 3, auth_id);
sqlite3_step(update_ses_expired);
sqlite3_reset(update_ses_expired);
}
}
/* all work has completed */
sqlite3_finalize(expire_info);
sqlite3_finalize(update_ses_expired);
return;
}
I appreciate everyone's time,
-Alex V
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
