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 [link] [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 [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>