Hello all, I have some questions and comments about savepoints, documented here:
https://www.sqlite.org/lang_savepoint.html Here are some excerpts from that documentation that are relevant to my questions/comments: 1. "The SAVEPOINT command starts a new transaction with a name. The transaction names need not be unique." 2. "The ROLLBACK command with a TO clause rolls back transactions going backwards in time back to the most recent SAVEPOINT with a matching name. The SAVEPOINT with the matching name remains on the transaction stack, but all database changes that occurred after that SAVEPOINT was created are rolled back." Also (not documented on that page): 3. Savepoint names cannot be parameterized in statements, which means you cannot prepare a savepoint statement ahead of time, and supply the savepoint name at execution time. Through some experimentation (using v3.11.0), I have found that #1 and #2 together don't work very well: begin transaction; ... Work A ... savepoint Foo; ... Work B ... savepoint Foo; <--- Allowed by #1 ... Work C ... rollback to savepoint Foo; <--- Rolls back Work C, but leaves the innermost savepoint Foo active due to #2 rollback to savepoint Foo; <--- Does nothing - I can no longer rollback past the innermost Foo without rolling back the entire transaction. Questions: A) If #2 is desirable, then why allow non-unique savepoint names within the same transaction? It is impossible to rollback the innermost savepoint with a non-unique name, and later rollback past that savepoint using the same name. B) If #1 is desirable, then why doesn't ROLLBACK TO pop the matched savepoint off the stack? Even without considering #1, I'm having trouble coming up with a scenario in which I would want the matched savepoint to remain active. C) If #1 and #2 are desirable, then why can't I parameterize savepoint names? This would allow me to prepare my savepoint statements once, and execute them with different names, gaining the efficiencies of prepared statements for these oft-used actions. Currently, I am forced to generate unique savepoint names _and_ prepare new statements every time I want to create a new savepoint, which is doubly inefficient. Thoughts? -Paul _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
