Hi,
> What problem do you think you can solve with this?
I have one readonly connection. Is being used for reading.
From time to time I need to modify data using WAL approach. I don't want to
bother clients of the first connection with reopening it twice (DELETE->WAL
before modification and WAL->DELETE after). I am trying to do that from
separate read-write connection. And problem which I cannot solve is that I am
not able to switch WAL->DELETE in second (readwrite connection) after data
modification. So:
1)maybe there is something like purge or sync or whatever just to let first
connection to unlock databases completely for a while
2)maybe I could use the same readonly connection but is that possible to switch
connection like READONLY->READWRITE and than READWRITE->READONLY? I mean to
force sqlite to reopen underlying file socket with readwrite permissions and
than again with readonly?
Regards,
Andrii
________________________________
From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of
sqlite-users-requ...@mailinglists.sqlite.org
<sqlite-users-requ...@mailinglists.sqlite.org>
Sent: Monday, August 8, 2016 2:00 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: sqlite-users Digest, Vol 104, Issue 8
Send sqlite-users mailing list submissions to
sqlite-users@mailinglists.sqlite.org
To subscribe or unsubscribe via the World Wide Web, visit
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
or, via email, send a message with subject or body 'help' to
sqlite-users-requ...@mailinglists.sqlite.org
You can reach the person managing the list at
sqlite-users-ow...@mailinglists.sqlite.org
When replying, please edit your Subject line so it is more specific
than "Re: Contents of sqlite-users digest..."
Today's Topics:
1. Re: switching from WAL to DELETE mode (Clemens Ladisch)
2. Re: Locking databases - Possibly (probably?) a dumb question
(Rob Willett)
3. Re: Locking databases - Possibly (probably?) a dumb question
(Rob Willett)
4. Re: newbie has waited days for a DB build to complete. what's
up with this. (Kevin O'Gorman)
5. Re: Locking databases - Possibly (probably?) a dumb question
(Jean-Christophe Deschamps)
6. Re: Bug in CREATE INDEX (Kevin O'Gorman)
7. Re: Bug in CREATE INDEX (Olivier Mascia)
8. Re: Bug in CREATE INDEX (Dominique Pellé)
9. Re: Bug in CREATE INDEX (Kevin O'Gorman)
10. Re: Bug in CREATE INDEX (Dan Kennedy)
11. SQLite 3.12 refuses to load fts3 tokenizer in Tcl and Perl
DBD::SQLite (or missing api for scripting case)
(hkoba {Kobayasi Hiroaki})
12. Re: SQLite 3.12 refuses to load fts3 tokenizer in Tcl and
Perl DBD::SQLite (or missing api for scripting case)
(Kenichi Ishigaki)
13. Re: Bug in CREATE INDEX (Stephan Mueller)
14. Re: Bug in CREATE INDEX (Philip Newton)
----------------------------------------------------------------------
Message: 1
Date: Sun, 7 Aug 2016 15:20:21 +0200
From: Clemens Ladisch <clem...@ladisch.de>
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] switching from WAL to DELETE mode
Message-ID: <3ac6c8eb-d10d-7bca-b33c-9e37e81da...@ladisch.de>
Content-Type: text/plain; charset=utf-8
Andrii Motsok wrote:
> Is that possible to switch database from WAL to DELETE mode when holding more
> than one connection?
No. There is no mechanism to tell the other connections about the change.
> If no, which workaround could we use
Don't do it. What problem do you think you can solve with this?
Regards,
Clemens
------------------------------
Message: 2
Date: Sun, 07 Aug 2016 17:20:57 +0100
From: "Rob Willett" <rob.sql...@robertwillett.com>
To: "SQLite mailing list" <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Locking databases - Possibly (probably?) a dumb
question
Message-ID: <27ecdfb8-b65c-46b6-9dcf-af789aaa5...@robertwillett.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Ryan,
Thanks for the update.
We have done a few more tests during the day and not had any issues to
date. This is still on a test version but we are getting a warm, cuddly
feeling about using WAL mode.
The -wal file grows as you describe and you have explained it very well.
We were groping in the twilight to get to where we wanted to go, your
explanation brought a bright beacon of light onto the proceedings. (I
have been watching too many speeches from the various US political
conventions in the US though I am British).
We will investigate changing the page size. We would need to work out
the row size.
I will note in future your OCD and ensure that I am accurate in
reporting numbers rather than have self inflicted rounding errors, 60x
is a nicer number than 50x as it maps to mins and secs more easily :)
Thanks again for the help.
Rob
On 7 Aug 2016, at 12:11, R Smith wrote:
> On 2016/08/07 8:55 AM, Rob Willett wrote:
>> Richard, Ryan,
>>
>> Thanks for this. We were dimly aware of WAL but until now hadn’t
>> needed to use it.
>>
>> We’ve done a quick check with it and it *seems* to work on a test
>> database. We’ve all read the docs again and paid attention to
>> https://www.sqlite.org/wal.html#bigwal
>>
>> To test if it works we started our long running analytics query, on
>> our test machine it takes around 8 mins. We then speed up the rate of
>> updating our database with external data. In the real world an update
>> comes along every 3-5 mins, in our test system we queue them up so we
>> have them every 6-10 secs so they are around 60x quicker. The updates
>> are real data around 3-5MB in size.
>>
>> We monitored the -wal and the .shm files created as we throw data in
>> the database.
>>
>> The .wal file gets larger and larger until it hits 224MB and then
>> stays constant, the .shm file is only 1.8MB and seems to stay at that
>> size. We can also see that the main sqlite database is NOT updated
>> (or at least the timestamp isn’t) whilst we are running the updates
>> in WAL mode. This appears to be correct as the updates would be in
>> the -wal file.
>
> I'm truncating this post for brevity - but basically your concern
> about the size (voiced later in the post) is not a concern. What
> happens is the stated 4MB is simply 1000 pages x 4KB default page size
> - your page size might be a lot bigger (and should be set higher
> looking at your DB size and data entry sizes - I think it is "nicer"
> if, at a minimum, a complete row can fit on a page). Further, the WAL
> for your version of SQLite will grow with copies of data and multiple
> inserts in it because of the long-running query not allowing push-back
> check points for the time - and looking at your insert frequency and
> size, your WAL size seems pretty normal. (If you manage it wrong, it
> will fill up Terrabytes - this is the situation you want to avoid, but
> I think you've got it sorted).
>
> The Documentation simply describes the normal situation, which yours
> isn't.
>
> Also, on a point of satisfying my OCD... going on your quoted averages
> - 5 minutes vs. 10 secs is a 30:1 ratio (5 mins = 300 seconds vs. 10
> secs) so the speed is only 30x faster, not 60) - And before anyone
> asks, yes the low end of the ranges given is 3mins (180s) vs. 6 secs
> which is also a 30:1 ratio. Even if I take the opposite range
> extremes (5 mins vs. 6s) I still only get 50x speedup. LoL - Sorry,
> I'll shush now :)
>
> As an aside, I think Richard posted a small study of testing multiple
> DB ops with varying page sizes and varying hardware page sizes, and
> basically, IIRC, the Jury was out on best size in the general case
> with 8192 seeming to be a good standard and the idea that the page
> size should try match the underlying OS page size for best performance
> turned out to be a bit of a "sometimes maybe", but the point was made
> that every implementation should experiment to find the optimum size.
> That said, my memory cannot be trusted - could someone re-post that or
> point us to an on-line page somewhere? Thanks!
>
> Cheers,
> Ryan
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
------------------------------
Message: 3
Date: Sun, 07 Aug 2016 17:27:19 +0100
From: "Rob Willett" <rob.sql...@robertwillett.com>
To: "SQLite mailing list" <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Locking databases - Possibly (probably?) a dumb
question
Message-ID: <b70b1422-764e-4685-80ce-875928fab...@robertwillett.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Jean-Christophe
Thanks for the update on wal-mode. Your explanation is clear and makes
sense to us. We can see what we would have a 224MB -wal file, we
experimented with killing processes whilst updating and generally
messing around and SQLite did what is was supposed to do. I wouldn’t
say we were exhaustively testing it and to be honest, I know we can’t
push SQLite to its limits with the little things we use it for.
We did understand the differences in 3.11.10 and 3.8 re the size of the
-wal mode, its just that I communicated it poorly. Too little sleep and
far too much coffee.
We are going to do some more tests, more about familiarising ourselves
with WAL rather than expecting it to break to be honest. WAL seems to
work well enough for us and assuming our last conversion tests work OK,
we’ll shine it in tomorrow night when we get some downtime.
Thanks for you help and elegant description
Rob
On 7 Aug 2016, at 9:59, Jean-Christophe Deschamps wrote:
> Rob,
>
> At 08:55 07/08/2016, you wrote:
>> We think that using WAL mode works for us, indeed inspection seems to
>> indicate it does, but the size of the -wal file appears to be far
>> larger than would be expected. Is there a problem here? It doesn't
>> appear to be a problem but would welcome any comments.
>
> After reading your post I'd like to clear up a few points about WAL
> mode.
>
>> We can also see that the main sqlite database is NOT updated (or at
>> least the timestamp isn't) whilst we are running the updates in WAL
>> mode. This appears to be correct as the updates would be in the -wal
>> file.
>
> The WAL mode is persistant and consistant. That means that once
> successfully put in his mode the DB itself will remain in WAL mode for
> every (new) connection. Thus your updates and the long-running query
> are both running under WAL mode. That is, provided the WAL mode was
> set prior to the start of the long-running query, but that detail
> doesn't matter for reads in this case.
>
> It doesn't matter whether your query is a single query statement
> (hence in auto-commit mode) or a huge transaction extracting and
> massaging data in multiple temp tables and myriads of read/write
> statements, all inside an explicit transaction), ACID properties
> guarantee that once your query is started, it will see the DB in the
> state prior to any updates that could occur during its run. Else you
> would obtain potentially dangerously inconsistant data of course.
>
>> We have not set the journal_size_limit and we have a -wal file which
>> is 224MB in size, somewhat larger than 4MB. We are running
>>
>> 3.8.2 2013-12-06 14:53:30 27392118af4c38c5203a04b8013e1afdb1cebd0d
>>
>> which does not appear to have the code in 3.11.0 so that the WAL file
>> is proportional to the size of the transaction. From the same page of
>> the manual:
>>
>> ```
>> Very large write transactions.
>>
>> A checkpoint can only complete when no other transactions are
>> running, which
>> means the WAL file cannot be reset in the middle of a write
>> transaction. So a large change to a large database
>> might result in a large WAL file. The WAL file will be checkpointed
>> once the write transaction completes
>> (assuming there are no other readers blocking it) but in the
>> meantime, the file can grow very big.
>>
>> As of SQLite version 3.11.0, the WAL file for a single transaction
>> should be proportional in size to
>> the transaction itself. Pages that are changed by the transaction
>> should only be written into the WAL
>> file once. However, with older versions of SQLite, the same page
>> might be written into the WAL file multiple
>> times if the transaction grows larger than the page cache.
>> ```
>
> Reread the quoted part again: only with SQLite versions 3.11.0 and
> above will a given page be written only once in the WAL file when
> initiated within a given transaction.
>
> Since you're running a prior version, it's pretty logical to expect
> that your updates will cause writes of multiple distinct copies of the
> same pages in the WAL file. You should update your SQLite version to
> see a possible improvement there. That, or refer only to the old 3.8.2
> documentation, but this is an inferior option because there have been
> significant improvements meanwhile.
>
> Also the ratio in the help file (1000 pages or about 4Mb) applies to
> the default page size (4Kb).
>
> Finally, while the long-running query is running, no checkpoint can
> run to completion. Doc states under "Checkpoint starvation.":
>
>> However, if a database has many concurrent overlapping readers and
>> there is always at least one active reader, then no checkpoints will
>> be able to complete and hence the WAL file will grow without bound.
>
> Since you clearly can't introduce a read-gap inside your read query,
> the .wal file will grow as large as it needs until completion of the
> query. You mentionned that you tested with much more frequent updates
> than the real-world case (120x actually), so .wal file size shouldn't
> be an issue in your actual use case.
>
> HTH
>
> --
> Jean-Christophe
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
------------------------------
Message: 4
Date: Sun, 7 Aug 2016 09:40:09 -0700
From: "Kevin O'Gorman" <kevinogorm...@gmail.com>
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] newbie has waited days for a DB build to
complete. what's up with this.
Message-ID:
<CA+ho=O3MuM5Fp4QZdPR=m5suku42enfkl6tox+8qzh3k-to...@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
On Sat, Aug 6, 2016 at 2:49 PM, Kevin O'Gorman <kevinogorm...@gmail.com>
wrote:
> On Sat, Aug 6, 2016 at 2:09 AM, Dan Kennedy <danielk1...@gmail.com> wrote:
>
>> On 08/06/2016 09:52 AM, Kevin O'Gorman wrote:
>>
>>> On Fri, Aug 5, 2016 at 2:03 PM, Dan Kennedy <danielk1...@gmail.com>
>>> wrote:
>>>
>>> On 08/06/2016 03:28 AM, Kevin O'Gorman wrote:
>>>>
>>>> On Fri, Aug 5, 2016 at 1:08 PM, David Raymond <david.raym...@tomtom.com
>>>>> >
>>>>> wrote:
>>>>>
>>>>> ......
>>>>>
>>>>> Apart from the default location of the files, it reads like your next
>>>>> main
>>>>>
>>>>>> concern is how many temp files get opened up. My bet is that it'll be
>>>>>> a
>>>>>> very small number, just potentially huge in file size while it's doing
>>>>>> its
>>>>>> thing. But again, try that pragma and take a look.
>>>>>>
>>>>>> My best bet is the contrary: it starts with small files and makes
>>>>>>
>>>>> increasingly larger ones, like the sort utility does. The problem is
>>>>> that
>>>>> there are too many of them at the beginning for it to work with
>>>>> anonymous
>>>>> files (which sort does not use). This at least offers a possible
>>>>> explanation of its getting wedged on large indexes: an unexpected and
>>>>> untested error, handled poorly.
>>>>>
>>>>> You could verify this by checking the number of open handles in
>>>> "/proc/<pid>/fd" after your process is wedged.
>>>>
>>>> Excellent idea. I did not know about that possibility. And sure
>>>> enough,
>>>>
>>> I'm wrong. It's using anonymous files, all right, but only one or two
>>> at a
>>> time. I assume they're big. I'm in the process of bracketing where size
>>> begins to matter. So far, 1/10 of the data loads and indexes just fine,
>>> albeit somewhat more slowly that the smaller samples predicted. The
>>> database load took 6.5 minutes, the troublesome index 10 minutes. At
>>> smaller sizes, indexing is faster than the database load.
>>>
>>> I'm trying 1/3 now (500 million lines)
>>>
>>
>> What does [top] tell you once the process becomes wedged? What percentage
>> is the CPU running at? Or is it completely bogged down waiting for IO?
>>
>> Dan
>
>
> I'm waiting for a good time to get the answer to this. It takes a good
> long while to get to wedged, so I'll probably do it overnight tonight.
>
RETRACTION: it doesn't get wedged after all, it just takes about 4 times
longer than I expected. On small inputs, it makes the indexes faster than
the data table. When I test on the whole data file, it takes 4 times
longer to build each index than it took to make the table. I guess that's
what it looks like when building the table is O(n) complexity (linear), and
the index is O(n * log(n)) and log(n) is getting bigger.
Sorry for the kerfluffle. This is my first time working with datasets this
big. Since it takes almost 6 hours to build each index (there are 2 at
this point), and I didn't know to expect that, I drew the wrong
conclusion. My bad.
I'm just glad Mr. Hipp got into this thread and advised to build the
indexes after the data is loaded. It would have been _lots_ slower
inserting each index individually, and in fact my first attempt -- the one
that gave me the initial idea that this had wedged -- took this approach
and I decided it was wedged after a few _days_. I suspect it wasn't
either, but might as well have been.
So far, this has been a feasibility exercise. Building a 500 GB database
overnight is definitely feasible. Now I can go on to see if it helps me
solve my problem.
--
#define QUESTION ((bb) || (!bb)) /* Shakespeare */
------------------------------
Message: 5
Date: Sun, 07 Aug 2016 19:17:03 +0200
From: Jean-Christophe Deschamps <j...@antichoc.net>
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Locking databases - Possibly (probably?) a dumb
question
Message-ID:
<mailman.2.1470657601.30623.sqlite-us...@mailinglists.sqlite.org>
Content-Type: text/plain; charset="us-ascii"; format=flowed
Rob,
At 18:27 07/08/2016, you wrote:
>Too little sleep and far too much coffee.
I was in the same situation, multiplying by 2 instead of dividing, as
Ryan pointed out.
Nice to see that WAL fits your use case. I for one found it rock solid
and very useful.
--
Jean-Christophe
------------------------------
Message: 6
Date: Sun, 7 Aug 2016 11:24:58 -0700
From: "Kevin O'Gorman" <kevinogorm...@gmail.com>
To: sqlite-users <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Bug in CREATE INDEX
Message-ID:
<CA+ho=o3hzvstaqslfktwo0p0d4w6thfsornw9caq_kwanah...@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
RETRACTED: Not wedged, just somewhat slower than I expected. I was
expecting about an hour, and it takes 6. On reflection, it's okay. My
ideas about what's going on were wrong too -- anonymous files are used, but
only two of them at a time.
I still think it is using a poor choice of default directory for temporary
files. Is it /var/tmp instead of the more usual /tmp? That's the only
other choice that would not be just plain wrong, and maybe it was chosen
thinking that it's likely to be on a bigger partition. Maybe, sometimes.
But a lot of the time, /var/tmp is on the same partition as /tmp, or just
ignored because the sort utility defaults to /tmp and often that's the
biggest user of big temporary files. That's the reason that I've placed
/tmp on its own huge partition (3 TB) and left /var/tmp alone. The big
reason to use /var/tmp is that the files there are not deleted
automatically -- the standard says they're to be preserved -- but anonymous
files aren't ever preserved -- they've disappeared from the file system by
definition.
In all, not a big issue since $TMPDIR is honored, but I don't see that it's
documented. I just guessed it since the sort utility honors it and I
thought it was possible sort was being used under the covers. It's not,
but it all worked out okay.
Does anybody know where the actual defaults and controlling environment
variables are documented, by operating system? Or are they?
On Fri, Aug 5, 2016 at 12:36 PM, Kevin O'Gorman <kevinogorm...@gmail.com>
wrote:
> CREATE INDEX has two problems:
> 1) poor default location of temporary storage.
> 2) gets wedged on very large indexes.
>
> I'm using the sqlite that came with Xubuntu 14.04, I think it's version
> 3.8.2.
>
> I created a table, and used .import to populate it with records, about 1.4
> billion of them. The resulting database is 232 GB. All seemed well.
>
> I then went to CREATE INDEX on two fields, one CHAR(64) and the other an
> INT. This initially died quickly, but I determined that it was because it
> was attempting to use my smallest partition for workspace. It was in my
> root partition, but not in /tmp which is separate. I submit that /tmp
> would be the natural choice, as it is what the sort command uses by
> default. That's problem 1.
>
> Fortunately, it honors the $TMPDIR environment variable, but gets wedged
> after a bit. I have an idea why. While it was still actively adding
> space, there were no filenames showing, so I infer that it's using
> "anonymous" files, perhaps by calling tmpfile(). This could be bad news,
> as anonymous files have to be held open, but there are limits on how many
> open files any process is allowed. Unless your merge process starts with
> at least 1.4 million keys in each buffer, this is going to fail on my
> data. I suggest using tempnam() and closing the files when you're not
> actively using them.
>
> --
> #define QUESTION ((bb) || (!bb)) /* Shakespeare */
>
--
#define QUESTION ((bb) || (!bb)) /* Shakespeare */
------------------------------
Message: 7
Date: Sun, 7 Aug 2016 20:54:59 +0200
From: Olivier Mascia <o...@integral.be>
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Bug in CREATE INDEX
Message-ID: <77663eb6-1bb8-4a2e-b99d-d0e9fad13...@integral.be>
Content-Type: text/plain; charset=us-ascii
> Does anybody know where the actual defaults and controlling environment
> variables are documented, by operating system?
https://www.sqlite.org/tempfiles.html
--
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia (from mobile device), integral.be/om
------------------------------
Message: 8
Date: Sun, 7 Aug 2016 21:03:07 +0200
From: Dominique Pellé <dominique.pe...@gmail.com>
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Bug in CREATE INDEX
Message-ID:
<caon-t_htsevtyd+631wccmf2haxq+zeeaas2ae2rdhdvr0m...@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Kevin O'Gorman wrote:
> CREATE INDEX has two problems:
> 1) poor default location of temporary storage.
> 2) gets wedged on very large indexes.
>
> I'm using the sqlite that came with Xubuntu 14.04, I think it's version
> 3.8.2.
SQLite-3.8.2 is old (Dec 2013). It's better to download and
compile SQLite yourself.
There has been several optimizations since that release.
In particular, looking at release notes at
https://sqlite.org/changes.html the following
improvement which may be relevant for your issue:
=== BEGIN QUOTE https://sqlite.org/changes.html ===
3.8.10:
Performance improvements for ORDER BY, VACUUM,
CREATE INDEX, PRAGMA integrity_check, and
PRAGMA quick_check.
=== END QUOTE ===
Regards
Dominique
------------------------------
Message: 9
Date: Sun, 7 Aug 2016 13:37:55 -0700
From: "Kevin O'Gorman" <kevinogorm...@gmail.com>
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Bug in CREATE INDEX
Message-ID:
<CA+ho=O15Z3q0s24f=bc3um-cmcxacfttud3vessuqs7atis...@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
On Sun, Aug 7, 2016 at 12:03 PM, Dominique Pellé <dominique.pe...@gmail.com>
wrote:
> Kevin O'Gorman wrote:
>
> > CREATE INDEX has two problems:
> > 1) poor default location of temporary storage.
> > 2) gets wedged on very large indexes.
> >
> > I'm using the sqlite that came with Xubuntu 14.04, I think it's version
> > 3.8.2.
>
> SQLite-3.8.2 is old (Dec 2013). It's better to download and
> compile SQLite yourself.
>
> There has been several optimizations since that release.
> In particular, looking at release notes at
> https://sqlite.org/changes.html the following
> improvement which may be relevant for your issue:
>
> === BEGIN QUOTE https://sqlite.org/changes.html ===
> 3.8.10:
>
> Performance improvements for ORDER BY, VACUUM,
> CREATE INDEX, PRAGMA integrity_check, and
> PRAGMA quick_check.
> === END QUOTE ===
>
> Regards
> Dominique
>
>
I use the LTS (long-term support) version of Ubuntu, and like not having to
keep up with all the latest. My current 14.04 is at end-of-life, and I'll
upgrade to 16.04 soon -- probably this weekend -- and get sqlite 3.8.17
automatically.
I'm a hobbyist going solo with a lot of interests. This means I have to
accept the concept of "good enough" or pare down my interests to those I
can devote admin time to on a regular basis. Even more so when I haven't
really decided to adopt the package yet.
Works for me, but YMMV.
--
#define QUESTION ((bb) || (!bb)) /* Shakespeare */
------------------------------
Message: 10
Date: Mon, 08 Aug 2016 13:11:31 +0700
From: Dan Kennedy <danielk1...@gmail.com>
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Bug in CREATE INDEX
Message-ID: <57a82293.4020...@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
On 08/08/2016 02:03 AM, Dominique Pellé wrote:
> Kevin O'Gorman wrote:
>
>> CREATE INDEX has two problems:
>> 1) poor default location of temporary storage.
>> 2) gets wedged on very large indexes.
>>
>> I'm using the sqlite that came with Xubuntu 14.04, I think it's version
>> 3.8.2.
> SQLite-3.8.2 is old (Dec 2013). It's better to download and
> compile SQLite yourself.
>
> There has been several optimizations since that release.
> In particular, looking at release notes at
> https://sqlite.org/changes.html the following
> improvement which may be relevant for your issue:
>
> === BEGIN QUOTE https://sqlite.org/changes.html ===
> 3.8.10:
>
> Performance improvements for ORDER BY, VACUUM,
> CREATE INDEX, PRAGMA integrity_check, and
> PRAGMA quick_check.
> === END QUOTE ===
3.8.7 introduced the multi-threaded sorter too. So with a more recent
version of SQLite,
PRAGMA threads = 4
might help this case.
https://sqlite.org/pragma.html#pragma_threads
Dan.
------------------------------
Message: 11
Date: Mon, 8 Aug 2016 16:50:16 +0900
From: "hkoba {Kobayasi Hiroaki}" <buribul...@gmail.com>
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] SQLite 3.12 refuses to load fts3 tokenizer in Tcl
and Perl DBD::SQLite (or missing api for scripting case)
Message-ID:
<cabs8+krpnpehp8ezuh9p_hgzw-aeg6n0abjdadp07thm2cl...@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Hi!
Recently I upgraded loving SQLite to 3.12 (because Fedora24 uses it)
and I found some of my programs (written in Tcl or Perl)
stopped working because they can't load my own fts3 tokenizer extension.
I read https://www.sqlite.org/fts3.html#section_8_1 and realized I may
need to call sqlite3_db_config().
It is ok for C-level apps, but unfortunately, my programs are written
in Tcl or Perl.
# I tried authorizer too in Tcl, but it didn't help.
So,
(1) How about exposing sqlite3_db_config() to Tcl interface bindings?
(2) How about changing SQLITE_Fts3Tokenizer flag too in
sqlite3_enable_load_extension() when SQLITE_ENABLE_FTS3 is set?
# (1) is ideal, but there is no way to force this change to other
language bindings like DBD::SQLite.
# This is why I propose (2) too here.
I'm using sqlite-3.13.0-1.fc24.x86_64 on Fedora24.
Thank you!
--
hkoba
------------------------------
Message: 12
Date: Mon, 8 Aug 2016 17:07:57 +0900
From: Kenichi Ishigaki <kishig...@gmail.com>
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] SQLite 3.12 refuses to load fts3 tokenizer in
Tcl and Perl DBD::SQLite (or missing api for scripting case)
Message-ID:
<CADp=7tzvEyh5Gj3LA7GVtyPm=cpa7oomlh2klngmub4i53b...@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Hi,
2016-08-08 16:50 GMT+09:00 hkoba {Kobayasi Hiroaki} <buribul...@gmail.com>:
> Hi!
>
> Recently I upgraded loving SQLite to 3.12 (because Fedora24 uses it)
> and I found some of my programs (written in Tcl or Perl)
> stopped working because they can't load my own fts3 tokenizer extension.
>
> I read https://www.sqlite.org/fts3.html#section_8_1 and realized I may
> need to call sqlite3_db_config().
> It is ok for C-level apps, but unfortunately, my programs are written
> in Tcl or Perl.
>
> # I tried authorizer too in Tcl, but it didn't help.
>
> So,
>
> (1) How about exposing sqlite3_db_config() to Tcl interface bindings?
>
> (2) How about changing SQLITE_Fts3Tokenizer flag too in
> sqlite3_enable_load_extension() when SQLITE_ENABLE_FTS3 is set?
>
> # (1) is ideal, but there is no way to force this change to other
> language bindings like DBD::SQLite.
> # This is why I propose (2) too here.
>
As for the latest developer release of DBD::SQLite for perl (since
version 1.51_01 to be exact), you can set SQLITE_ENABLE_FTS3_TOKENIZER
environmental variable to true to enable fts3 tokenizer when you run
its Makefile.PL.
Cheers,
Kenichi Ishigaki, the maintainer of DBD::SQLite
>
> I'm using sqlite-3.13.0-1.fc24.x86_64 on Fedora24.
>
> Thank you!
> --
> hkoba
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
------------------------------
Message: 13
Date: Sun, 7 Aug 2016 18:35:54 +0000
From: Stephan Mueller <stephan.muel...@microsoft.com>
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Bug in CREATE INDEX
Message-ID:
<cy1pr0301mb16593bef25f03cfa66c9d2eef0...@cy1pr0301mb1659.namprd03.prod.outlook.com>
Content-Type: text/plain; charset="utf-8"
Kevin asks:
" Does anybody know where the actual defaults and controlling environment
" variables are documented, by operating system? Or are they?
I believe Section 5.0. near the end of https://www.sqlite.org/tempfiles.html
describes what you're looking for.
thanks,
stephan();
------------------------------
Message: 14
Date: Mon, 8 Aug 2016 11:41:55 +0200
From: Philip Newton <philip.new...@pobox.com>
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Bug in CREATE INDEX
Message-ID:
<ca+cwsm88qfrthgpuazeqww9lenta1d_tlbbyaexedrfko3m...@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
On 7 August 2016 at 22:37, Kevin O'Gorman <kevinogorm...@gmail.com> wrote:
> I use the LTS (long-term support) version of Ubuntu, and like not having to
> keep up with all the latest. My current 14.04 is at end-of-life
LTS are supported for 5 years; your 14.04 is good till April 2019.
Ph.
------------------------------
Subject: Digest Footer
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
------------------------------
End of sqlite-users Digest, Vol 104, Issue 8
********************************************
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
