[sqlite] sqlite ext3 settings

[Andrii Motsok]

Hi,


I have sqlite database on top of ext3 (on ssd).


Does

  1) ext3 parameters (data=writeback|ordered,barrier=0|1, etc)

  2) drive write-caching setting (hdparm -W) value

  3) ???

have impact on the possibility of losing data due to power loss? If yes which 
parameters should be used to achieve maximum safety against loosing of the data 
due to power loss during writing to WAL database?

  3) Are there any other settings (system, sqlite, etc) which could increase 
protection against data corruption after power lost or system crash?


Regards,

Andrii

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

Re: [sqlite] Default ordering of SELECT query

[Andrii Motsok]

>On 2016/10/05 5:27 PM, Andrii Motsok wrote:

>> Date: Mon, 3 Oct 2016 16:25:09 +0200
>>
>>
>> Hi,
>>
>> 1) In case of ORDER BY if we choose ordering which does not follow "index 
>> ordering" we will get performance degradation.
>> 2) If we use ORDER BY can we be sure that order of rows is the same for two 
>> subsequent calls (without any writes to database) for non UNIQUE index?
>> 3) Just from curiosity - could you please provide any real world scenario 
>> which shows how two similar subsequent calls can return different order of 
>> rows?
>>
>> Regards,
>> Andrii

>1) - How do you mean "follow index ordering"? If you order (Ascending or
>descending) on fields that are contained in an index, you will get full
>performance.
>
>2) No you can't be sure, well, currently that should be the case because
>the QP and sort orderer won't change algorithms between two sorts, but
>this is a very unsafe assumption. Any DB update or insert or vacuum or
>analyze might alter the row order produced where the ordering isn't
>explicit.
>
>3) We can show you what /might/ produce different row orders, but I
>don't know how to show what will definitely guarantee a different order.
>For the same reason it is unsafe to "assume" a static ordering, I can
>also not "assume" a different ordering unless specifically asking the
>sorter to adhere to another ordering. Point is, if you depend on the
>order, you have to specify it precisely. You can always order by more
>than 1 column, or even a function on a column. Why not simply Order by
>your non-unique field AND then by row_id. This will guarantee the same
>order.
>SELECT * FROM t ORDER BY t.NonUniqueField, t.row_id
>(That is, unless you are using WITHOUT ROWID tables, in which case use
>the PK.)

>>> How do you mean "follow index ordering"?
This is only my observation that sqlite usually returns rows in the order in 
which they are sorted in by index. So if I have two indexes and SELECT with 
WHERE followed by AND choice of indexes can be different. This choice depends 
on query planner and statistics. So there is always a chance that ORDER BY will 
require additional sorting.

>>>Any DB update or insert or vacuum or analyze might alter the row order 
>>>produced where the ordering isn't explicit.
I am interested only in the same ordering between two subsequent query 
executions. The things which can happen in between are:
 * checkpointing
 * close/open in with different journal mode: DELETE->WAL/WAL->DELETE

>>>Why not simply Order by your non-unique field AND then by row_id
Unfortunately this is not simple. We don't have row_id. I am the library on top 
of sqlite which accepts and executes SQL queries. I cannot force my users to 
add ORDER BY to all queries especially for non UNIQUE indexes. And I need to do 
some work under cover (checkpoint and reopen in DELETE mode) and then I need to 
restore queries statues to their previous position.

Regards,
Andrii




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

[sqlite] transaction during reading on read-only database

[Andrii Motsok]

Hi,


My understanding is that any reading from database automatically starts read 
transaction. The question: why does sqlite need to do it on read-only database?


Regards,

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

Re: [sqlite] Default ordering of SELECT query (Clemens Ladisch)

[Andrii Motsok]

Date: Mon, 3 Oct 2016 16:25:09 +0200

From: Clemens Ladisch <clem...@ladisch.de>
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Default ordering of SELECT query
Message-ID: <dc6a07e2-2aed-7512-0483-77c1fd204...@ladisch.de>
Content-Type: text/plain; charset=utf-8

>>Andrii Motsok wrote:
>> "If a SELECT statement that returns more than one row does not have an ORDER 
>> BY clause, the order in which the rows are returned is undefined"
>>
>> We have scenario:
>>  * database is opened in WAL mode
>>  * 10 rows have been read to container1
>>  * database has been checkpointed
>>  * database has been closed
>>  * database has been opened on DELETE mode
>>  * 10 rows have been read to container2
>>
>> Can we assume that container1 == container2 ?

>This assumption happens to be true in the current version of SQLite, if no
>other changes are being made to the database before re-opening it.

>But to assume this is extremely dangerous, because you don't know how your
>application will be changed in the future.  Just use ORDER BY.


>Regards,
>Clemens

Hi,

1) In case of ORDER BY if we choose ordering which does not follow "index 
ordering" we will get performance degradation.
2) If we use ORDER BY can we be sure that order of rows is the same for two 
subsequent calls (without any writes to database) for non UNIQUE index?
3) Just from curiosity - could you please provide any real world scenario which 
shows how two similar subsequent calls can return different order of rows?

Regards,
Andrii

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

[sqlite] Default ordering of SELECT query

[Andrii Motsok]

Hi,


Sqlite man (https://www.sqlite.org/lang_select.html)

[https://sqlite.org/images/syntax/select-stmt.gif]

SQLite Query Language: SELECT
www.sqlite.org
The SELECT statement is used to query the database. The result of a SELECT is 
zero or more rows of data where each row has a fixed number of columns.

says: "If a SELECT statement that returns more than one row does not have an 
ORDER BY clause, the order in which the rows are returned is undefined"

There is a question:

We have scenario:
 * database is opened in WAL mode
 * 10 rows have been read to container1
 * database has been checkpointed
 * database has been closed
 * database has been opened on DELETE mode
 * 10 rows have been read to container2

Can we assume that container1 == container2 ? Or in another words can we assume 
that order of rows is same in this scenario?


Regards,
Andrii

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

Re: [sqlite] switching from WAL to DELETE mode (Clemens Ladisch)

[Andrii Motsok]

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, a

[sqlite] switching from WAL to DELETE mode

[Andrii Motsok]

Hi,


Is that possible to switch database from WAL to DELETE mode when holding more 
than one connection? If no, which workaround could we use to omit closing of 
'all-except-one' connections?


Regards,

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