Re: [sqlite] Different temp_store_directory settings okay?

[D. Richard Hipp]

On Mar 4, 2010, at 4:30 PM, Brian Dantes wrote:

> D. Richard Hipp wrote:
>
>> It is OK for different processes to use different  
>> temp_store_directory
>
>> settings.  The temp_store_directory is only used for TEMP tables.  It
>
>> does not play a roll in the persistent state of the database.
>
> I was worried because of this statement in the docs:
>
> "When the temp_store_directory setting is changed, all existing
> temporary tables, indices, triggers, and viewers are immediately
> deleted."
>
> If I have one application using the default temp_store_directory, say
> /tmp,
> and another that sets it explicitly to something else, does that  
> second
> application blow away all the temporary data for the first application
> sitting in /tmp?

No.

>
>>> I am experiencing physical DB corruption and am searching
>>> for possible explanations.
>> Is the corruption repeatable?  What version of SQLite are you  
>> running?
>
> No, unfortunately. 3.6.14.2.
>
> -Brian Dantes
>
>> -Original Message-
>> From: Brian Dantes
>> Sent: Thursday, March 04, 2010 1:10 PM
>> To: 'sqlite-users@sqlite.org'
>> Subject: Different temp_store_directory settings okay?
>>
>> Is it okay for two different *processes* using
>> independent connections to the same database two
>> have different values for the temp_store_directory
>> pragma?
>>
>> The docs make it clear this is not okay for two
>> *threads* in the same process -- but for two
>> processes is not so clear.
>>
>> I am experiencing physical DB corruption and am searching
>> for possible explanations.
>>
>> Brian Dantes
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Different temp_store_directory settings okay?

[Brian Dantes]

D. Richard Hipp wrote:

> It is OK for different processes to use different temp_store_directory

> settings.  The temp_store_directory is only used for TEMP tables.  It

> does not play a roll in the persistent state of the database.

I was worried because of this statement in the docs:

"When the temp_store_directory setting is changed, all existing
temporary tables, indices, triggers, and viewers are immediately
deleted."

If I have one application using the default temp_store_directory, say
/tmp,
and another that sets it explicitly to something else, does that second
application blow away all the temporary data for the first application
sitting in /tmp?

> > I am experiencing physical DB corruption and am searching
> > for possible explanations.
> Is the corruption repeatable?  What version of SQLite are you running?

No, unfortunately. 3.6.14.2.

-Brian Dantes

> -Original Message-
> From: Brian Dantes
> Sent: Thursday, March 04, 2010 1:10 PM
> To: 'sqlite-users@sqlite.org'
> Subject: Different temp_store_directory settings okay?
> 
> Is it okay for two different *processes* using
> independent connections to the same database two
> have different values for the temp_store_directory
> pragma?
> 
> The docs make it clear this is not okay for two
> *threads* in the same process -- but for two
> processes is not so clear.
> 
> I am experiencing physical DB corruption and am searching
> for possible explanations.
> 
> Brian Dantes
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Different temp_store_directory settings okay?

[D. Richard Hipp]

On Mar 4, 2010, at 4:10 PM, Brian Dantes wrote:

> Is it okay for two different *processes* using
> independent connections to the same database two
> have different values for the temp_store_directory
> pragma?
>
> The docs make it clear this is not okay for two
> *threads* in the same process -- but for two
> processes is not so clear.

It is OK for different processes to use different temp_store_directory  
settings.  The temp_store_directory is only used for TEMP tables.  It  
does not play a roll in the persistent state of the database.

>
> I am experiencing physical DB corruption and am searching
> for possible explanations.

Is the corruption repeatable?  What version of SQLite are you running?

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

D. Richard Hipp
d...@hwaci.com



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

[sqlite] Different temp_store_directory settings okay?

[Brian Dantes]

Is it okay for two different *processes* using
independent connections to the same database two
have different values for the temp_store_directory
pragma?

The docs make it clear this is not okay for two
*threads* in the same process -- but for two
processes is not so clear.

I am experiencing physical DB corruption and am searching
for possible explanations.

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

[sqlite] wrong output for a select group by to a sub query with a case expression

[Pedro Pedruzzi]

I'm using sqlite3-3.6.22, downloaded today from sqlite web site.

Steps to reproduce:

create table test(a real);
.import bugdata test

select categ, count(1) from (select *, (case when a=0 then 0 else 
cast((a + 25) / 50 + 0.5 as int) end) as categ from test) a group by categ;

1|10
1|25
3|26

The count is ok, but the categ is not supposed to duplicate.

This next very similar query works ok (with the provided data in 
particular the correct results happens to be the same).

select categ, count(1) from (select *, (case when 0=1 then 0 else 
cast((a + 25) / 50 + 0.5 as int) end) as categ from test) a group by categ;

1|10
2|25
3|26


Here is the bugdata:

120.0
35.5
95.0
41.0
51.5
64.5
140.0
64.5
108.5
138.5
138.5
94.5
130.5
119.5
148.5
75.5
94.0
144.0
78.5
86.0
112.0
132.0
51.0
42.0
44.0
48.0
12.0
35.5
35.5
75.5
77.5
130.5
103.0
110.5
53.5
86.5
122.0
146.0
129.0
91.5
141.0
76.5
66.5
35.5
126.0
90.0
96.0
134.0
63.0
106.5
77.5
35.5
64.0
121.0
119.5
126.0
58.0
123.0
133.0
77.0
56.0

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

Re: [sqlite] Fastest concurrent read-only performance (+ threads vs processes)

[Luke Evans]

It's Objective-C, but all the SQLite interfacing bits are pure C directly 
driving the SQLite API compiled into the program (3.6.22 amalgamation).

On 2010-03-04, at 11:25 AM, Pavel Ivanov wrote:

> What language is that? Probably wrapper for this language introduces
> its own mutexes.
> 

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

Re: [sqlite] Fastest concurrent read-only performance (+ threads vs processes)

[Pavel Ivanov]

What language is that? Probably wrapper for this language introduces
its own mutexes.


Pavel

On Thu, Mar 4, 2010 at 2:07 PM, Luke Evans  wrote:
> Thanks guys.
>
> Well, I had played with turning off the shared cache, and have just done so 
> again.
> What I'm actually seeing is serialisation (or at least some apparently quite 
> coarse-grained synchronisation).
>
> Here's some logging output on my Mac.  The start/finished messages are logged 
> by each thread, bracketing the query.
>
> 010-03-04 10:41:41.490 SQLiteTest[19641:5603] Starting query on thread 
> {name = (null), num = 6}
> 2010-03-04 10:41:41.490 SQLiteTest[19641:420b] Starting query on thread 
> {name = (null), num = 2}
> 2010-03-04 10:41:41.490 SQLiteTest[19641:5403] Starting query on thread 
> {name = (null), num = 4}
> 2010-03-04 10:41:41.490 SQLiteTest[19641:1b03] Starting query on thread 
> {name = (null), num = 3}
> 2010-03-04 10:41:41.490 SQLiteTest[19641:5503] Starting query on thread 
> {name = (null), num = 5}
> 2010-03-04 10:41:47.640 SQLiteTest[19641:5703] Starting query on thread 
> {name = (null), num = 7}
> 2010-03-04 10:41:52.524 SQLiteTest[19641:420b] Finished query on thread 
> {name = (null), num = 2} in 10.97s
> 2010-03-04 10:41:52.524 SQLiteTest[19641:5403] Finished query on thread 
> {name = (null), num = 4} in 10.97s
> 2010-03-04 10:41:52.532 SQLiteTest[19641:5503] Finished query on thread 
> {name = (null), num = 5} in 10.98s
> 2010-03-04 10:41:52.534 SQLiteTest[19641:5603] Finished query on thread 
> {name = (null), num = 6} in 10.98s
> 2010-03-04 10:41:52.534 SQLiteTest[19641:1b03] Finished query on thread 
> {name = (null), num = 3} in 10.98s
> 2010-03-04 10:41:53.088 SQLiteTest[19641:5703] Finished query on thread 
> {name = (null), num = 7} in 5.45s
> 2010-03-04 10:41:53.089 SQLiteTest[19641:a0f] Finished all queries in 11.60s
>
>
> With the shared cache turned on, these threads (which are all doing the exact 
> same query in this test) behave in the same 'serial' way, but the total time 
> is more like 4.5s.
>
> Is there any other config that could cause this kind of entanglement between 
> separate threads doing simple reads (albeit with aliases, sorting and 
> grouping), or is there anything in the database file that might induce this?
>
> Thanks again.
>
> -- Luke
>
>
>
>
>> On 2010-03-04, at 10:24 AM, Pavel Ivanov wrote:
>
>>> The process experiment seems to confirm that very fast independent parallel 
>>> queries can be made on the same database, but clearly I would like the same 
>>> behaviour using multiple threads (and probably connections) in the same 
>>> process.  Is this possible with some specific configuration of SQLite?
>>
>> Do not use shared cache if you want completely independent connections
>> in each thread. Otherwise each call to sqlite3_step() will block other
>> threads trying to call sqlite3_step() on the same database. Without
>> shared cache they will be completely independent and work as if they
>> were executed in different processes (including amount of I/O involved
>> and amount of memory consumed).
>
> On 2010-03-04, at 10:26 AM, Dan Kennedy wrote:
>
>>> The process experiment seems to confirm that very fast independent
>>> parallel queries can be made on the same database, but clearly I
>>> would like the same behaviour using multiple threads (and probably
>>> connections) in the same process.  Is this possible with some
>>> specific configuration of SQLite?
>>
>> Maybe, if you disable shared-cache. Of course then you will
>> use more memory, make more read() calls etc.
>>
>> Each shared-cache has associated with it a mutex. The mutex
>> is held for the duration of each sqlite3_step() call on a
>> statement handle that accesses that shared-cache.
>>
>> Dan.
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fastest concurrent read-only performance (+ threads vs processes)

[Luke Evans]

Thanks guys.

Well, I had played with turning off the shared cache, and have just done so 
again.
What I'm actually seeing is serialisation (or at least some apparently quite 
coarse-grained synchronisation).

Here's some logging output on my Mac.  The start/finished messages are logged 
by each thread, bracketing the query.

010-03-04 10:41:41.490 SQLiteTest[19641:5603] Starting query on thread 
{name = (null), num = 6}
2010-03-04 10:41:41.490 SQLiteTest[19641:420b] Starting query on thread 
{name = (null), num = 2}
2010-03-04 10:41:41.490 SQLiteTest[19641:5403] Starting query on thread 
{name = (null), num = 4}
2010-03-04 10:41:41.490 SQLiteTest[19641:1b03] Starting query on thread 
{name = (null), num = 3}
2010-03-04 10:41:41.490 SQLiteTest[19641:5503] Starting query on thread 
{name = (null), num = 5}
2010-03-04 10:41:47.640 SQLiteTest[19641:5703] Starting query on thread 
{name = (null), num = 7}
2010-03-04 10:41:52.524 SQLiteTest[19641:420b] Finished query on thread 
{name = (null), num = 2} in 10.97s
2010-03-04 10:41:52.524 SQLiteTest[19641:5403] Finished query on thread 
{name = (null), num = 4} in 10.97s
2010-03-04 10:41:52.532 SQLiteTest[19641:5503] Finished query on thread 
{name = (null), num = 5} in 10.98s
2010-03-04 10:41:52.534 SQLiteTest[19641:5603] Finished query on thread 
{name = (null), num = 6} in 10.98s
2010-03-04 10:41:52.534 SQLiteTest[19641:1b03] Finished query on thread 
{name = (null), num = 3} in 10.98s
2010-03-04 10:41:53.088 SQLiteTest[19641:5703] Finished query on thread 
{name = (null), num = 7} in 5.45s
2010-03-04 10:41:53.089 SQLiteTest[19641:a0f] Finished all queries in 11.60s


With the shared cache turned on, these threads (which are all doing the exact 
same query in this test) behave in the same 'serial' way, but the total time is 
more like 4.5s.

Is there any other config that could cause this kind of entanglement between 
separate threads doing simple reads (albeit with aliases, sorting and 
grouping), or is there anything in the database file that might induce this?

Thanks again.

-- Luke




> On 2010-03-04, at 10:24 AM, Pavel Ivanov wrote:

>> The process experiment seems to confirm that very fast independent parallel 
>> queries can be made on the same database, but clearly I would like the same 
>> behaviour using multiple threads (and probably connections) in the same 
>> process.  Is this possible with some specific configuration of SQLite?
> 
> Do not use shared cache if you want completely independent connections
> in each thread. Otherwise each call to sqlite3_step() will block other
> threads trying to call sqlite3_step() on the same database. Without
> shared cache they will be completely independent and work as if they
> were executed in different processes (including amount of I/O involved
> and amount of memory consumed).

On 2010-03-04, at 10:26 AM, Dan Kennedy wrote:

>> The process experiment seems to confirm that very fast independent  
>> parallel queries can be made on the same database, but clearly I  
>> would like the same behaviour using multiple threads (and probably  
>> connections) in the same process.  Is this possible with some  
>> specific configuration of SQLite?
> 
> Maybe, if you disable shared-cache. Of course then you will
> use more memory, make more read() calls etc.
> 
> Each shared-cache has associated with it a mutex. The mutex
> is held for the duration of each sqlite3_step() call on a
> statement handle that accesses that shared-cache.
> 
> Dan.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

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

Re: [sqlite] Fastest concurrent read-only performance (+ threads vs processes)

[Dan Kennedy]

On Mar 5, 2010, at 12:50 AM, Luke Evans wrote:

> Hi SQLiters,
>
> We're currently investigating SQLite in an application that needs to  
> issue a batch of queries (SELECTs) before doing some work with all  
> the data returned.
>
> I have been trying to figure out the fastest way to get the  
> results.  Given there are no writes involved, I figured there might  
> be some advantages in having the queries run on separate threads,  
> with a connection each, but with a shared cache and the read  
> uncommitted flag set ON.  This works fine, but the cumulative time  
> for all the queries is in the same ballpark as if the queries were  
> serialised, despite having SQLITE_CONFIG_MULTITHREAD set,  
> SQLLITE_CONFIG_MEMSTATUS off, with SQLITE_OPEN_SHAREDCACHE and  
> SQLITE_OPEN_NOMUTEX used on open.
>
> So, I'm not sure exactly how to configure (build and runtime) SQLite  
> so it allows database reads to be essentially independent and lock- 
> free.
>
> As an experiment, I wrote a simple program to do the same kind of  
> query and ran several of these in parallel as processes (against the  
> same database file).  In this case, I'm seeing very parallel  
> behaviour, and all queries complete in just over the time it would  
> normally take for a single one.
>
> The process experiment seems to confirm that very fast independent  
> parallel queries can be made on the same database, but clearly I  
> would like the same behaviour using multiple threads (and probably  
> connections) in the same process.  Is this possible with some  
> specific configuration of SQLite?

Maybe, if you disable shared-cache. Of course then you will
use more memory, make more read() calls etc.

Each shared-cache has associated with it a mutex. The mutex
is held for the duration of each sqlite3_step() call on a
statement handle that accesses that shared-cache.

Dan.

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

Re: [sqlite] Fastest concurrent read-only performance (+ threads vs processes)

[Pavel Ivanov]

> The process experiment seems to confirm that very fast independent parallel 
> queries can be made on the same database, but clearly I would like the same 
> behaviour using multiple threads (and probably connections) in the same 
> process.  Is this possible with some specific configuration of SQLite?

Do not use shared cache if you want completely independent connections
in each thread. Otherwise each call to sqlite3_step() will block other
threads trying to call sqlite3_step() on the same database. Without
shared cache they will be completely independent and work as if they
were executed in different processes (including amount of I/O involved
and amount of memory consumed).


Pavel

On Thu, Mar 4, 2010 at 12:50 PM, Luke Evans  wrote:
> Hi SQLiters,
>
> We're currently investigating SQLite in an application that needs to issue a 
> batch of queries (SELECTs) before doing some work with all the data returned.
>
> I have been trying to figure out the fastest way to get the results.  Given 
> there are no writes involved, I figured there might be some advantages in 
> having the queries run on separate threads, with a connection each, but with 
> a shared cache and the read uncommitted flag set ON.  This works fine, but 
> the cumulative time for all the queries is in the same ballpark as if the 
> queries were serialised, despite having SQLITE_CONFIG_MULTITHREAD set, 
> SQLLITE_CONFIG_MEMSTATUS off, with SQLITE_OPEN_SHAREDCACHE and 
> SQLITE_OPEN_NOMUTEX used on open.
>
> So, I'm not sure exactly how to configure (build and runtime) SQLite so it 
> allows database reads to be essentially independent and lock-free.
>
> As an experiment, I wrote a simple program to do the same kind of query and 
> ran several of these in parallel as processes (against the same database 
> file).  In this case, I'm seeing very parallel behaviour, and all queries 
> complete in just over the time it would normally take for a single one.
>
> The process experiment seems to confirm that very fast independent parallel 
> queries can be made on the same database, but clearly I would like the same 
> behaviour using multiple threads (and probably connections) in the same 
> process.  Is this possible with some specific configuration of SQLite?
>
> Cheers!
>
> -- Luke
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Fastest concurrent read-only performance (+ threads vs processes)

[Luke Evans]

Hi SQLiters,

We're currently investigating SQLite in an application that needs to issue a 
batch of queries (SELECTs) before doing some work with all the data returned.

I have been trying to figure out the fastest way to get the results.  Given 
there are no writes involved, I figured there might be some advantages in 
having the queries run on separate threads, with a connection each, but with a 
shared cache and the read uncommitted flag set ON.  This works fine, but the 
cumulative time for all the queries is in the same ballpark as if the queries 
were serialised, despite having SQLITE_CONFIG_MULTITHREAD set, 
SQLLITE_CONFIG_MEMSTATUS off, with SQLITE_OPEN_SHAREDCACHE and 
SQLITE_OPEN_NOMUTEX used on open.

So, I'm not sure exactly how to configure (build and runtime) SQLite so it 
allows database reads to be essentially independent and lock-free.

As an experiment, I wrote a simple program to do the same kind of query and ran 
several of these in parallel as processes (against the same database file).  In 
this case, I'm seeing very parallel behaviour, and all queries complete in just 
over the time it would normally take for a single one.

The process experiment seems to confirm that very fast independent parallel 
queries can be made on the same database, but clearly I would like the same 
behaviour using multiple threads (and probably connections) in the same 
process.  Is this possible with some specific configuration of SQLite?

Cheers!

-- Luke


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

Re: [sqlite] possible buffer over-read in sqlite3VXPrintf()

[Dan Kennedy]

>> Shouldn't escarg[] contain a nul-terminated string?
>
> Yes, I wondered that too when I looked at it again later. In which  
> case the code ought to be safe.

> But if it's true that escarg[] should always be a nul-terminated  
> string (are you confident of this? I haven't studied the code to  
> check where it comes from), then I'd agree that this appears to  
> point to a bug in the caller rather than in sqlite itself.

Not as confident as I was the other day. Now fixed here:

   http://www.sqlite.org/src/info/9abd6aa831

Thanks for tracking this down.

Dan.


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

[sqlite] AUTO: Bret Patterson/Austin/IBM is out of the office (returning 03/08/2010)

[Bret Patterson]

I am out of the office until 03/08/2010.

I'm out of the office but checking email once or twice a day and will
respond to any high importance issues as quickly as possible.


Note: This is an automated response to your message  "sqlite-users Digest,
Vol 27, Issue 4" sent on 3/4/10 5:00:02.

This is the only notification you will receive while this person is away.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] possible buffer over-read in sqlite3VXPrintf()

[Jonathan Kew]

Dan Kennedy  wrote:
> 
> On Mar 2, 2010, at 6:54 PM, Jonathan Kew wrote:
> 
> > I've run into what appears to be a small bug in this function (from  
> > sqlite3.c, v 3.6.22). Suggested patch:
[snip]
> >
> > The issue here is that when k reaches zero, the access to escarg[i]  
> > may try to look one byte beyond the end of the allocated buffer; to  
> > avoid this, simply reverse the order of the tests so that k is  
> > checked for non-zero first.
> >
> > The error is normally harmless, testing a "random" byte and then  
> > exiting the loop anyway because of the value of k, but it can cause  
> > a bus error in the (extremely rare) event that the buffer is  
> > allocated exactly at the end of a virtual memory page, and the  
> > following page is unallocated. (This was encountered when running  
> > under Guard Malloc.)
> 
> Shouldn't escarg[] contain a nul-terminated string?

Yes, I wondered that too when I looked at it again later. In which case the 
code ought to be safe.

> How did you provoke the error under Guard Malloc? Do you
> have a stack trace? I'm thinking the error might be caused
> by some bug in the caller.

This occurred during starting up a debug build of Firefox. No specific action 
appeared to provoke it, the application was loading its "history" db, etc. 
Unfortunately, I don't have a backtrace saved, and when I tried again 
yesterday, it did not recur. I did confirm at the time that the bus error was 
due to the attempt to read escarg[i], and that k had reached zero, which was 
why I jumped to the conclusion that it was an incorrectly handled edge case.

But if it's true that escarg[] should always be a nul-terminated string (are 
you confident of this? I haven't studied the code to check where it comes 
from), then I'd agree that this appears to point to a bug in the caller rather 
than in sqlite itself.

Thanks,

Jonathan


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

Re: [sqlite] Maximum database size?

[Collin Capano]

Hi Roger,

Thanks for the explanation.

Collin

On 3/3/10 11:11 PM, Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Collin Capano wrote:
>
>> Why does it prefer to use
>> /var/tmp or some other temp directory as opposed to just using the
>> current directory? Is there some performance advantage in doing this?
>>  
> The temp tables are for a particular connection only.  If the process dies
> unexpectedly then no other process would ever look for the temp tables.
> Consequently the temp files backing the temp tables really are temporary
> files and the OS appropriate locations are used by default.  (For example
> the OS may remove temp files not touched in the last 7 days.)
>
> By contrast the journal is looked for by other processes.  If a process is
> writing to the journal and dies unexpectedly then another SQLite based
> process will look for the journal and do a rollback as appropriate.
>
> As you saw you can control the temporary location at compile and run time,
> and also chance the behaviour between using files and using memory.
>
> ie SQLite has sensible defaults but you can still make things work however
> you want.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iEYEARECAAYFAkuPMukACgkQmOOfHg372QTTVgCgt5Cp3uk+mY/DaTgX+CycOwa2
> bt4An31hdkCLYeQG1b8Tp8L3Z8AK4/vQ
> =zBma
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

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

Re: [sqlite] game, set, match

[Jay A. Kreibich]

On Thu, Mar 04, 2010 at 06:37:02AM +, Simon Slavin scratched on the wall:
> 
> On 4 Mar 2010, at 5:39am, P Kishor wrote:
> 
> > My point was a bit different -- seems like
> > only SQLite offers the right mix of functional punch, agile
> > performance and lightweight footprint to be a viable technology for a
> > web database.
> 
> I don't think any of those characteristics are the ones which made 
> this happen.  I think that it's the facts that SQLite is Open Source
> and compiles in so many compilers.

  That, and the fact that it already exists in the code base of so
  many browsers.  Gears also set something of a precedence.

  Of course, the reason it exists in so many browsers is likely related
  to being "the right mix."

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS3 bug with MATCH plus OR

[Ralf Junker]

Thanks to both Scott and Dan for your answers!

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

Re: [sqlite] Type affinity changed by HAVING clause

[Pavel Ivanov]

I'd say this is definitely a bug. Especially keeping in mind that it's
not affinity that changed it's actual datatype of returning value.
It's interesting though that affinity for the value remains TEXT and
comparison is performed as for text:

sqlite> create table t1(a text, b int);
sqlite> insert into t1 values(123, 456);
sqlite> select typeof(a), a from t1;
text|123
sqlite> select typeof(a), a from t1 group by a;
text|123
sqlite> select typeof(a), a from t1 group by a having a=123;
text|123
sqlite> select typeof(a), a from t1 group by a having a='123';
text|123
sqlite> select typeof(a), a from t1 group by a having a select typeof(a), a, a<1222, a>1222, b<4555 from t1 group by a
having a wrote:
> Hi,
>
> I think I may have found a bug where affinities change through the HAVING
> expression.  For example, under v3.6.22, if I do...
>
> create table t1(a text, b int);
> insert into t1 values(123, 456);
> select typeof(a), a from t1 group by a having a
> then I get "integer|123" when I would expect "text|123"
>
> If, instead, I do...
>
> select typeof(a), a from t1 group by a having a=123;
> or...
> select typeof(a), a from t1 group by a having a='123';
>
> then I get "text|123" as expected (note: this was previously fixed under
> ticket #3493 at http://www.sqlite.org/cvstrac/tktview?tn=3493).
>
> Can anyone confirm that this is indeed a bug?  I've not provided the sql
> statement I'm trying to run, but instead created a set of statements to
> match the style used in tkt3493.test to better isolate the problem as I
> see it.  I trust this is more helpful.
>
> Thanks
> Will
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can multiple applications from different hosts access a single database file on NFS at the same time?

[Pavel Ivanov]

> My question is: supposed NFS implementes fcntl()  correctly, can
> multiple applications from different hosts access a single database
> file on NFS at the same time?

If fcntl() is implemented correctly on all hosts using database
(including one where the physical disk is located) then yes they can.
But the general rule is: it isn't implemented correctly. And the sad
truth is that you can't prove (in)correctness in any other way than
running your applications in production and waiting for a "malformed
database" error. If you've encountered this error then you'll see that
you just proved the general rule. If you don't ever see this error
then you're lucky to have some good NFS implementations. And now it's
up to you to calculate costs and answer a question: can you afford
trashing your database at some unpredictable point of time?


Pavel

On Thu, Mar 4, 2010 at 5:59 AM, Zhigang Wang  wrote:
> Hi all,
>
> My question is: supposed NFS implementes fcntl()  correctly, can
> multiple applications from different hosts access a single database
> file on NFS at the same time?
>
> After reading http://www.sqlite.org/faq.html#q5 and
> http://www.sqlite.org/lockingv3.html carefully, I still cannot get a
> positive answer.
>
> I think the answer is:
>
> * SELECT is supported.
> * SQLite will automatically serialize any changes to the db, no extra
> action (eg. flock()) is needed by the user application.  Eg. for
> python, I can execute the following actions on different hosts:
>
> conn = sqlite3.connect('/nfs/example')
> c = conn.cursor()
> c.execute("""insert into stocks  values
> ('2006-01-05','BUY','RHAT',100,35.14)""")
> conn.commit()
> c.execute("""insert into stocks """)
> conn.commit()
> 
>
> Please kindly confirm my thought.
>
> Thanks,
>
> Zhigang
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can multiple applications from different hosts access a single database file on NFS at the same time?

[Zhigang Wang]

Hi all,

My question is: supposed NFS implementes fcntl()  correctly, can
multiple applications from different hosts access a single database
file on NFS at the same time?

After reading http://www.sqlite.org/faq.html#q5 and
http://www.sqlite.org/lockingv3.html carefully, I still cannot get a
positive answer.

I think the answer is:

* SELECT is supported.
* SQLite will automatically serialize any changes to the db, no extra
action (eg. flock()) is needed by the user application.  Eg. for
python, I can execute the following actions on different hosts:

conn = sqlite3.connect('/nfs/example')
c = conn.cursor()
c.execute("""insert into stocks  values
('2006-01-05','BUY','RHAT',100,35.14)""")
conn.commit()
c.execute("""insert into stocks """)
conn.commit()


Please kindly confirm my thought.

Thanks,

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

Re: [sqlite] Why is this query faster?

[Bart Smissaert]

Yes, thanks, that has the same speed as the second query and it looks neater.
Has the similar query plan as the second query:

order   fromdetail
---
0   0   TABLE A3Morb5B4_F USING PRIMARY KEY
0   0   TABLE A3Morb5B4_F WITH INDEX
IDX_A3Morb5B4_E_PATIENT_ID ORDER BY


RBS



On Thu, Mar 4, 2010 at 11:32 AM, Simon Davies
 wrote:
> On 4 March 2010 10:54, Bart Smissaert  wrote:
>> One table with this create SQL:
>>
>> CREATE TABLE Table1([PATIENT_ID] INTEGER,
>> [ENTRY_ID] INTEGER PRIMARY KEY,
>> [READ_CODE] TEXT,
>> [ADDED_DATE] TEXT,
>> [START_DATE] TEXT)
>>
>> and a non-uique index on PATIENT_ID
>>
>> Then the following 2 queries, that give the same result:
>>
>> select *
>> from
>> Table1 t1
>> where
>> t1.entry_id in(select
>> max(t2.entry_id)
>> from
>> Table1 t2
>> where
>> t1.patient_id = t2.patient_id)
>> order by
>> patient_id asc
>>
>> select *
>> from
>> Table1
>> where
>> rowid in(select
>> t1.rowid
>> from
>> Table1 t1
>> where
>> t1.entry_id = (select
>> max(t2.entry_id)
>> from
>> Table1 t2
>> where
>> t1.patient_id = t2.patient_id))
>> order by
>> patient_id asc
>>
>> Now the second query is about twice as fast and looking at the query
>> plans it seems that it because the second query uses
>> the primary key and the first doesn't:
>>
>> first query:
>> order   from    detail
>> ---
>> 0               0       TABLE A3Morb41C_F AS t1 WITH INDEX
>> IDX_A3Morb41C_E_PATIENT_ID ORDER BY
>> 0               0       TABLE A3Morb41C_F AS t2 WITH INDEX
>> IDX_A3Morb41C_E_PATIENT_ID ORDER BY
>>
>> second query:
>> order   from    detail
>> ---
>> 0               0       TABLE A3Morb41C_F USING PRIMARY KEY
>> 0               0       TABLE A3Morb41C_F AS t1
>> 0               0       TABLE A3Morb41C_F AS t2 WITH INDEX
>> IDX_A3Morb41C_E_PATIENT_ID ORDER BY
>>
>>
>> Is there a way to force the use of the primary key in the first query
>> or is there any other way to tackle this?
>
> Perhaps this does what you want:
>
> select * from Table1
> where entry_id in (
>      select max( entry_id ) from Table1 group by patient_id
> );
>
>>
>>
>> RBS
>>
>
> Regards,
> Simon
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why is this query faster?

[Simon Davies]

On 4 March 2010 10:54, Bart Smissaert  wrote:
> One table with this create SQL:
>
> CREATE TABLE Table1([PATIENT_ID] INTEGER,
> [ENTRY_ID] INTEGER PRIMARY KEY,
> [READ_CODE] TEXT,
> [ADDED_DATE] TEXT,
> [START_DATE] TEXT)
>
> and a non-uique index on PATIENT_ID
>
> Then the following 2 queries, that give the same result:
>
> select *
> from
> Table1 t1
> where
> t1.entry_id in(select
> max(t2.entry_id)
> from
> Table1 t2
> where
> t1.patient_id = t2.patient_id)
> order by
> patient_id asc
>
> select *
> from
> Table1
> where
> rowid in(select
> t1.rowid
> from
> Table1 t1
> where
> t1.entry_id = (select
> max(t2.entry_id)
> from
> Table1 t2
> where
> t1.patient_id = t2.patient_id))
> order by
> patient_id asc
>
> Now the second query is about twice as fast and looking at the query
> plans it seems that it because the second query uses
> the primary key and the first doesn't:
>
> first query:
> order   from    detail
> ---
> 0               0       TABLE A3Morb41C_F AS t1 WITH INDEX
> IDX_A3Morb41C_E_PATIENT_ID ORDER BY
> 0               0       TABLE A3Morb41C_F AS t2 WITH INDEX
> IDX_A3Morb41C_E_PATIENT_ID ORDER BY
>
> second query:
> order   from    detail
> ---
> 0               0       TABLE A3Morb41C_F USING PRIMARY KEY
> 0               0       TABLE A3Morb41C_F AS t1
> 0               0       TABLE A3Morb41C_F AS t2 WITH INDEX
> IDX_A3Morb41C_E_PATIENT_ID ORDER BY
>
>
> Is there a way to force the use of the primary key in the first query
> or is there any other way to tackle this?

Perhaps this does what you want:

select * from Table1
where entry_id in (
  select max( entry_id ) from Table1 group by patient_id
);

>
>
> RBS
>

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

[sqlite] Why is this query faster?

[Bart Smissaert]

One table with this create SQL:

CREATE TABLE Table1([PATIENT_ID] INTEGER,
[ENTRY_ID] INTEGER PRIMARY KEY,
[READ_CODE] TEXT,
[ADDED_DATE] TEXT,
[START_DATE] TEXT)

and a non-uique index on PATIENT_ID

Then the following 2 queries, that give the same result:

select *
from
Table1 t1
where
t1.entry_id in(select
max(t2.entry_id)
from
Table1 t2
where
t1.patient_id = t2.patient_id)
order by
patient_id asc

select *
from
Table1
where
rowid in(select
t1.rowid
from
Table1 t1
where
t1.entry_id = (select
max(t2.entry_id)
from
Table1 t2
where
t1.patient_id = t2.patient_id))
order by
patient_id asc

Now the second query is about twice as fast and looking at the query
plans it seems that it because the second query uses
the primary key and the first doesn't:

first query:
order   fromdetail
---
0   0   TABLE A3Morb41C_F AS t1 WITH INDEX
IDX_A3Morb41C_E_PATIENT_ID ORDER BY
0   0   TABLE A3Morb41C_F AS t2 WITH INDEX
IDX_A3Morb41C_E_PATIENT_ID ORDER BY

second query:
order   fromdetail
---
0   0   TABLE A3Morb41C_F USING PRIMARY KEY
0   0   TABLE A3Morb41C_F AS t1
0   0   TABLE A3Morb41C_F AS t2 WITH INDEX
IDX_A3Morb41C_E_PATIENT_ID ORDER BY


Is there a way to force the use of the primary key in the first query
or is there any other way to tackle this?


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