I probably misundertood something about SAVEPOINTS in SQLite.
I am using C API in an application, where we run a bunch of inserts, and in case something fails, we should give up on all the inserts alltogether.
I am aware I could BEGIN TRANSACTION for such a simple scenario, but I fear that the scenario could get a more complicated, and nesting might become a requirement, that's why I went for SAVEPOINTS.
Anyway, here is an extract of SQL statements I run:
SQL> SAVEPOINT SAVEPOINT_20170524_172706;
SQL> INSERT, SELECT STATEMENT (no COMMIT or END TRANSACTION)
SQL> ROLLBACK TO SAVEPOINT SAVEPOINT_20170524_172706;
SQL> RELEASE SAVEPOINT_20170524_172706;
Basically I create a new savepoint based on the timestamp, before I start inserting and selecting data from the database. Then one operation fails and I need to bail out, so I rollback to the savepoint I just created. In the end I want to get rid of the savepoint I wont need anymore, since I dont want to clutter the database with useless savepoints, hence I ran RELEASE . In this case I find myself with the database filled with all the data inserted by statements that were supposed to be rolled back.
If I dont execute the RELEASE statement, then the database looks just fine, but I wonder what happens with the abandoned SAVEPOINT which will never be referenced anymore.
Which wrong assumption am I making? What happens to SAVEPOINTS if I dont release them, are they going to be 'dropped' as I close the 'connection' to the DB file?
I had PRAGMA journal_mode = OFF