I may be reading different documentation (or perhaps just misreading it). https://www.sqlite.org/autoinc.html says (for the no AUTOINCREMENT case, which applies to the original post) "If no ROWID is specified on the insert ... [the] usual algorithm is to give the newly created row a ROWID that is one larger than the largest ROWID in the table prior to the insert."
And goes on to strongly imply that "not usual" means either the table is currently empty, or the largest ROWID is already at the largest possible integer. The implication is pretty strong that leaving ROWID unspecified on an insert is equivalent (in the usual case) to specifying MAX(ROWID) + 1. I understand the difference between "documenting some details about how things currently happen to behave" and "documenting the intended behavior." When I read about the -journal or -wal files, I'm usually happy with the thought that in the future SQLite may support (or use exclusively) an entirely different rollback mechanism. It isn't entirely obvious which category autoinc.html falls into, but I believe that a reasonable reader can interpret that as documenting intended behavior. Best Regards, Bill -----Original Message----- From: Richard Hipp [mailto:d...@sqlite.org] Sent: Wednesday, December 09, 2015 7:07 AM To: SQLite mailing list Subject: Re: [sqlite] INSERT OR IGNORE consuming too many rowids As others have a pointed out, though, the "desired" behavior is "undocumented". SQLite nor any other SQL database engine is obligated to provide the behavior you want. It happens as you like purely by chance. Do not depend on this behavior since it might change at any moment, without warning. ************************************************************************************** This e-mail and any attachments thereto may contain confidential information and/or information protected by intellectual property rights for the exclusive attention of the intended addressees named above. If you have received this transmission in error, please immediately notify the sender by return e-mail and delete this message and its attachments. Unauthorized use, copying or further full or partial distribution of this e-mail or its contents is prohibited. **************************************************************************************