[sqlite] TR: sqlite 3.7.8 and sqlite 3.7.9 crash with Apple Xcode 4.2.1 on armv6 ios device

[Gilles Vollant]

Thank you a lot for your answer.
Do you known more about this bug? Is there any other documentation?
Does it affect other code? Will apple fix it soon?

My currenct solution was using sqlite 3.7.1

Regards
Gilles Vollant


Le jeudi 1 décembre 2011, James Berry  a écrit :
> Gilles,
>
> This is due to bugs in llvm when compiling for arm6 with thumb mode
enabled. Passing -mno-thumb to the compiler will solve the problem.
>
> James
>
> On Nov 30, 2011, at 4:31 PM, Gilles Vollant wrote:
>
>> Hello,
>>
>> I'm using a recompiled version of SQLite in one project (I need FTS3 and
now
>> FTS4 support, which is not avaiable in the sqlite included in iOS).
>> So I just include "sqlite3.c" and "sqlite3.h" in my XCode projet.
>>
>> I've discovered that using LLVM compiler of Xcode 4.2.1 on Mac OS Lion
>> targeting armv6 (the project must be compatible with iPhone 3G) with
>> optimisation has a bug with sqLite source.
>>
>> Please note that :
>> - sqlite 3.7.7.1 has no problem, sqlite 3.7.8 and sqlite 3.7.9 has the
>> problem
>> - there is no problem with armv7 (iPhone 3GS and higher) compiler
>> - there is no problem in debug mode (or release with optimization fully
>> disabled)
>> - there is no problem with old xcode 4.0 and GCC 4.2 compiler
>>
>>
>> you can download this project, do a Release build to iOS device and start
on
>> iOS device, then tap on the "i". This demo application crash in very
simple
>> sql code.
>> (it crashes when compiling a very simple SQL command):
>>
>> http://gvollant.free.fr/sqliteCrashXcode.zip
>>
>> I used
>>
>> http://sqlite.org/sqlite-amalgamation-3070900.zip : latest SQLite 3.7.9
>> which crashes
>> http://sqlite.org/sqlite-amalgamation-3070800.zip : SQLite 3.7.8 which
>> crashes
>> http://sqlite.org/sqlite-amalgamation-3070701.zip : SQLite 3.7.7.1 which
run
>> well
>>
>> ___
>> 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] sqlite 3.7.8 and sqlite 3.7.9 crash with Apple Xcode 4.2.1 on armv6 ios device

[sq sq]

Le jeudi 1 décembre 2011, James Berry  a écrit :
> Gilles,
>
> This is due to bugs in llvm when compiling for arm6 with thumb mode
enabled. Passing -mno-thumb to the compiler will solve the problem.
>
> James
>
> On Nov 30, 2011, at 4:31 PM, Gilles Vollant wrote:
>
>> Hello,
>>
>> I'm using a recompiled version of SQLite in one project (I need FTS3 and
now
>> FTS4 support, which is not avaiable in the sqlite included in iOS).
>> So I just include "sqlite3.c" and "sqlite3.h" in my XCode projet.
>>
>> I've discovered that using LLVM compiler of Xcode 4.2.1 on Mac OS Lion
>> targeting armv6 (the project must be compatible with iPhone 3G) with
>> optimisation has a bug with sqLite source.
>>
>> Please note that :
>> - sqlite 3.7.7.1 has no problem, sqlite 3.7.8 and sqlite 3.7.9 has the
>> problem
>> - there is no problem with armv7 (iPhone 3GS and higher) compiler
>> - there is no problem in debug mode (or release with optimization fully
>> disabled)
>> - there is no problem with old xcode 4.0 and GCC 4.2 compiler
>>
>>
>> you can download this project, do a Release build to iOS device and
start on
>> iOS device, then tap on the "i". This demo application crash in very
simple
>> sql code.
>> (it crashes when compiling a very simple SQL command):
>>
>> http://gvollant.free.fr/sqliteCrashXcode.zip
>>
>> I used
>>
>> http://sqlite.org/sqlite-amalgamation-3070900.zip : latest SQLite 3.7.9
>> which crashes
>> http://sqlite.org/sqlite-amalgamation-3070800.zip : SQLite 3.7.8 which
>> crashes
>> http://sqlite.org/sqlite-amalgamation-3070701.zip : SQLite 3.7.7.1 which
run
>> well
>>
>> ___
>> 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] sqlite 3.7.8 and sqlite 3.7.9 crash with Apple Xcode 4.2.1 on armv6 ios device

[James Berry]

(you only need to turn off thumb mode for the arm6 architecture) -jdb

On Nov 30, 2011, at 8:02 PM, James Berry wrote:

> Gilles,
> 
> This is due to bugs in llvm when compiling for arm6 with thumb mode enabled. 
> Passing -mno-thumb to the compiler will solve the problem.
> 
> James
> 
> On Nov 30, 2011, at 4:31 PM, Gilles Vollant wrote:
> 
>> Hello,
>> 
>> I'm using a recompiled version of SQLite in one project (I need FTS3 and now
>> FTS4 support, which is not avaiable in the sqlite included in iOS).
>> So I just include "sqlite3.c" and "sqlite3.h" in my XCode projet.
>> 
>> I've discovered that using LLVM compiler of Xcode 4.2.1 on Mac OS Lion
>> targeting armv6 (the project must be compatible with iPhone 3G) with
>> optimisation has a bug with sqLite source.
>> 
>> Please note that :
>> - sqlite 3.7.7.1 has no problem, sqlite 3.7.8 and sqlite 3.7.9 has the
>> problem
>> - there is no problem with armv7 (iPhone 3GS and higher) compiler
>> - there is no problem in debug mode (or release with optimization fully
>> disabled)
>> - there is no problem with old xcode 4.0 and GCC 4.2 compiler
>> 
>> 
>> you can download this project, do a Release build to iOS device and start on
>> iOS device, then tap on the "i". This demo application crash in very simple
>> sql code.
>> (it crashes when compiling a very simple SQL command):
>> 
>> http://gvollant.free.fr/sqliteCrashXcode.zip
>> 
>> I used
>> 
>> http://sqlite.org/sqlite-amalgamation-3070900.zip : latest SQLite 3.7.9
>> which crashes
>> http://sqlite.org/sqlite-amalgamation-3070800.zip : SQLite 3.7.8 which
>> crashes
>> http://sqlite.org/sqlite-amalgamation-3070701.zip : SQLite 3.7.7.1 which run
>> well
>> 
>> ___
>> 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] sqlite 3.7.8 and sqlite 3.7.9 crash with Apple Xcode 4.2.1 on armv6 ios device

[James Berry]

Gilles,

This is due to bugs in llvm when compiling for arm6 with thumb mode enabled. 
Passing -mno-thumb to the compiler will solve the problem.

James

On Nov 30, 2011, at 4:31 PM, Gilles Vollant wrote:

> Hello,
> 
> I'm using a recompiled version of SQLite in one project (I need FTS3 and now
> FTS4 support, which is not avaiable in the sqlite included in iOS).
> So I just include "sqlite3.c" and "sqlite3.h" in my XCode projet.
> 
> I've discovered that using LLVM compiler of Xcode 4.2.1 on Mac OS Lion
> targeting armv6 (the project must be compatible with iPhone 3G) with
> optimisation has a bug with sqLite source.
> 
> Please note that :
> - sqlite 3.7.7.1 has no problem, sqlite 3.7.8 and sqlite 3.7.9 has the
> problem
> - there is no problem with armv7 (iPhone 3GS and higher) compiler
> - there is no problem in debug mode (or release with optimization fully
> disabled)
> - there is no problem with old xcode 4.0 and GCC 4.2 compiler
> 
> 
> you can download this project, do a Release build to iOS device and start on
> iOS device, then tap on the "i". This demo application crash in very simple
> sql code.
> (it crashes when compiling a very simple SQL command):
> 
> http://gvollant.free.fr/sqliteCrashXcode.zip
> 
> I used
> 
> http://sqlite.org/sqlite-amalgamation-3070900.zip : latest SQLite 3.7.9
> which crashes
> http://sqlite.org/sqlite-amalgamation-3070800.zip : SQLite 3.7.8 which
> crashes
> http://sqlite.org/sqlite-amalgamation-3070701.zip : SQLite 3.7.7.1 which run
> well
> 
> ___
> 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] sqlite 3.7.8 and sqlite 3.7.9 crash with Apple Xcode 4.2.1 on armv6 ios device

[Gilles Vollant]

Hello,

I'm using a recompiled version of SQLite in one project (I need FTS3 and now
FTS4 support, which is not avaiable in the sqlite included in iOS).
So I just include "sqlite3.c" and "sqlite3.h" in my XCode projet.

I've discovered that using LLVM compiler of Xcode 4.2.1 on Mac OS Lion
targeting armv6 (the project must be compatible with iPhone 3G) with
optimisation has a bug with sqLite source.

Please note that :
- sqlite 3.7.7.1 has no problem, sqlite 3.7.8 and sqlite 3.7.9 has the
problem
- there is no problem with armv7 (iPhone 3GS and higher) compiler
- there is no problem in debug mode (or release with optimization fully
disabled)
- there is no problem with old xcode 4.0 and GCC 4.2 compiler


you can download this project, do a Release build to iOS device and start on
iOS device, then tap on the "i". This demo application crash in very simple
sql code.
(it crashes when compiling a very simple SQL command):

http://gvollant.free.fr/sqliteCrashXcode.zip

I used

http://sqlite.org/sqlite-amalgamation-3070900.zip : latest SQLite 3.7.9
which crashes
http://sqlite.org/sqlite-amalgamation-3070800.zip : SQLite 3.7.8 which
crashes
http://sqlite.org/sqlite-amalgamation-3070701.zip : SQLite 3.7.7.1 which run
well

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

Re: [sqlite] WAL file size

[Teg]

Hello Richard,

Thanks again. 

Wednesday, November 30, 2011, 5:37:32 PM, you wrote:

RH> On Wed, Nov 30, 2011 at 5:31 PM, Teg  wrote:

>> Since we're on this topic
>>
>> If  I  open  a  30  GB  DB  file,  then attach a new empty DB and then
>> select/insert  all the data from the full DB to the empty one, the WAL
>> file grows to 4 times the size of the source file. Why is that?
>>
>> I  don't  believe I use any manual checkpointing. Just let WAL do it's
>> thing.
>>

RH> The WAL file won't get so big if you break the transfer up into multiple
RH> transactions.

RH> Or, you can temporarily disable WAL mode for certain unusual operations
RH> (like this one) for which is not well suited.


>>
>>
>>
>>
>>
>> Wednesday, November 30, 2011, 4:36:00 PM, you wrote:
>>
>>
>> SS> On 30 Nov 2011, at 7:01pm, Richard Hipp wrote:
>>
>> >> The wal file persists until the last connection to the database closes,
>> >> then the wal file is deleted.
>>
>> SS> Actually this is the key to a lot of questions about this.  If
>> SS> your WAL file is taking up too much space, quit and restart your
>> SS> app.  Or have your app close and reopen the database connection.
>>
>> SS> It's a little more complicated in multi-user or multi-process
>> situations, of course.
>>
>> SS> Simon.
>> SS> ___
>> SS> sqlite-users mailing list
>> SS> sqlite-users@sqlite.org
>> SS> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>>
>> --
>> Best regards,
>>  Tegmailto:t...@djii.com
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>






-- 
Best regards,
 Tegmailto:t...@djii.com

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

Re: [sqlite] WAL file size

[Richard Hipp]

On Wed, Nov 30, 2011 at 5:31 PM, Teg  wrote:

> Since we're on this topic
>
> If  I  open  a  30  GB  DB  file,  then attach a new empty DB and then
> select/insert  all the data from the full DB to the empty one, the WAL
> file grows to 4 times the size of the source file. Why is that?
>
> I  don't  believe I use any manual checkpointing. Just let WAL do it's
> thing.
>

The WAL file won't get so big if you break the transfer up into multiple
transactions.

Or, you can temporarily disable WAL mode for certain unusual operations
(like this one) for which is not well suited.


>
>
>
>
>
> Wednesday, November 30, 2011, 4:36:00 PM, you wrote:
>
>
> SS> On 30 Nov 2011, at 7:01pm, Richard Hipp wrote:
>
> >> The wal file persists until the last connection to the database closes,
> >> then the wal file is deleted.
>
> SS> Actually this is the key to a lot of questions about this.  If
> SS> your WAL file is taking up too much space, quit and restart your
> SS> app.  Or have your app close and reopen the database connection.
>
> SS> It's a little more complicated in multi-user or multi-process
> situations, of course.
>
> SS> Simon.
> SS> ___
> SS> sqlite-users mailing list
> SS> sqlite-users@sqlite.org
> SS> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>
> --
> Best regards,
>  Tegmailto:t...@djii.com
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] WAL file size

[Teg]

Since we're on this topic

If  I  open  a  30  GB  DB  file,  then attach a new empty DB and then
select/insert  all the data from the full DB to the empty one, the WAL
file grows to 4 times the size of the source file. Why is that?

I  don't  believe I use any manual checkpointing. Just let WAL do it's
thing.





Wednesday, November 30, 2011, 4:36:00 PM, you wrote:


SS> On 30 Nov 2011, at 7:01pm, Richard Hipp wrote:

>> The wal file persists until the last connection to the database closes,
>> then the wal file is deleted.

SS> Actually this is the key to a lot of questions about this.  If
SS> your WAL file is taking up too much space, quit and restart your
SS> app.  Or have your app close and reopen the database connection.

SS> It's a little more complicated in multi-user or multi-process situations, 
of course.

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




-- 
Best regards,
 Tegmailto:t...@djii.com

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

Re: [sqlite] WAL file size

[Simon Slavin]

On 30 Nov 2011, at 7:01pm, Richard Hipp wrote:

> The wal file persists until the last connection to the database closes,
> then the wal file is deleted.

Actually this is the key to a lot of questions about this.  If your WAL file is 
taking up too much space, quit and restart your app.  Or have your app close 
and reopen the database connection.

It's a little more complicated in multi-user or multi-process situations, of 
course.

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

Re: [sqlite] EXT :Re: WAL file size

[Black, Michael (IS)]

So how about the "pragma wal_trim" that does a checkpoint, grabs the lock, 
trims, and drops the lock?

Or some other new keyword?



The WAL file never gets trimmed (just reused apparently) so having an explicit 
way to trim it without having to close and reopen the database would seem quite 
handy.



And...would also address the FAQ of "why doesn't my wal file shrink?" with a 
simple "use 'pragma wal_trim but note that it will block readers and writers'







Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp [d...@sqlite.org]
Sent: Wednesday, November 30, 2011 12:59 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] WAL file size

On Wed, Nov 30, 2011 at 1:21 PM, Black, Michael (IS)  wrote:

> Maybe we need a "pragma wal_trim" ??  This would automatically trim it
> after a checkpoint?  Or make it an explicit action?
>

checkpoint cannot trim.  This is because of the rule that checkpoints will
never block either readers or writers, and a trim operation requires
getting a write lock, and hence as the potential to block writers.  That's
why the trim is done by the next writer - since it already has to get a
write lock and hence has already dealt with any concurrency issues.



>
>
>
> Waiting for another write before truncating it seems too late and not a
> logical thing to expect.
>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Richard Hipp [d...@sqlite.org]
> Sent: Wednesday, November 30, 2011 12:10 PM
> To: Teg; General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] WAL file size
>
> On Wed, Nov 30, 2011 at 1:04 PM, Teg  wrote:
>
> > Hello Richard,
> >
> > When  does the WAL get trimmed down? If my transaction completes, does
> > the  WAL file get deleted? I've seen the WAL grow to 4 times my actual
> > DB  size  so, I don't really want 160 Gigs of WAL hanging out when the
> > process that generated it is only run once a month.
> >
>
> SQLite starts writing the WAL file from the beginning again on the first
> write transaction that follows a checkpoint that ran to completion and
> where there are no readers using the WAL file.  A checkpoint will normally
> run to completion if there are no readers still using prior transactions.
> If there are readers on prior transactions, then the checkpoint cannot run
> to completion since that would delete content out from under the readers.
>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] WAL file size

[Teg]

Hello Richard,

That's what I was hoping to hear. Thanks,



Wednesday, November 30, 2011, 2:01:46 PM, you wrote:

RH> On Wed, Nov 30, 2011 at 1:39 PM, Teg  wrote:

>> Hello Richard,
>>
>> Appreciate the reply but, it doesn't address my question (unless I'm
>> missing something). I get that checkpointing doesn't reduce the size
>> of the WAL. Assuming a single writer and no readers, If I perform a
>> transaction that generates a WAL file. Does it eventually get deleted
>> when I finish my transaction? How about when I close the DB?


RH> The wal file persists until the last connection to the database closes,
RH> then the wal file is deleted.  Meanwhile, the wal file is recycled (meaning
RH> that SQLite starts reusing it again from the beginning) after each
RH> successful checkpoint.  Checkpoints are automatically run by transactions
RH> that cause the size of the wal file to exceed 1000 pages (though this can
RH> be tuned or disabled if desired).

RH> For most applications, WAL mode just works and you don't have to think
RH> about how.



>> I haven't
>> updated to the most current SQlite because I was unclear about how
>> persistent the WAL files actually were. Some of my DB's are 30 GB+. I
>> don't mind the WAL's growing as long as they go away when the process
>> is finished.
>>
>>
>>
>>
>>
>> Wednesday, November 30, 2011, 1:10:50 PM, you wrote:
>>
>> RH> On Wed, Nov 30, 2011 at 1:04 PM, Teg  wrote:
>>
>> >> Hello Richard,
>> >>
>> >> When  does the WAL get trimmed down? If my transaction completes, does
>> >> the  WAL file get deleted? I've seen the WAL grow to 4 times my actual
>> >> DB  size  so, I don't really want 160 Gigs of WAL hanging out when the
>> >> process that generated it is only run once a month.
>> >>
>>
>> RH> SQLite starts writing the WAL file from the beginning again on the
>> first
>> RH> write transaction that follows a checkpoint that ran to completion and
>> RH> where there are no readers using the WAL file.  A checkpoint will
>> normally
>> RH> run to completion if there are no readers still using prior
>> transactions.
>> RH> If there are readers on prior transactions, then the checkpoint cannot
>> run
>> RH> to completion since that would delete content out from under the
>> readers.
>>
>>
>>
>>
>>
>>
>>
>> --
>> Best regards,
>>  Tegmailto:t...@djii.com
>>
>>





-- 
Best regards,
 Tegmailto:t...@djii.com

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

Re: [sqlite] WAL file size

[Richard Hipp]

On Wed, Nov 30, 2011 at 1:39 PM, Teg  wrote:

> Hello Richard,
>
> Appreciate the reply but, it doesn't address my question (unless I'm
> missing something). I get that checkpointing doesn't reduce the size
> of the WAL. Assuming a single writer and no readers, If I perform a
> transaction that generates a WAL file. Does it eventually get deleted
> when I finish my transaction? How about when I close the DB?


The wal file persists until the last connection to the database closes,
then the wal file is deleted.  Meanwhile, the wal file is recycled (meaning
that SQLite starts reusing it again from the beginning) after each
successful checkpoint.  Checkpoints are automatically run by transactions
that cause the size of the wal file to exceed 1000 pages (though this can
be tuned or disabled if desired).

For most applications, WAL mode just works and you don't have to think
about how.



> I haven't
> updated to the most current SQlite because I was unclear about how
> persistent the WAL files actually were. Some of my DB's are 30 GB+. I
> don't mind the WAL's growing as long as they go away when the process
> is finished.
>
>
>
>
>
> Wednesday, November 30, 2011, 1:10:50 PM, you wrote:
>
> RH> On Wed, Nov 30, 2011 at 1:04 PM, Teg  wrote:
>
> >> Hello Richard,
> >>
> >> When  does the WAL get trimmed down? If my transaction completes, does
> >> the  WAL file get deleted? I've seen the WAL grow to 4 times my actual
> >> DB  size  so, I don't really want 160 Gigs of WAL hanging out when the
> >> process that generated it is only run once a month.
> >>
>
> RH> SQLite starts writing the WAL file from the beginning again on the
> first
> RH> write transaction that follows a checkpoint that ran to completion and
> RH> where there are no readers using the WAL file.  A checkpoint will
> normally
> RH> run to completion if there are no readers still using prior
> transactions.
> RH> If there are readers on prior transactions, then the checkpoint cannot
> run
> RH> to completion since that would delete content out from under the
> readers.
>
>
>
>
>
>
>
> --
> Best regards,
>  Tegmailto:t...@djii.com
>
>


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

Re: [sqlite] WAL file size

[Richard Hipp]

On Wed, Nov 30, 2011 at 1:21 PM, Black, Michael (IS)  wrote:

> Maybe we need a "pragma wal_trim" ??  This would automatically trim it
> after a checkpoint?  Or make it an explicit action?
>

checkpoint cannot trim.  This is because of the rule that checkpoints will
never block either readers or writers, and a trim operation requires
getting a write lock, and hence as the potential to block writers.  That's
why the trim is done by the next writer - since it already has to get a
write lock and hence has already dealt with any concurrency issues.



>
>
>
> Waiting for another write before truncating it seems too late and not a
> logical thing to expect.
>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Richard Hipp [d...@sqlite.org]
> Sent: Wednesday, November 30, 2011 12:10 PM
> To: Teg; General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] WAL file size
>
> On Wed, Nov 30, 2011 at 1:04 PM, Teg  wrote:
>
> > Hello Richard,
> >
> > When  does the WAL get trimmed down? If my transaction completes, does
> > the  WAL file get deleted? I've seen the WAL grow to 4 times my actual
> > DB  size  so, I don't really want 160 Gigs of WAL hanging out when the
> > process that generated it is only run once a month.
> >
>
> SQLite starts writing the WAL file from the beginning again on the first
> write transaction that follows a checkpoint that ran to completion and
> where there are no readers using the WAL file.  A checkpoint will normally
> run to completion if there are no readers still using prior transactions.
> If there are readers on prior transactions, then the checkpoint cannot run
> to completion since that would delete content out from under the readers.
>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Error when compiling with the SQLITE_OMIT_WALL directive

[Richard Hipp]

On Wed, Nov 30, 2011 at 1:30 PM, Adolfo Jiménez Millán wrote:

> Pavel:
>
> Thanks for your answer.
>
> By the way, there are a similar error when using the directive
> SQLITE_OMIT_ANALYZE
>

SQLITE_OMIT_ANALYZE won't work with the amalgamation because the
amalgamation is generated code, and different code is generated depending
on whether or not SQLITE_OMIT_ANALYZE is present.  To use
SQLITE_OMIT_ANALYZE, you have to start with the canonical source files.



>
> =0A--
> Adolfo.
>
> >
> >  Mensaje original 
> > De: Pavel Ivanov 
> > Para:  a...@zator.com, General Discussion of SQLite Database <
> sqlite-users@sqlite.org>
> > Fecha:  Wed, 30 Nov 2011 16:33:42 +0100
> > Asunto:  Re: [sqlite] Error when compiling with the SQLITE_OMIT_WALL
> directive
> >
> >
> >
> >This will be fixed in next version. See
> >http://www.sqlite.org/src/info/5dbfaed8c3.
> >
> >Pavel
> >
> >
> >On Wed, Nov 30, 2011 at 6:29 AM, Adolfo Jiménez Millán 
> >wrote:
> =0A>> Hi all:
> >>
> >> Using MS VC++ 98, I try to compile the 3.7.9 amalgamation, using the
> SQLI
> >TE OMIT WAL directive, but then I get an error:
> >>
> >> sqlite3.c(131886) : error C2129: static function 'void
> sqlite3PagerClearC
> >ache(Pager *)' declared but not defined.
> >>
> >> I've used some other directives, but all of them compiles smoothly.
> >>
> >> Any clue?
> >>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] WAL file size

[Teg]

Hello Richard,

Appreciate the reply but, it doesn't address my question (unless I'm
missing something). I get that checkpointing doesn't reduce the size
of the WAL. Assuming a single writer and no readers, If I perform a
transaction that generates a WAL file. Does it eventually get deleted
when I finish my transaction? How about when I close the DB? I haven't
updated to the most current SQlite because I was unclear about how
persistent the WAL files actually were. Some of my DB's are 30 GB+. I
don't mind the WAL's growing as long as they go away when the process
is finished.





Wednesday, November 30, 2011, 1:10:50 PM, you wrote:

RH> On Wed, Nov 30, 2011 at 1:04 PM, Teg  wrote:

>> Hello Richard,
>>
>> When  does the WAL get trimmed down? If my transaction completes, does
>> the  WAL file get deleted? I've seen the WAL grow to 4 times my actual
>> DB  size  so, I don't really want 160 Gigs of WAL hanging out when the
>> process that generated it is only run once a month.
>>

RH> SQLite starts writing the WAL file from the beginning again on the first
RH> write transaction that follows a checkpoint that ran to completion and
RH> where there are no readers using the WAL file.  A checkpoint will normally
RH> run to completion if there are no readers still using prior transactions.
RH> If there are readers on prior transactions, then the checkpoint cannot run
RH> to completion since that would delete content out from under the readers.







-- 
Best regards,
 Tegmailto:t...@djii.com

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

Re: [sqlite] Error when compiling with the SQLITE_OMIT_WALL directive

[Adolfo Jiménez Millán]

Pavel:

Thanks for your answer.

By the way, there are a similar error when using the directive 
SQLITE_OMIT_ANALYZE

=0A--
Adolfo.

>
>  Mensaje original 
> De: Pavel Ivanov 
> Para:  a...@zator.com, General Discussion of SQLite Database 
> 
> Fecha:  Wed, 30 Nov 2011 16:33:42 +0100
> Asunto:  Re: [sqlite] Error when compiling with the SQLITE_OMIT_WALL directive
>
> 
>
>This will be fixed in next version. See
>http://www.sqlite.org/src/info/5dbfaed8c3.
>
>Pavel
>
>
>On Wed, Nov 30, 2011 at 6:29 AM, Adolfo Jiménez Millán  
>wrote:
=0A>> Hi all:
>>
>> Using MS VC++ 98, I try to compile the 3.7.9 amalgamation, using the SQLI
>TE OMIT WAL directive, but then I get an error:
>>
>> sqlite3.c(131886) : error C2129: static function 'void sqlite3PagerClearC
>ache(Pager *)' declared but not defined.
>>
>> I've used some other directives, but all of them compiles smoothly.
>>
>> Any clue?
>>

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

Re: [sqlite] WAL file size

[Black, Michael (IS)]

Maybe we need a "pragma wal_trim" ??  This would automatically trim it after a 
checkpoint?  Or make it an explicit action?



Waiting for another write before truncating it seems too late and not a logical 
thing to expect.





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp [d...@sqlite.org]
Sent: Wednesday, November 30, 2011 12:10 PM
To: Teg; General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] WAL file size

On Wed, Nov 30, 2011 at 1:04 PM, Teg  wrote:

> Hello Richard,
>
> When  does the WAL get trimmed down? If my transaction completes, does
> the  WAL file get deleted? I've seen the WAL grow to 4 times my actual
> DB  size  so, I don't really want 160 Gigs of WAL hanging out when the
> process that generated it is only run once a month.
>

SQLite starts writing the WAL file from the beginning again on the first
write transaction that follows a checkpoint that ran to completion and
where there are no readers using the WAL file.  A checkpoint will normally
run to completion if there are no readers still using prior transactions.
If there are readers on prior transactions, then the checkpoint cannot run
to completion since that would delete content out from under the readers.




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

Re: [sqlite] WAL file size

[Richard Hipp]

On Wed, Nov 30, 2011 at 1:04 PM, Teg  wrote:

> Hello Richard,
>
> When  does the WAL get trimmed down? If my transaction completes, does
> the  WAL file get deleted? I've seen the WAL grow to 4 times my actual
> DB  size  so, I don't really want 160 Gigs of WAL hanging out when the
> process that generated it is only run once a month.
>

SQLite starts writing the WAL file from the beginning again on the first
write transaction that follows a checkpoint that ran to completion and
where there are no readers using the WAL file.  A checkpoint will normally
run to completion if there are no readers still using prior transactions.
If there are readers on prior transactions, then the checkpoint cannot run
to completion since that would delete content out from under the readers.




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

Re: [sqlite] WAL file size

[Sreekumar TP]

Hi Richard,

How does sqlite determine the right size for the WAL file? Application can
ofcourse indicate the maximum size.

-Sreekumar

On Wed, Nov 30, 2011 at 4:02 PM, Richard Hipp  wrote:

> On Wed, Nov 30, 2011 at 4:41 AM, Sreekumar TP  >wrote:
>
> > Hello,
> >
> > I have sqlite configure to do manual checkpoint. I do checkpoint every
> few
> > hundred records. I expect the WAL to reduce in size and become zero when
> > there are no more inserts done.
> > However, I see that the WAL file size is static and does not reduce in
> > size.  Why isnt manual checkpoint reducing the size of WAL file.
> >
>
> Because we have experimentally determined that it is faster to overwrite an
> existing file than to append to a file.  So we don't truncate the WAL file
> on a checkpoint, which makes subsequent writes to the WAL file go faster.
>
> SQLite will truncate the WAL file down to the size specified by the "PRAGMA
> journal_size_limit" setting, if you have set the journal_size_limit.
>
>
> >
> > -Sreekumar
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL file size

[Teg]

Hello Richard,

When  does the WAL get trimmed down? If my transaction completes, does
the  WAL file get deleted? I've seen the WAL grow to 4 times my actual
DB  size  so, I don't really want 160 Gigs of WAL hanging out when the
process that generated it is only run once a month.


Wednesday, November 30, 2011, 10:02:34 AM, you wrote:

RH> On Wed, Nov 30, 2011 at 4:41 AM, Sreekumar TP wrote:

RH> Because we have experimentally determined that it is faster to overwrite an
RH> existing file than to append to a file.  So we don't truncate the WAL file
RH> on a checkpoint, which makes subsequent writes to the WAL file go faster.

RH> SQLite will truncate the WAL file down to the size specified by the "PRAGMA
RH> journal_size_limit" setting, if you have set the journal_size_limit.


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






-- 
Best regards,
 Tegmailto:t...@djii.com

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

Re: [sqlite] Inserting Multiple Rows in a single statement

[Alaric Snell-Pym]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/30/2011 03:41 PM, Pavel Ivanov wrote:
>> I do have multiple insertions bounded by BEGIN-COMMIT . I am looking at
>> possibilities of making the insertions faster.
>
> Prepare your statement in the form
>
> INSERT INTO table_name (val1, val2, ...) VALUES (?1, ?2, ...)
>
> Then for each row you want to insert you will bind necessary values,
> execute statement and reset. With all that wrapped in transaction
> there's no way to make insertions any faster.

If you have lots of indexes, then for really big inserts (where the
ratio of rows added to rows already existing is high), it might be
quicker to drop the indexes, do the inserts, then put them back
(especially with the new faster CREATE INDEX).

Failing that, if you have a block of inserts to do, sorting them by an
indexed column (especially primary key) before insertion might boost
throughput a bit by increasing locality of reference in the B-Trees.

Aside: I was talking about B-Trees with some colleagues when my six year
old daughter (then five, I think, actually) butted in and said they'd
learnt about B-Trees in school that day. Sadly, it was some spelling
game, rather than the school getting a bit of computer science in early...

There are index structures that support faster insertions than B-Trees,
at the cost of slightly slower selects: streaming merge trees in
particular have come to my attention:

http://www.acunu.com/blogs/tom-wilkie/castle-storage-engine-oscon/

Perhaps future versions of SQLite might support some of this crazy stuff :-)

ABS

- --
Alaric Snell-Pym
http://www.snell-pym.org.uk/alaric/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk7WUZwACgkQRgz/WHNxCGoSkQCcCwZ7WzAPsUKMGnN2ZiTJ5AoB
xicAn1sgb60s40XQPlYIXdMNmfRoOBxk
=DOWQ
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserting Multiple Rows in a single statement

[Pavel Ivanov]

> I do have multiple insertions bounded by BEGIN-COMMIT . I am looking at
> possibilities of making the insertions faster.

Prepare your statement in the form

INSERT INTO table_name (val1, val2, ...) VALUES (?1, ?2, ...)

Then for each row you want to insert you will bind necessary values,
execute statement and reset. With all that wrapped in transaction
there's no way to make insertions any faster. Even if SQLite supported
the multi-row insert syntax it would do exactly the same operations -
bind values, insert row, reset, bind values, insert row, reset.


Pavel


On Wed, Nov 30, 2011 at 2:52 AM, Sreekumar TP  wrote:
> Hi,
>
> I do have multiple insertions bounded by BEGIN-COMMIT . I am looking at
> possibilities of making the insertions faster.
>
> -Sreekumar
>
> On Tue, Nov 29, 2011 at 4:36 PM, Donald Griggs  wrote:
>
>> Sreekumar,
>>
>> Regarding:
>> >
>> > Is it possible to insert multiple rows using a single statement ?
>> >
>>
>> You might want to let us know your reasons for requesting this.
>>
>> If it's speed of insertion you're after, then be sure to put many INSERT's
>> into each transaction. That is, be sure to surround a batch of, say, 1000
>> INSERT's with BEGIN and END statements.
>>
>> http://sqlite.org/lang_transaction.html
>>  ___
>> 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] Error when compiling with the SQLITE_OMIT_WALL directive

[Pavel Ivanov]

This will be fixed in next version. See
http://www.sqlite.org/src/info/5dbfaed8c3.

Pavel


On Wed, Nov 30, 2011 at 6:29 AM, Adolfo Jiménez Millán  wrote:
> Hi all:
>
> Using MS VC++ 98, I try to compile the 3.7.9 amalgamation, using the 
> SQLITE_OMIT_WAL directive, but then I get an error:
>
> sqlite3.c(131886) : error C2129: static function 'void 
> sqlite3PagerClearCache(Pager *)' declared but not defined.
>
> I've used some other directives, but all of them compiles smoothly.
>
> Any clue?
>
> A.J.Millán.
>
>
> ___
> 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] WAL file size

[Filip Navara]

On Wed, Nov 30, 2011 at 4:02 PM, Richard Hipp  wrote:

> On Wed, Nov 30, 2011 at 4:41 AM, Sreekumar TP  >wrote:
>
> > Hello,
> >
> > I have sqlite configure to do manual checkpoint. I do checkpoint every
> few
> > hundred records. I expect the WAL to reduce in size and become zero when
> > there are no more inserts done.
> > However, I see that the WAL file size is static and does not reduce in
> > size.  Why isnt manual checkpoint reducing the size of WAL file.
> >
>
> Because we have experimentally determined that it is faster to overwrite an
> existing file than to append to a file.  So we don't truncate the WAL file
> on a checkpoint, which makes subsequent writes to the WAL file go faster.
>
> SQLite will truncate the WAL file down to the size specified by the "PRAGMA
> journal_size_limit" setting, if you have set the journal_size_limit.
>
>
Would it be possible to enhance SQLite to allow resetting the WAL file down
to zero bytes?

Something like
  pragma journal_size_limit=0;
  pragma wal_checkpoint=restart;

Best regards,
Filip Navara
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL file size

[Richard Hipp]

On Wed, Nov 30, 2011 at 4:41 AM, Sreekumar TP wrote:

> Hello,
>
> I have sqlite configure to do manual checkpoint. I do checkpoint every few
> hundred records. I expect the WAL to reduce in size and become zero when
> there are no more inserts done.
> However, I see that the WAL file size is static and does not reduce in
> size.  Why isnt manual checkpoint reducing the size of WAL file.
>

Because we have experimentally determined that it is faster to overwrite an
existing file than to append to a file.  So we don't truncate the WAL file
on a checkpoint, which makes subsequent writes to the WAL file go faster.

SQLite will truncate the WAL file down to the size specified by the "PRAGMA
journal_size_limit" setting, if you have set the journal_size_limit.


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



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

Re: [sqlite] mandatory locks versus advisory locks

[Dan Kennedy]

On 11/30/2011 06:55 AM, Andy Spencer wrote:

I have seen documentation that SQLite uses POSIX advisory locks to
implement locking on Unix.

I have an issue in that attempts to operate on a sqlite database on a
Linux file system that has setgid bit (set group ID on execution) set
on the database file (e.g. chmod g+s) causes database operations to
fail (presumably returning SQLITE_BUSY and "database is locked" error
message).

I looked at the fcntl documentation (since fcntl is used to acquire,
release, and test for the existence of record locks), and this
mentioned both advisory locks and mandatory locks.
If mandatory locking is enabled on a file system, then mandatory
locking is enabled on a file by disabling group execute permission on
the file and enabling the set-group-ID permission bit.

Is sqlite supposed to be able to work on a file that has mandatory
locking enabled?


I think it should work in rollback mode at least. Not sure about
WAL mode. Probably not...

But its untested of course. Maybe check using strace or
similar that the problem is related to mandatory locking.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using VACUUM on an empty file will create a defaultdatabase

[Igor Tandetnik]

sworddrag...@aol.com wrote:
> I'm using sqlite3 3.7.9 on Ubuntu 12.04 dev. If the VACUUM command is used on 
> empty files it will create a default database (for
> example "touch /tmp/test.txt && sqlite3 /tmp/test.txt VACUUM"). 
> 
> If this behaviour is wanted there should be an option to disable this because 
> it could destroy complete environments like
> operation systems. 

In other news, "echo 'garbage' > some/random/file" considered harmful.
-- 
Igor Tandetnik

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

[sqlite] mandatory locks versus advisory locks

[Andy Spencer]

I have seen documentation that SQLite uses POSIX advisory locks to
implement locking on Unix.

I have an issue in that attempts to operate on a sqlite database on a
Linux file system that has setgid bit (set group ID on execution) set
on the database file (e.g. chmod g+s) causes database operations to
fail (presumably returning SQLITE_BUSY and "database is locked" error
message).

I looked at the fcntl documentation (since fcntl is used to acquire,
release, and test for the existence of record locks), and this
mentioned both advisory locks and mandatory locks.
If mandatory locking is enabled on a file system, then mandatory
locking is enabled on a file by disabling group execute permission on
the file and enabling the set-group-ID permission bit.

Is sqlite supposed to be able to work on a file that has mandatory
locking enabled?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Using VACUUM on an empty file will create a default database

[sworddragon2]

I'm using sqlite3 3.7.9 on Ubuntu 12.04 dev. If the VACUUM command is used on 
empty files it will create a default database (for example "touch /tmp/test.txt 
&& sqlite3 /tmp/test.txt VACUUM").

If this behaviour is wanted there should be an option to disable this because 
it could destroy complete environments like operation systems.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Looping select

[Don V Nielsen]

There is too much ambiguity with regards to needid; poor coding|edit
checking on my part.  When I label everything properly, then the query runs
successfully (darn fast, too).  I also changed the replacement tag @depth
to @dpth.  When I was examining everything with notepad++, it highlighted
@depth as a keyword.  I thought maybe I should change that.

Anywho, the system was not throwing an error.  It just cycled and cycled
and cycled.

dvn

=

update seg_02_matches set rtwgt =
(
  select avg(rowid) from
  (
select pr.rowid
from seg_02_matches as m1
inner join seg_02_matches_view as n1 on n1.rowid = m1.needid
inner join seg_02_pool_view as p on p.zip = n1.zip and p.crrt = n1.crrt
inner join add_priorities as pr on pr.prty = p.prty and pr.poolid =
p.fileid
where m1.needid = seg_02_matches.needid
order by pr.rowid
limit @dpth
  )
)
where needid in
(
  select m2.needid from seg_02_matches as m2
  inner join seg_02_matches_view as n2 on n2.rowid = m2.needid
  where n2.CR = @dpth
);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error when compiling with the SQLITE_OMIT_WALL directive

[Adolfo Jiménez Millán]

Sorry I mean MS V.Studio C++ 2008 (not 98)

--
A.J.Millán


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

[sqlite] Error when compiling with the SQLITE_OMIT_WALL directive

[Adolfo Jiménez Millán]

Hi all:

Using MS VC++ 98, I try to compile the 3.7.9 amalgamation, using the 
SQLITE_OMIT_WAL directive, but then I get an error:
 
sqlite3.c(131886) : error C2129: static function 'void 
sqlite3PagerClearCache(Pager *)' declared but not defined.

I've used some other directives, but all of them compiles smoothly.

Any clue?

A.J.Millán.


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

[sqlite] fsync of directories on AIX

[Alaric Snell-Pym]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


Hello there!

In os_unix.c, the following logic appears in unixSync:

  /* Also fsync the directory containing the file if the DIRSYNC flag
  ** is set.  This is a one-time occurrance.  Many systems (examples: AIX)
  ** are unable to fsync a directory, so ignore errors on the fsync.
  */
  if( pFile->ctrlFlags & UNIXFILE_DIRSYNC ){
int dirfd;
OSTRACE(("DIRSYNC %s (have_fullfsync=%d fullsync=%d)\n", pFile->zPath,
HAVE_FULLFSYNC, isFullsync));
rc = osOpenDirectory(pFile->zPath, );
if( rc==SQLITE_OK && dirfd>=0 ){
  full_fsync(dirfd, 0, 0);
  robust_close(pFile, dirfd, __LINE__);
}else if( rc==SQLITE_CANTOPEN ){
  rc = SQLITE_OK;
}
pFile->ctrlFlags &= ~UNIXFILE_DIRSYNC;
  }
  return rc;

This is all well and good, as indeed, fsync on directories often fails
on AIX (although, amusingly, it sometimes succeeds as well).

However, unixDelete has the following logic:

#ifndef SQLITE_DISABLE_DIRSYNC
  if( dirSync ){
int fd;
rc = osOpenDirectory(zPath, );
if( rc==SQLITE_OK ){
#if OS_VXWORKS
  if( fsync(fd)==-1 )
#else
  if( fsync(fd) )
#endif
  {
rc = unixLogError(SQLITE_IOERR_DIR_FSYNC, "fsync", zPath);
  }
  robust_close(0, fd, __LINE__);
}else if( rc==SQLITE_CANTOPEN ){
  rc = SQLITE_OK;
}
  }
#endif
  return rc;

Unfortunately, this does not ignore directory fsync errors. This means
that committing on AIX will fail unless the directory sync succeeded.

As far as we can tell, fsync on a dir on AIX succeeds if no other files
have been created in the directory "recently" (although we're not sure
how recent that has to be), and fails with EBADF otherwise!

Therefore, I respectfully suggest that unixDelete be altered to silently
ignore errors on directory fsyncs!

I can't find anywhere else that fsync is called on directories.

Thanks,

ABS

- --
Alaric Snell-Pym
http://www.snell-pym.org.uk/alaric/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk7WApEACgkQRgz/WHNxCGqhewCgk+sYT9Dkwc+vq3Z0S8SJD0eb
05IAn3Rxol3qzNKju7/hLeQPRKISW28t
=oVaA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] WAL file size

[Sreekumar TP]

Hello,

I have sqlite configure to do manual checkpoint. I do checkpoint every few
hundred records. I expect the WAL to reduce in size and become zero when
there are no more inserts done.
However, I see that the WAL file size is static and does not reduce in
size.  Why isnt manual checkpoint reducing the size of WAL file.

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

Re: [sqlite] XML import/export t/from sqlite

[Simon Slavin]

On 30 Nov 2011, at 8:33am, Christoph P.U. Kukulies wrote:

> Does anybody know of additions to SQLITE (.NET) to provide for XML 
> import/export.

SQLITE is just the bit that talks to SQLite databases.  You can find some other 
.NET library which does XML import/export and write some programming to make 
the two work together.

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

[sqlite] XML import/export t/from sqlite

[Christoph P.U. Kukulies]

Does anybody know of additions to SQLITE (.NET) to provide for XML 
import/export.

TIA

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