[sqlite] Which pragmas are persistent?

[Jens Alfke]

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

[Richard Hipp]

Post by Jens Alfke
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
auto_vacuum, page_size — Persistent?
journal_size_limit, synchronous — Ephemeral?

Yes. Which other PRAGMAs are you interested in?

Post by Jens Alfke
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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[n***@null.net]

Post by Richard Hipp

Post by Jens Alfke
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
auto_vacuum, page_size — Persistent?
journal_size_limit, synchronous — Ephemeral?

Yes. Which other PRAGMAs are you interested in?

Post by Jens Alfke
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
...

[Tony Papadimitriou]

-----Original Message-----

Post by n***@null.net
What would be useful (at least via the shell CLI) is a "list_pragmas"
sqlite> PRAGMA list_pragmas;

PRAGMA list;

would be less redundant.

[John G]

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

John G

Post by Tony Papadimitriou

Post by n***@null.net
What would be useful (at least via the shell CLI) is a "list_pragmas"
sqlite> PRAGMA list_pragmas;

PRAGMA list;
would be less redundant.
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[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:sqlite-users-***@mailinglists.sqlite.org] 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

Post by Tony Papadimitriou

Post by n***@null.net
What would be useful (at least via the shell CLI) is a "list_pragmas"
sqlite> PRAGMA list_pragmas;

PRAGMA list;
would be less redundant.
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

Post by Jens Alfke
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.

[Jens Alfke]

Post by Simon Slavin

Post by Jens Alfke
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.)

Post by Simon Slavin
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

[David Raymond]

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:sqlite-users-***@mailinglists.sqlite.org] 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
sqlite-***@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Clemens Ladisch]

Post by Jens Alfke
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