Re: [sqlite] Which pragmas are persistent?

[John G]

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

> -Original Message- From: no...@null.net
>
>> 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Which pragmas are persistent?

[Tony Papadimitriou]

-Original Message- 
From: no...@null.net 


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

Re: [sqlite] Which pragmas are persistent?

[Clemens Ladisch]

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

Re: [sqlite] Which pragmas are persistent?

[Jens Alfke]

> On Apr 12, 2017, at 1:01 PM, Simon Slavin  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Which pragmas are persistent?

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



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-boun...@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-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Which pragmas are persistent?

[Simon Slavin]

On 12 Apr 2017, at 8:29pm, Jens Alfke  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Which pragmas are persistent?

[Richard Hipp]

On 4/12/17, Jens Alfke  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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

[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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users