Dear SQL Developers / Users, I am using Sqlite3 in our project. My database has journal_mode set to WAL and has one writer & multiple readers. During normal mode of operation, only writing process is accessing database and there is no active reader connection I have observed that when I don't have any primary key in the database table then WAL auto_checkpoint doesn't work and WAL file keeps growing without any bound. At one point, it consumes all the available memory in the system.
I have tried different approaches to fix this issue like setting up SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT = 0, setting SQLITE_DEFAULT_WAL_AUTOCHECKPOINT = 100 etc. but nothing worked. I have confirmed in my code that auto_checkpoint is not disabled. I have referred to information on the link https://www.sqlite.org/wal.html#avoiding_excessively_large_wal_files too and made sure that any of these cases are not happening with my code Another approach, I have tried is that to manually checkpoint WAL file in another process at regular interval. This seems to work for me as WAL file doesn't grow beyond specified limit. But I don't want to do manual checkpoint in my code I want to know why auto_checkpoint is not working when there is no Primary Key in the table. FYI, when I make first column as Primary Key in the table then auto_checkpoint starts working with the same code. That's why I am doubting some relation between auto_checkpoint and Primary Key. I checked the documentation on https://www.sqlite.org/wal.html#ckpt and didn't find any reference of Primary Key and WAL file. I request to please share what could be the reason for auto_checkpoint not working in WAL mode. I will appreciate help on this. Thanks for your help in advance. Regards, Tanuj Sharma _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
