SQLite Forum

max page count doesn't return sqlite_full error when reaches the limit of page count
Login

max page count doesn't return sqlite_full error when reaches the limit of page count

(1) By Deepanshi Zood (DeepanshiZood) on 2020-07-02 12:45:54 [link] [source]

Hi,

I am trying to limit the DB file size using max_page_count on an open connection. 
For the same, I tried the following configurations before the creation of the table:

page_size = 512
max_page_count = 1

Run the vacuum command also to make the changes effective.

As per my understanding, it should return error SQLITE_FULL once 512 bytes are written in DB and I try to insert another byte.

However, I am not getting any such result.
Can anybody help me with that why the page_count parameter is not working as expected?

(2) By David Raymond (dvdraymond) on 2020-07-02 13:02:55 in reply to 1 [source]

Are you opening an existing file? Page size can only be run either before the file has actually been written, or right before a vacuum to change the page size. If you've opened an existing file, pragma page_size on its own won't do anything unless immediately followed by vacuum.

From the pragma documentation: "The maximum page count cannot be reduced below the current database size." What are you getting when you do your max_page_count pragma?

What following commands are you trying which are succeeding?


SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> pragma page_size = 512;
sqlite> pragma max_page_count = 1;
1
sqlite> create table foo (a text);
Error: database or disk is full
sqlite>

(3.1) By Deepanshi Zood (DeepanshiZood) on 2020-07-03 08:17:14 edited from 3.0 in reply to 2 [link] [source]

Thanks for your reply David, 

I was executing the pragma with the DB alias name like

$ sqlite3 test.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite>
sqlite> attach database 'test' as test;
sqlite> pragma test.page_size = 512;
sqlite> pragma test.max_page_count = 1;
1
sqlite>

However, without the DB alias, I can see the command is a success.

Could you please let me know why it doesn't work with aliases or specific database as I believe that "pragma max_page_count = 1;" might be setting the limit on main DB.

I am worried if I have multiple databases, and want to restrict the file size on a few of them only.

(4) By David Raymond (dvdraymond) on 2020-07-07 20:21:53 in reply to 3.1 [link] [source]

It seems to be working ok. Below you can see me attaching a new database and limiting its page count. After limiting it, when I try to create a new table in the attached database it gives an error. When I try to make a new table in the main database it succeeds. And you can see that it keeps the max_page_count separate for each database.

SQLite version 3.32.3 2020-06-18 14:00:33
Enter ".help" for usage hints.
sqlite> .header on
sqlite> create table t1 (foo text);
sqlite> create table t2 (foo text);
sqlite> pragma page_count;
page_count
3
sqlite> attach database 'db2.sqlite' as test;
sqlite> create table test.t1 (foo text);
sqlite> pragma test.page_count;
page_count
2
sqlite> pragma test.max_page_count;
max_page_count
1073741823
sqlite> pragma test.max_page_count = 2;
max_page_count
2
sqlite> create table test.t2 (foo text);
Error: database or disk is full
sqlite> create table t3 (foo text);
sqlite> pragma main.page_count;
page_count
4
sqlite> pragma main.max_page_count;
max_page_count
1073741823
sqlite> pragma test.max_page_count;
max_page_count
2
sqlite>