Ambiguity in 'pragma page_size' docs

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Ambiguity in 'pragma page_size' docs

Jens Alfke-2
The docs[1] say that:

> The page_size pragma will only set in the page size if it is issued before any other SQL statements that cause I/O against the database file.

It's not stated explicitly, but I believe the pragma has to be issued before any other statement _ever_ causes I/O, i.e. it has to be the very first persistent statement ever used on a new database file. (Except for the situation regarding VACUUM described later on.) Is this true?

(Also, the word "in" is a typo that should be removed from that sentence.)

> SQL statements that cause I/O against the database file include "CREATE", "SELECT", "BEGIN IMMEDIATE", and "PRAGMA journal_mode=WAL".

I'm guessing that "PRAGMA user_version" also causes I/O. The implication is that, if you want to set the page_size, you have to do it every time the database is opened, not just on first-time initialization; because checking to see if you've initialized the database already would trigger I/O which will make setting the page_size a no-op. (Unless you use the filesystem to check that the db file is missing or empty before opening it, I suppose.)

—Jens

[1]: http://www.sqlite.org/pragma.html#pragma_page_size
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Ambiguity in 'pragma page_size' docs

Richard Hipp-3
On 4/12/17, Jens Alfke <[hidden email]> wrote:
> The docs[1] say that:
>
>> The page_size pragma will only set in the page size if it is issued before
>> any other SQL statements that cause I/O against the database file.
>
> It's not stated explicitly, but I believe the pragma has to be issued before
> any other statement _ever_ causes I/O, i.e. it has to be the very first
> persistent statement ever used on a new database file. (Except for the
> situation regarding VACUUM described later on.) Is this true?

I think the best thing to do is:  "PRAGMA page_size=4096; VACUUM;"  --
all in a single call to sqlite3_exec().  But only do this when you
really need to change the page size as it is expensive.

To a first approximation, you never need to change the page size.  So
why is this important to you?

>
>> SQL statements that cause I/O against the database file include "CREATE",
>> "SELECT", "BEGIN IMMEDIATE", and "PRAGMA journal_mode=WAL".
>
> I'm guessing that "PRAGMA user_version" also causes I/O.
>

Yes

>
> The implication is
> that, if you want to set the page_size, you have to do it every time the
> database is opened, not just on first-time initialization; because checking
> to see if you've initialized the database already would trigger I/O which
> will make setting the page_size a no-op. (Unless you use the filesystem to
> check that the db file is missing or empty before opening it, I suppose.)
>

The implication *should* be that you *never* change the page size.
SQLite will pick a good page size for you automatically, which works
in 99.99% of all cases.

In the exceptional case where you do need to adjust the page_size
manually, do it exactly once, as if you are running CREATE INDEX.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Ambiguity in 'pragma page_size' docs

Jens Alfke-2

> On Apr 12, 2017, at 12:53 PM, Richard Hipp <[hidden email]> wrote:
>
> The implication *should* be that you *never* change the page size.
> SQLite will pick a good page size for you automatically, which works
> in 99.99% of all cases.

… provided you're running 3.12 or later. Prior to that it would pick 1024, which the docs suggest is non-optimal. (I'm sure it's not a big difference, but if I can glean some performance by adding one line of code, hey, I'm doing it.)

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Ambiguity in 'pragma page_size' docs

David Raymond
In reply to this post by Richard Hipp-3
I think there're two questions going on here. (Though I'm probably adding on to the original question)

First is similar to a recent thread and is: When I open a connection, what's the best way to ensure I've connected to an existing file, and am not about to create a brand new one by trying to find out? (In SQLite and apart from external programming language file system commands anyway)

The other one is: What do I need to explicitly set, and in what order when I'm making a new database? For me, for example, I use SQLite in Python, so while I know what's on my own computer, when I hand off the scripts to the world at large who knows what version or compilation options their Python installations' sqlite3.dll has in it. So of page_size, auto_vacuum, legacy_file_format, encoding, journal_mode, etc. what pragmas do I need to set and in what order to make sure it creates what I want? For example as stated, if you do journal_mode = wal before you set page_size, then you may not notice that page_size didn't get set because the file had been created at the journal_mode change.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Richard Hipp
Sent: Wednesday, April 12, 2017 3:53 PM
To: SQLite mailing list
Subject: Re: [sqlite] Ambiguity in 'pragma page_size' docs

On 4/12/17, Jens Alfke <[hidden email]> wrote:
> The docs[1] say that:
>
>> The page_size pragma will only set in the page size if it is issued before
>> any other SQL statements that cause I/O against the database file.
>
> It's not stated explicitly, but I believe the pragma has to be issued before
> any other statement _ever_ causes I/O, i.e. it has to be the very first
> persistent statement ever used on a new database file. (Except for the
> situation regarding VACUUM described later on.) Is this true?

I think the best thing to do is:  "PRAGMA page_size=4096; VACUUM;"  --
all in a single call to sqlite3_exec().  But only do this when you
really need to change the page size as it is expensive.

To a first approximation, you never need to change the page size.  So
why is this important to you?

>
>> SQL statements that cause I/O against the database file include "CREATE",
>> "SELECT", "BEGIN IMMEDIATE", and "PRAGMA journal_mode=WAL".
>
> I'm guessing that "PRAGMA user_version" also causes I/O.
>

Yes

>
> The implication is
> that, if you want to set the page_size, you have to do it every time the
> database is opened, not just on first-time initialization; because checking
> to see if you've initialized the database already would trigger I/O which
> will make setting the page_size a no-op. (Unless you use the filesystem to
> check that the db file is missing or empty before opening it, I suppose.)
>

The implication *should* be that you *never* change the page size.
SQLite will pick a good page size for you automatically, which works
in 99.99% of all cases.

In the exceptional case where you do need to adjust the page_size
manually, do it exactly once, as if you are running CREATE INDEX.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Ambiguity in 'pragma page_size' docs

Richard Hipp-3
On 4/12/17, David Raymond <[hidden email]> wrote:

>
> When I open a connection, what's
> the best way to ensure I've connected to an existing file, and am not about
> to create a brand new one by trying to find out?

Use sqlite3_open_v2() to create the database connection and make sure the
flag parameter is SQLITE_OPEN_READWRITE or SQLITE_OPEN_READONLY and
that it does not include the SQLITE_OPEN_CREATE bit.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...