On 31 Jan 2017, at 10:40pm, Warren Young <war...@etr-usa.com> wrote:
> On Jan 31, 2017, at 2:03 PM, Ward WIllats <sqlite-us...@wardco.com> wrote:
>
>> the delete sometimes (very rarely) fails with a 13 "disk or database full"
>> error. I assume because the purger is late to the party and it needs pages
>> in the WAL to be able to rollback if necessary.
>
> Is there an especially good reason you have to do this in a single shot?
>
> If you get this error, shrink the date range or pages-to-free value by half
> and try again. Repeat until it works, then repeat at that size until you’ve
> deleted as much as you need to.
It’s possible to do a DELETE in chunks, like you would use LIMIT on a SELECT.
Start by doing
SELECT rowid FROM MyTable
WHERE [whatever condition picks deletable rows]
OFFSET 1000 LIMIT 1
This gives you the 1000th row you would DELETE. Then you DELETE all the rows
up to that one:
DELETE FROM MyTable WHERE [whatever condition picks deletable rows] AND
rowid <= [whatever was returned]
then you do the SELECT again. Eventually you get nothing returned from the
SELECT and then you do the DELETE you would have done in the first place to
delete any surviving rows.
This keeps any transaction down to a maximum of 1000 rows, which puts an upper
limit on the amount of space the journal should take up.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
