Re: [sqlite] two threads block eachother opening db (WAL)
On 2 July 2018 at 23:32, Charles Samuels wrote: > I have found that when my process has a lot of threads each of which opens > a > DIFFERENT database, they each block on eachother while opening each > database. > > This is at least on conflict with the documentation, as the documentation > suggests that a thread is a process for the purposes of sqlite's > concurrency, Actually I believe the mutex acquisition here is to provide those semantics, because unix file locks are necessarily bound to a process and not a thread. So sqlite must manage some of the per-thread details itself to correctly handle the case where multiple threads are using the same DB, possibly with different locking levels, and the mutex protects those shared structures. Intuitively it seems like a mutex held on the sqlite3_file structure rather than a global mutex could be used for this purpose which would prevent unrelated databases from contesting each other, however... > I also feel like it's a bug in > general, as opening two unrelated databases should not block eachother. > ... unix is something of a complex beast and the question of whether two databases are unrelated is not as simple as it may seem. You might have two databases /tmp/db and /var/tmp/db, but what if /var/tmp is a symlink to /tmp? Or what if the two db files are hardlinks of the same inode? Identifying multiple opens of the same inode is the other problem the mutex is helping to solve here. And it's a detail sqlite _must_ be aware of because of the bizarre POSIX locking semantics where closing a file descriptor relinquishes all locks associated with its inode, across the entire process. So if sqlite isn't careful about this, corruption can easily result. I'm not saying there isn't room for improvement here, but it's not a simple problem! Each thread blocks at this point: > > #0 __lll_lock_wait () at ../sysdeps/unix/sysv/linux/x86 > _64/lowlevellock.S:135 > #1 0x774e4b95 in __GI___pthread_mutex_lock (mutex=0x77dd8148 > ) > at ../nptl/pthread_mutex_lock.c:80 > #2 0x77b26419 in unixEnterMutex () at sqlite3.c:31952 > #3 unixLock (id=0x74a25180, eFileLock=1) at sqlite3.c:32894 > #4 0x77b1f2fa in sqlite3OsLock (lockType=1, id=) > at > sqlite3.c:21299 > #5 pagerLockDb (pPager=pPager@entry=0x74a25008, eLock=eLock@entry=1) > at > sqlite3.c:50293 > #6 0x77b1f34b in pagerLockDb (eLock=1, pPager=0x74a25008) at > sqlite3.c:53054 > #7 pager_wait_on_lock (pPager=pPager@entry=0x74a25008, > locktype=locktype@entry=1) at sqlite3.c:53051 > #8 0x77b55d6c in sqlite3PagerSharedLock (pPager=0x74a25008) > at > sqlite3.c:54293 > #9 0x77b56835 in lockBtree (pBt=0x74a151e8) at sqlite3.c:64591 > #10 sqlite3BtreeBeginTrans (p=0x74a1b508, wrflag=wrflag@entry=0) at > sqlite3.c:64956 > #11 0x77b82997 in sqlite3InitOne (db=0x74a12008, iDb=iDb@entry=0, > > pzErrMsg=pzErrMsg@entry=0x751fe778) at sqlite3.c:119558 > #12 0x77b82aca in sqlite3Init (db=0x74a12008, > pzErrMsg=pzErrMsg@entry=0x751fe778) > at sqlite3.c:119740 > #13 0x77b82b00 in sqlite3ReadSchema > (pParse=pParse@entry=0x751fe770) at sqlite3.c:119765 > #14 0x77b8d8e4 in sqlite3Pragma (pParse=0x751fe770, > pId1=pId1@entry=0x751fddd0, > pId2=pId2@entry=0x751fdde8, pValue=pValue@entry=0x751fde18, > minusFlag=minusFlag@entry=0) > at sqlite3.c:117300 > Hm, this backtrace doesn't seem to have anything to do with WAL mode, so I'm not sure why using the rollback journal makes for a 5 time speedup. Perhaps because the same mutex is used to protect shared-memory accesses? sqlite3OSLock really doesn't do much with the mutex held; is there another thread holding things up via a different codepath? -Rowan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Segfault in window function
Beautiful, working on my end now as well. Thanks so much. On Mon, Jul 2, 2018 at 12:49 PM, Dan Kennedy wrote: > On 07/02/2018 10:54 PM, Charles Leifer wrote: > >> I'm getting a segfault with the following query, which uses window >> functions: >> > > Thanks for reporting this. Should now be fixed here: > > https://www.sqlite.org/src/info/693b4350d7413912 > > Thanks also for the sum() bug report in the follow-up. That one is fixed > here: > > https://www.sqlite.org/src/info/693b4350d7413912 > > Dan. > > > > > >> SELECT "t1"."counter", "t1"."value", RANK() OVER w AS "rank" >> FROM "sample" AS "t1" >> WINDOW w AS (PARTITION BY "t1"."counter" ORDER BY "t1"."value" DESC) >> ORDER BY "t1"."counter", RANK() OVER w >> >> The sql used to create the "sample" table: >> >> CREATE TABLE IF NOT EXISTS "sample" ( >>"id" INTEGER NOT NULL PRIMARY KEY, >>"counter" INTEGER NOT NULL, >>"value" REAL NOT NULL); >> >> Test data: >> >> INSERT INTO "sample" (counter, value) VALUES (1, 10.), (1, 20.), (2, 1.), >> (2, 3.), (3, 100.); >> >> The expected output for the window query is: >> >> 1, 20., 1 >> 1, 10., 2 >> 2, 3., 1 >> 2, 1., 2 >> 3, 100., 1 >> ___ >> 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] Window Function Crash -- overriding builtin aggregate
Dan, Yes, the exact query looks like this: with gsrange(f, t) as (values(1, 10)) select if(value == f, null, value-1) as rowvalue_1, value as rowvalue_2, if(value == t, null, value+1) as rowvalue_3, if(value == f or value == t, (if(value == f, 0, value-1) + value + if(value == t, 0, value+1)) / 2.0, value) as checkresult, avg(value) over (rows between 1 preceding and 1 following) as windowresult from generate_series join gsrange where start=f and stop=t; It now returns a "avg() may not be used as a window function" error. (the if(expr1, expr2, expr3) function implementes pretty much expr1 ? expr2 : expr3 (ie, case when expr1 then expr2 else expr3 end)) --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Dan Kennedy >Sent: Monday, 2 July, 2018 10:21 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Window Function Crash -- overriding builtin >aggregate > >On 07/02/2018 01:40 PM, Keith Medcalf wrote: >> I have overridden the builtin AVG function with a function of my >own that computes the average by using the "running average" method >rather than the simple sum/count method. This function is registered >as an old fashioned aggregate function. >> >> After the window extension is put in place, it appears that >somewhere the wires are getting crossed as it appears to call my >aggregate step function is getting called, however, when it tries to >call the xValue function, SQLite3 crashes. (the call is apparently >to "my" override xValue function, which is, of course, null -- not to >the original builtin avg xValue pointer which would not work either >but which would not result in a crash). > >Thanks for reporting this. The query causing the crash is a >window-function query (i.e. "avg(...) OVER ...", correct? > >Assuming so, should now be fixed here: > > https://www.sqlite.org/src/info/4f3c8a82fd1c5b14 > >Dan. > > > >> >> If I register "all" the methods (even though they do not work >correctly with a running calculation) the internal function is fully >overridden. >> >> Even if I register the override function using the new >sqlite_create_window_function and explicitly set the xValue and >xInverse to 0 (null), the program still crashes when attempting to >run the non-existent xValue function ... >> >> --- >> 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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New WIndow Functions ... question
On 07/02/2018 06:20 AM, Keith Medcalf wrote: They are part of the current draft release: http://www.sqlite.org/draft/releaselog/3_25_0.html Dan, I see that there is a new create function to create the window functions which have some slight changes to the methods being called and what they do. I presume that a function defined with the new API can continue to be used as a "normal" aggregate function, but that the new API must be used to define aggregates that can be used with the windowing calls (that is they must be able to compute "interim" results, be able to unwind or reverse value additions, and have a separate destroy function) ... and that if the definition does not include the new methods defined by the windowing API they cannot be used with the Windowing API .. is this a correct interpretation of the code? That's right. Any function created with sqlite3_create_window_function() can also be used as regular aggregate function. If you pass NULL for both xValue and xInverse, it can only be used as an aggregate function - just like aggregates created with the existing sqlite3_create_function*() APIs. To create a function that can be used as a window function all 4 callbacks - xStep, xFinal, xValue and xInverse - passed to sqlite3_create_window_function() must be non-NULL. The xDestroy() callback is like the one to sqlite3_create_function_v2(). It's invoked either when the database handle is closed or when the function is overridden. If you need to delete whatever the (void*) pointer passed as the 5th argument to sqlite3_create_window_function() points to. Dan. On another note, what a really nice addition! Now to figure out how to "unwind" value additions to a bunch of running computations so they can be used in the windowing API ... --- 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
Re: [sqlite] Typo in window function docs
On 07/02/2018 03:14 PM, Domingo Alvarez Duarte wrote: Hello ! This sql example on the new window function docs is missing the parenthesis on the "rank" function: Thanks for pointing this out. New version uploaded now. Dan. === SELECT x, y, row_number() OVER*win1*, rank OVER*win2* FROM t0 --< here the rank function is missing "()" WINDOW*win1* AS (ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), *win2* AS (PARTITION BY y ORDER BY x) ORDER BY x; === Cheers ! ___ 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] Segfault in window function
On 07/02/2018 10:54 PM, Charles Leifer wrote: I'm getting a segfault with the following query, which uses window functions: Thanks for reporting this. Should now be fixed here: https://www.sqlite.org/src/info/693b4350d7413912 Thanks also for the sum() bug report in the follow-up. That one is fixed here: https://www.sqlite.org/src/info/693b4350d7413912 Dan. SELECT "t1"."counter", "t1"."value", RANK() OVER w AS "rank" FROM "sample" AS "t1" WINDOW w AS (PARTITION BY "t1"."counter" ORDER BY "t1"."value" DESC) ORDER BY "t1"."counter", RANK() OVER w The sql used to create the "sample" table: CREATE TABLE IF NOT EXISTS "sample" ( "id" INTEGER NOT NULL PRIMARY KEY, "counter" INTEGER NOT NULL, "value" REAL NOT NULL); Test data: INSERT INTO "sample" (counter, value) VALUES (1, 10.), (1, 20.), (2, 1.), (2, 3.), (3, 100.); The expected output for the window query is: 1, 20., 1 1, 10., 2 2, 3., 1 2, 1., 2 3, 100., 1 ___ 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] Time Precision
> Igor wrote:
> select (julianday('now') - julianday('1970-01-01'))*24*60*60*1000
> Keith wrote:
> select (julianday() - 2440587.5) * 86400.0
Both of these got me on my way, Igor's a little more clearer. I'll
doing a little more checking to insure the solution below is correct,
but seems good. Thanks.
danap.
Solution:
SELECT CAST(
(SELECT (julianday('now', 'localtime') -
julianday('1970-01-01'))*24*60*60*1000)
AS INTEGER);
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Window Function Crash -- overriding builtin aggregate
On 07/02/2018 01:40 PM, Keith Medcalf wrote: I have overridden the builtin AVG function with a function of my own that computes the average by using the "running average" method rather than the simple sum/count method. This function is registered as an old fashioned aggregate function. After the window extension is put in place, it appears that somewhere the wires are getting crossed as it appears to call my aggregate step function is getting called, however, when it tries to call the xValue function, SQLite3 crashes. (the call is apparently to "my" override xValue function, which is, of course, null -- not to the original builtin avg xValue pointer which would not work either but which would not result in a crash). Thanks for reporting this. The query causing the crash is a window-function query (i.e. "avg(...) OVER ...", correct? Assuming so, should now be fixed here: https://www.sqlite.org/src/info/4f3c8a82fd1c5b14 Dan. If I register "all" the methods (even though they do not work correctly with a running calculation) the internal function is fully overridden. Even if I register the override function using the new sqlite_create_window_function and explicitly set the xValue and xInverse to 0 (null), the program still crashes when attempting to run the non-existent xValue function ... --- 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
Re: [sqlite] insert or replace performance with self "references" column
On 2 Jul 2018, at 2:45pm, Allen wrote: > My C program was creating a "Parent_Index" on two different tables, so > the second Parent_Index on the Transactions table was not being > created, and this was causing the "replace" to do a table scan. I > solved the problem by making the index names globally unique. Easy mistake to make. Glad you figured it out. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Segfault in window function
Furthermore, I've found another example of a window function returning
incorrect results.
Using the above "sample" table and sample data, the following query
produces the wrong results.
Query:
SELECT "counter", "value", SUM("value") OVER (ORDER BY "id" ROWS 2
PRECEDING)
FROM "sample"
ORDER BY "id"
Expected results:
1, 10., 10.
1, 20., 30.
2, 1., 31.
2, 3., 24.
3, 100., 104.
SQLite is reporting the following results:
1, 10., 10.
1, 20., 30.
2, 1., 31.
2, 3., 44.
3, 100., 164.
I hope that helps diagnose the issue.
On Mon, Jul 2, 2018 at 10:55 AM, Charles Leifer wrote:
> Oh, and gdb is reporting the segfault occurs in "dupedExprSize".
>
> Partial traceback:
>
> #0 0x74d4645b in dupedExprSize () from
> /usr/local/lib/libsqlite3.so.0
> No symbol table info available.
> #1 0x74d5bf12 in exprDup () from /usr/local/lib/libsqlite3.so.0
> No symbol table info available.
> #2 0x74d5c091 in sqlite3ExprListDup () from
> /usr/local/lib/libsqlite3.so.0
> No symbol table info available.
> #3 0x74dbfa4f in sqlite3Select () from
> /usr/local/lib/libsqlite3.so.0
> No symbol table info available.
> #4 0x74df082b in sqlite3RunParser () from
> /usr/local/lib/libsqlite3.so.0
> No symbol table info available.
>
>
> On Mon, Jul 2, 2018 at 10:54 AM, Charles Leifer
> wrote:
>
>> I'm getting a segfault with the following query, which uses window
>> functions:
>>
>> SELECT "t1"."counter", "t1"."value", RANK() OVER w AS "rank"
>> FROM "sample" AS "t1"
>> WINDOW w AS (PARTITION BY "t1"."counter" ORDER BY "t1"."value" DESC)
>> ORDER BY "t1"."counter", RANK() OVER w
>>
>> The sql used to create the "sample" table:
>>
>> CREATE TABLE IF NOT EXISTS "sample" (
>> "id" INTEGER NOT NULL PRIMARY KEY,
>> "counter" INTEGER NOT NULL,
>> "value" REAL NOT NULL);
>>
>> Test data:
>>
>> INSERT INTO "sample" (counter, value) VALUES (1, 10.), (1, 20.), (2, 1.),
>> (2, 3.), (3, 100.);
>>
>> The expected output for the window query is:
>>
>> 1, 20., 1
>> 1, 10., 2
>> 2, 3., 1
>> 2, 1., 2
>> 3, 100., 1
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Segfault in window function
Oh, and gdb is reporting the segfault occurs in "dupedExprSize". Partial traceback: #0 0x74d4645b in dupedExprSize () from /usr/local/lib/libsqlite3.so.0 No symbol table info available. #1 0x74d5bf12 in exprDup () from /usr/local/lib/libsqlite3.so.0 No symbol table info available. #2 0x74d5c091 in sqlite3ExprListDup () from /usr/local/lib/libsqlite3.so.0 No symbol table info available. #3 0x74dbfa4f in sqlite3Select () from /usr/local/lib/libsqlite3.so.0 No symbol table info available. #4 0x74df082b in sqlite3RunParser () from /usr/local/lib/libsqlite3.so.0 No symbol table info available. On Mon, Jul 2, 2018 at 10:54 AM, Charles Leifer wrote: > I'm getting a segfault with the following query, which uses window > functions: > > SELECT "t1"."counter", "t1"."value", RANK() OVER w AS "rank" > FROM "sample" AS "t1" > WINDOW w AS (PARTITION BY "t1"."counter" ORDER BY "t1"."value" DESC) > ORDER BY "t1"."counter", RANK() OVER w > > The sql used to create the "sample" table: > > CREATE TABLE IF NOT EXISTS "sample" ( > "id" INTEGER NOT NULL PRIMARY KEY, > "counter" INTEGER NOT NULL, > "value" REAL NOT NULL); > > Test data: > > INSERT INTO "sample" (counter, value) VALUES (1, 10.), (1, 20.), (2, 1.), > (2, 3.), (3, 100.); > > The expected output for the window query is: > > 1, 20., 1 > 1, 10., 2 > 2, 3., 1 > 2, 1., 2 > 3, 100., 1 > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Segfault in window function
I'm getting a segfault with the following query, which uses window functions: SELECT "t1"."counter", "t1"."value", RANK() OVER w AS "rank" FROM "sample" AS "t1" WINDOW w AS (PARTITION BY "t1"."counter" ORDER BY "t1"."value" DESC) ORDER BY "t1"."counter", RANK() OVER w The sql used to create the "sample" table: CREATE TABLE IF NOT EXISTS "sample" ( "id" INTEGER NOT NULL PRIMARY KEY, "counter" INTEGER NOT NULL, "value" REAL NOT NULL); Test data: INSERT INTO "sample" (counter, value) VALUES (1, 10.), (1, 20.), (2, 1.), (2, 3.), (3, 100.); The expected output for the window query is: 1, 20., 1 1, 10., 2 2, 3., 1 2, 1., 2 3, 100., 1 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] two threads block eachother opening db (WAL)
I have found that when my process has a lot of threads each of which opens a DIFFERENT database, they each block on eachother while opening each database. Here is a little rust program to demonstrate the problem: If you simply remove the line "PRAGMA journal_mode = WAL;", then the program runs more than 5x faster. This is at least on conflict with the documentation, as the documentation suggests that a thread is a process for the purposes of sqlite's concurrency, at least when OPEN_NO_MUTEX is specified. I also feel like it's a bug in general, as opening two unrelated databases should not block eachother. Each thread blocks at this point: #0 __lll_lock_wait () at ../sysdeps/unix/sysv/linux/x86_64/lowlevellock.S:135 #1 0x774e4b95 in __GI___pthread_mutex_lock (mutex=0x77dd8148 ) at ../nptl/pthread_mutex_lock.c:80 #2 0x77b26419 in unixEnterMutex () at sqlite3.c:31952 #3 unixLock (id=0x74a25180, eFileLock=1) at sqlite3.c:32894 #4 0x77b1f2fa in sqlite3OsLock (lockType=1, id=) at sqlite3.c:21299 #5 pagerLockDb (pPager=pPager@entry=0x74a25008, eLock=eLock@entry=1) at sqlite3.c:50293 #6 0x77b1f34b in pagerLockDb (eLock=1, pPager=0x74a25008) at sqlite3.c:53054 #7 pager_wait_on_lock (pPager=pPager@entry=0x74a25008, locktype=locktype@entry=1) at sqlite3.c:53051 #8 0x77b55d6c in sqlite3PagerSharedLock (pPager=0x74a25008) at sqlite3.c:54293 #9 0x77b56835 in lockBtree (pBt=0x74a151e8) at sqlite3.c:64591 #10 sqlite3BtreeBeginTrans (p=0x74a1b508, wrflag=wrflag@entry=0) at sqlite3.c:64956 #11 0x77b82997 in sqlite3InitOne (db=0x74a12008, iDb=iDb@entry=0, pzErrMsg=pzErrMsg@entry=0x751fe778) at sqlite3.c:119558 #12 0x77b82aca in sqlite3Init (db=0x74a12008, pzErrMsg=pzErrMsg@entry=0x751fe778) at sqlite3.c:119740 #13 0x77b82b00 in sqlite3ReadSchema (pParse=pParse@entry=0x751fe770) at sqlite3.c:119765 #14 0x77b8d8e4 in sqlite3Pragma (pParse=0x751fe770, pId1=pId1@entry=0x751fddd0, pId2=pId2@entry=0x751fdde8, pValue=pValue@entry=0x751fde18, minusFlag=minusFlag@entry=0) at sqlite3.c:117300 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert or replace performance with self "references" column
Thank you for your assistance. I got the same results as you using the shell, and was only getting the problem in my C program. My C program was creating a "Parent_Index" on two different tables, so the second Parent_Index on the Transactions table was not being created, and this was causing the "replace" to do a table scan. I solved the problem by making the index names globally unique. Thanks again. > Date: Thu, 28 Jun 2018 18:09:09 +0100 > From: Simon Slavin > To: SQLite mailing list > Subject: Re: [sqlite] insert or replace performance with self > "references" column > Message-ID: > Content-Type: text/plain; charset=us-ascii > > On 27 Jun 2018, at 11:20pm, Allen wrote: > >> I have a table with an additional index and a query: >> >> "create table Transactions (Id integer primary key not null, Parent >> references Transactions(id), Body varchar);" >> >> "create index Parent_Index on Transactions (Parent);" >> >> EXPLAIN QUERY PLAN insert or replace into Transactions (Id, Parent, >> Body) values (?1, ?2, ?3); >> 23 0 0 SCAN TABLE Transactions >> 43 0 0 SCAN TABLE Transactions > > SQLite version 3.22.0 2017-12-05 15:00:17 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> PRAGMA foreign_keys = ON; > sqlite> CREATE TABLE Transactions (id INTEGER PRIMARY KEY, >...> parent REFERENCES Transactions(id), >...> Body TEXT); > sqlite> create index Parent_Index on Transactions (Parent); > sqlite> EXPLAIN QUERY PLAN insert into Transactions (Id, Parent, Body) values > (3, 4, 'Hello'); > sqlite> EXPLAIN QUERY PLAN insert OR REPLACE into Transactions (Id, Parent, > Body) values (3, 4, 'Hello'); > 0|0|0|SCAN TABLE Transactions USING COVERING INDEX Parent_Index > 0|0|0|SCAN TABLE Transactions USING COVERING INDEX Parent_Index > > If you replace "not null' from your CREATE TABLE command, do you get the > result I do ? > What, specifically, INSERT OR REPLACE command did you use ? > Which version of the command-line shell tool did you use ? > > Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Window Function Crash -- overriding builtin aggregate
On 2 Jul 2018, at 11:48am, Keith Medcalf wrote: > If I rename the function then both the builtin avg and my new function work > correctly. Thanks. Worth testing just in case something weird happened. But it didn't. I leave it up to the experts. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Window Function Crash -- overriding builtin aggregate
If I rename the function then both the builtin avg and my new function work correctly. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin >Sent: Monday, 2 July, 2018 04:27 >To: SQLite mailing list >Subject: Re: [sqlite] Window Function Crash -- overriding builtin >aggregate > >On 2 Jul 2018, at 7:40am, Keith Medcalf wrote: > >> Even if I register the override function using the new >sqlite_create_window_function and explicitly set the xValue and >xInverse to 0 (null), the program still crashes when attempting to >run the non-existent xValue function ... > >As a test, and not a final resolution of your problem, what happens >if you register your function with a new name, rather than using >'AVG' ? Does it work or do you still get mystery calls to xValue ? > >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] Window Function Crash -- overriding builtin aggregate
On 2 Jul 2018, at 7:40am, Keith Medcalf wrote: > Even if I register the override function using the new > sqlite_create_window_function and explicitly set the xValue and xInverse to 0 > (null), the program still crashes when attempting to run the non-existent > xValue function ... As a test, and not a final resolution of your problem, what happens if you register your function with a new name, rather than using 'AVG' ? Does it work or do you still get mystery calls to xValue ? Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS4 content table
Wording looks great, thanks Dan. FWIW I believe the same docs issue applies to the FTS5 module. Docs are hard, I often find myself re-reading paragraphs and then figuring out that I'm just a big dummy and didn't 'get it' the first time. The FTS docs are actually pretty great, they introduce some new concepts that are unique to that module and so those nonconanical patterns cause people to make their own assumptions regarding how they are implemented, which was the case here. Thanks again. - Peter On Mon, 2 Jul 2018, 10:02 AM Dan Kennedy, wrote: > On 07/01/2018 05:20 PM, Peter Johnson wrote: > > I recall you must still enumerate the column names of the fields you wish > > to link to the contentless table when creating the virtual table. > > > > If I remember correctly, failing to do so will result in 'no such column' > > errors. > > > > Last time I looked at the docs I got caught with the same gotcha, the > docs > > show an example query of how the contentless content is loaded but > > (possibly?) don't make it 100% clear whether you need to specify those > > columns in the vtable definition in order to allow them to be returned in > > the result rows. > > Fair enough. Added a sentence here to try to make things clearer: > >https://sqlite.org/docsrc/info/ea0f0b4c4af09bf9 > > Dan. > > > > > > > -P > > > > On Sun, 1 Jul 2018, 12:07 PM Dan Kennedy, wrote: > > > >> On 06/30/2018 10:24 PM, Dudu Markovitz wrote: > >>> Hi > >>> > >>> according to the documentation - > >>> 6.2.2. External Content FTS4 Tables > >>> > >>> An "external content" FTS4 table is similar to a contentless table, > >> except > >>> that if evaluation of a query requires the value of a column other than > >>> docid, FTS4 attempts to retrieve that value from a table (or view, or > >>> virtual table) nominated by the user (hereafter referred to as the > >> "content > >>> table"). > >>> > >>> However I see no example for a query on the fts table that implicitly > >>> retrieves values from the content table, nor could I find any in other > >>> place. > >>> All my attempts to write such a query ended up with "Error: no such > >>> column". > >>> Am I missing something or is it a problem in the documentation? > >> I don't think I understand the question. What do you want it to do? > >> > >> The SELECT query in the second block of code in that section: > >> > >> https://sqlite.org/fts3.html#_external_content_fts4_tables_ > >> > >> is: > >> > >> SELECT * FROM t3 WHERE t3 MATCH 'k' > >> > >> t3 is the FTS4 table. The query does a lookup on the FTS4 index to find > >> matches for token 'k', then automatically retrieves values from the > >> content table (t2) for each matched row. The first instance of the > >> SELECT in the block of code shows everything working as expected, the > >> subsequent two show the counter-intuitive effects of allowing the FTS > >> index to get out of sync with the content table. > >> > >> Dan. > >> > >> > >> ___ > >> 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 > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Typo in window function docs
Hello ! This sql example on the new window function docs is missing the parenthesis on the "rank" function: === SELECT x, y, row_number() OVER*win1*, rank OVER*win2* FROM t0 --< here the rank function is missing "()" WINDOW*win1* AS (ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), *win2* AS (PARTITION BY y ORDER BY x) ORDER BY x; === Cheers ! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS4 content table
On 07/01/2018 05:20 PM, Peter Johnson wrote: I recall you must still enumerate the column names of the fields you wish to link to the contentless table when creating the virtual table. If I remember correctly, failing to do so will result in 'no such column' errors. Last time I looked at the docs I got caught with the same gotcha, the docs show an example query of how the contentless content is loaded but (possibly?) don't make it 100% clear whether you need to specify those columns in the vtable definition in order to allow them to be returned in the result rows. Fair enough. Added a sentence here to try to make things clearer: https://sqlite.org/docsrc/info/ea0f0b4c4af09bf9 Dan. -P On Sun, 1 Jul 2018, 12:07 PM Dan Kennedy, wrote: On 06/30/2018 10:24 PM, Dudu Markovitz wrote: Hi according to the documentation - 6.2.2. External Content FTS4 Tables An "external content" FTS4 table is similar to a contentless table, except that if evaluation of a query requires the value of a column other than docid, FTS4 attempts to retrieve that value from a table (or view, or virtual table) nominated by the user (hereafter referred to as the "content table"). However I see no example for a query on the fts table that implicitly retrieves values from the content table, nor could I find any in other place. All my attempts to write such a query ended up with "Error: no such column". Am I missing something or is it a problem in the documentation? I don't think I understand the question. What do you want it to do? The SELECT query in the second block of code in that section: https://sqlite.org/fts3.html#_external_content_fts4_tables_ is: SELECT * FROM t3 WHERE t3 MATCH 'k' t3 is the FTS4 table. The query does a lookup on the FTS4 index to find matches for token 'k', then automatically retrieves values from the content table (t2) for each matched row. The first instance of the SELECT in the block of code shows everything working as expected, the subsequent two show the counter-intuitive effects of allowing the FTS index to get out of sync with the content table. Dan. ___ 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
[sqlite] Window Function Crash -- overriding builtin aggregate
I have overridden the builtin AVG function with a function of my own that computes the average by using the "running average" method rather than the simple sum/count method. This function is registered as an old fashioned aggregate function. After the window extension is put in place, it appears that somewhere the wires are getting crossed as it appears to call my aggregate step function is getting called, however, when it tries to call the xValue function, SQLite3 crashes. (the call is apparently to "my" override xValue function, which is, of course, null -- not to the original builtin avg xValue pointer which would not work either but which would not result in a crash). If I register "all" the methods (even though they do not work correctly with a running calculation) the internal function is fully overridden. Even if I register the override function using the new sqlite_create_window_function and explicitly set the xValue and xInverse to 0 (null), the program still crashes when attempting to run the non-existent xValue function ... --- 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
