Which pragmas are persistent?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Which pragmas are persistent?

Jens Alfke-2
Many of SQLite's pragma commands change database settings. It would be helpful if their documentation[1] stated which of these persist across closing/reopening the database, and which are scoped only to the open connection. For example, the docs say that that 'pragma journal_mode=WAL' is persistent. Presumably 'mmap_size' is not because it doesn't have any effect on file storage. But there are some I'm unsure about:

auto_vacuum, page_size — Persistent?
journal_size_limit, synchronous — Ephemeral?

I'm asking because I'd like to know which pragmas I need to reissue every time the database connection is opened, and which I only need to issue when initializing a new database.

—Jens

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

Re: Which pragmas are persistent?

Richard Hipp-3
On 4/12/17, Jens Alfke <[hidden email]> wrote:
> Many of SQLite's pragma commands change database settings. It would be
> helpful if their documentation[1] stated which of these persist across
> closing/reopening the database, and which are scoped only to the open
> connection. For example, the docs say that that 'pragma journal_mode=WAL' is
> persistent. Presumably 'mmap_size' is not because it doesn't have any effect
> on file storage. But there are some I'm unsure about:
>
> auto_vacuum, page_size — Persistent?
> journal_size_limit, synchronous — Ephemeral?

Yes.  Which other PRAGMAs are you interested in?

>
> I'm asking because I'd like to know which pragmas I need to reissue every
> time the database connection is opened, and which I only need to issue when
> initializing a new database.
>
> —Jens
>
> [1]: http://www.sqlite.org/pragma.html
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: Which pragmas are persistent?

Simon Slavin-3
In reply to this post by Jens Alfke-2

On 12 Apr 2017, at 8:29pm, Jens Alfke <[hidden email]> wrote:

> which I only need to issue when initializing a new database.

'need' is a bit strong.  I have never worried about pagesize in a database.  I’ve always just left it at the default for whatever platform I’m using when I create the database.  But I don’t write programs where SQLite speed is a bottleneck.

I have previous argued for category documention for PRAGMAs: which ones are stored in the database, which ones just tell you things, etc..

The only pragma needed anew for each connection to the database is

        PRAGMA busy_timeout = milliseconds

I might argue that given what it does, this should really be saved in the database file somewhere.

The rest

* just tell you things, have no lasting effect
* are single-use tools which change the database file in a one-time way
* are saved in the database file (e.g. journal mode = WAL, page size)
* can reasonably be different for different concurrent connections to the database (some poll for changes, others log changes)

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

Re: Which pragmas are persistent?

David Raymond
In reply to this post by Jens Alfke-2
I recommend taking a look at
http://www.sqlite.org/fileformat2.html
and look at 1.2 The Database Header. Since everything's stored in the file, the permanent pragmas are going to be ones which change one of the values in there.

<Not complete lists>

Permanent:
page_size (change requires vacuum)
auto_vacuum (change to or from incremental requires vacuum)
journal_mode (only WAL vs non-WAL)
legacy_file_format (think can only be changed at database creation)
encoding (think can only be changed at database creation)
user_version

Connection lifespan only:
(Also note this means any of these will not affect any other connections)
foreign_keys (biggest one to remember I think)
journal_mode (only if between the non-WAL types)
synchronous
automatic_index
busy_timeout
cache_size
case_sensitive_like
defer_foreign_keys
ignore_check_constraints
locking_mode
max_page_count
mmap_size
recursive_triggers
reverse_unordered_selects
etc, etc...

Please correct me if I messed up.

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Jens Alfke
Sent: Wednesday, April 12, 2017 3:29 PM
To: SQLite mailing list
Subject: [sqlite] Which pragmas are persistent?

Many of SQLite's pragma commands change database settings. It would be helpful if their documentation[1] stated which of these persist across closing/reopening the database, and which are scoped only to the open connection. For example, the docs say that that 'pragma journal_mode=WAL' is persistent. Presumably 'mmap_size' is not because it doesn't have any effect on file storage. But there are some I'm unsure about:

auto_vacuum, page_size — Persistent?
journal_size_limit, synchronous — Ephemeral?

I'm asking because I'd like to know which pragmas I need to reissue every time the database connection is opened, and which I only need to issue when initializing a new database.

—Jens

[1]: http://www.sqlite.org/pragma.html
_______________________________________________
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
|

Re: Which pragmas are persistent?

Jens Alfke-2
In reply to this post by Simon Slavin-3

> On Apr 12, 2017, at 1:01 PM, Simon Slavin <[hidden email]> wrote:
>
>> which I only need to issue when initializing a new database.
>
> 'need' is a bit strong.  I have never worried about pagesize in a database.  I’ve always just left it at the default for whatever platform I’m using when I create the database.  But I don’t write programs where SQLite speed is a bottleneck.

I didn't mean 'need' quite so strongly; it was more like "…which I don't need to issue when reopening an existing database".

The discussion of the page-size change in 3.12.0 says that "on modern hardware, a 4096 byte page is a faster and better choice", and performance is important to our code, so it seemed worth it to bump the page size. (We still support SQLite versions older than 3.12.)

> I have previous argued for category documention for PRAGMAs: which ones are stored in the database, which ones just tell you things, etc..

Yes, that would be great.

—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
|

Re: Which pragmas are persistent?

Clemens Ladisch
In reply to this post by Jens Alfke-2
Jens Alfke wrote:
> I'd like to know which pragmas I need to reissue every time the
> database connection is opened, and which I only need to issue when
> initializing a new database.

http://stackoverflow.com/documentation/sqlite/5223/pragma-statements/18507/pragmas-with-permanent-effects
says the following are permanent:

    application_id
    journal_mode (when enabling or disabling WAL mode)
    schema_version
    user_version
    wal_checkpoint

and these need to be set before creating the database:

    auto_vacuum
    encoding
    legacy_file_format
    page_size


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Which pragmas are persistent?

nomad
In reply to this post by Richard Hipp-3
On Wed Apr 12, 2017 at 03:38:11PM -0400, Richard Hipp wrote:

> On 4/12/17, Jens Alfke <[hidden email]> wrote:
> > Many of SQLite's pragma commands change database settings. It would be
> > helpful if their documentation[1] stated which of these persist across
> > closing/reopening the database, and which are scoped only to the open
> > connection. For example, the docs say that that 'pragma journal_mode=WAL' is
> > persistent. Presumably 'mmap_size' is not because it doesn't have any effect
> > on file storage. But there are some I'm unsure about:
> >
> > auto_vacuum, page_size — Persistent?
> > journal_size_limit, synchronous — Ephemeral?
>
> Yes.  Which other PRAGMAs are you interested in?
>
> >
> > I'm asking because I'd like to know which pragmas I need to reissue every
> > time the database connection is opened, and which I only need to issue when
> > initializing a new database.

What would be useful (at least via the shell CLI) is a "list_pragmas"
pragma that shows for example something like this:

    sqlite> PRAGMA list_pragmas;
    pragma          writeable   default     current     permanent
    ------          ---------   -------     -------     ---------
    application_id  yes         0           0           yes
    auto_vacuum     yes         0           0           yes
    automatic_index yes         1           1           yes
    busy_timeout    yes         0           0           no
    ...
    table_info      no          (complex)   (complex)   n/a
    ...


--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Which pragmas are persistent?

Tony Papadimitriou
-----Original Message-----
From: [hidden email]

>What would be useful (at least via the shell CLI) is a "list_pragmas"
>pragma that shows for example something like this:
>
>    sqlite> PRAGMA list_pragmas;

PRAGMA list;

would be less redundant.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Which pragmas are persistent?

John Gillespie-2
PRAGMA foreign_keys=1   is  transient, but it would be nice if it were
persistent.

John G

On 13 April 2017 at 12:35, Tony Papadimitriou <[hidden email]> wrote:

> -----Original Message----- From: [hidden email]
>
>> What would be useful (at least via the shell CLI) is a "list_pragmas"
>> pragma that shows for example something like this:
>>
>>    sqlite> PRAGMA list_pragmas;
>>
>
> PRAGMA list;
>
> would be less redundant.
>
> _______________________________________________
> 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
|

Re: Which pragmas are persistent?

David Raymond
I agree that something like that would be nice, at this point I think it's just backwards compatability issues preventing that.

The best you can do at the moment is to compile anything you use/write yourself with...

SQLITE_DEFAULT_FOREIGN_KEYS=1

...but if you're not in a situation where you have full control over who accesses your databases and with what tool version, then that's only going to help prevent you from messing it up by accident, and not others.

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of John G
Sent: Friday, April 21, 2017 1:40 PM
To: SQLite mailing list
Subject: Re: [sqlite] Which pragmas are persistent?

PRAGMA foreign_keys=1   is  transient, but it would be nice if it were
persistent.

John G

On 13 April 2017 at 12:35, Tony Papadimitriou <[hidden email]> wrote:

> -----Original Message----- From: [hidden email]
>
>> What would be useful (at least via the shell CLI) is a "list_pragmas"
>> pragma that shows for example something like this:
>>
>>    sqlite> PRAGMA list_pragmas;
>>
>
> PRAGMA list;
>
> would be less redundant.
>
> _______________________________________________
> 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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users