sqlite session extension and conflict rollback

Previous Topic Next Topic
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view

sqlite session extension and conflict rollback

Hello everyone,

Does anyone know of any additional documentation or good examples of the
session extension usage?

I am probably using it in an odd way, but I encountered something that
makes perfect sense but was surprising originally.  Basically if you use
the session extension and apply a changeset to a table with a column
created with ON CONFLICT ROLLBACK then even if the apply succeeds properly
(OMIT/REPLACE) the call will always return an error if any conflicts occur
as the ON CONFLICT ROLLBACK causes all pending SAVESTATEs to be rolled back
including the changeset_apply savestate, as such the changeset_apply will
no longer exist when the call to RELEASE it is made.

I am using sqlite via C# and a slightly modified fork of sqlite-net (
https://github.com/praeclarum/sqlite-net) along with a pinvoke wrapper
[time permitting to I will see if it is wanted upstream] for the session
extension.  I have a table in which I set the primary key to ON CONFLICT
ROLLBACK and proceed to insert rows into the table.  I have a replica of
the database, using the session module I create a change set of the
differences between the two databases and apply [in the wrong direction]
the change set which will cause a conflict for each difference and allows
my conflict handler to determine if the change should be replicated (which
is performed via ignoring the change to keep current, or INSERT, DELETE, or
UPDATE as appropriate).  I have unit tests which create a simple database
and replica then proceed to make changes and replicate, which works fine
(uses default ON CONFLICT ABORT, i.e. not specified).  However, if the
database uses the ROLLBACK mode then in the session extension within
sessionChangesetApply() the "SAVEPOINT changeset_apply" will be released on
the first conflict (regardless of how it is handled by the user's conflict
handler) and later in the sessionChangesetApply() function the following
line "rc = sqlite3_exec(db, "RELEASE changeset_apply", 0, 0, 0);" will
always fail as 'changeset_apply' no longer exists and so
sqlite3changeset_apply will always fail if there are any conflicts.  The
reason this occurs is within sqlite3VdbeHalt() if the conflict action
[p->errorAction] isn't one of SQLITE_OK, OE_Fail, or OE_Abort then all open
save points are closed, including the 'changeset_apply' one.

SQLITE_OK  || p->errorAction==OE_Fail ){
        eStatementOp = SAVEPOINT_RELEASE;
      }else if( p->errorAction==OE_Abort ){
        eStatementOp = SAVEPOINT_ROLLBACK;
        sqlite3RollbackAll(db, SQLITE_ABORT_ROLLBACK);
        sqlite3CloseSavepoints(db);   <<---

The document session/sqlite3changeset_apply.html states "All changes made
by this function are enclosed in a savepoint transaction. If any other
error (aside from a constraint failure when attempting to write to the
target database) occurs, then the savepoint transaction is rolled back,
restoring the target database to its original state, and an SQLite error
code returned." isn't completely accurate, as an error is also returned on
constraint failure and the constraint causes automatic ROLLBACK.

On reflection (and re-reading that INSERT OR ROLLBACK is basically a per
statement form of [not valid] INSERT ON CONFLICT ROLLBACK), when using
session extension the table schema should probably not use ON CONFLICT
ROLLBACK, but if needed for a particular scenario then I believe the

Hopefully, as my searches did not turn up much outside of the official
documentation on the SESSION extension (probably a bit of my part using bad
search criteria and its hard to search for 'session'), someone here can
direct me to either better search terms or some additional
documentation/usage of the session extension and maybe my explanation will
help someone in the future realize why the call always fails when nothing
looks wrong.

Thank you,
Kenneth J. Davis
[hidden email]
sqlite-users mailing list
[hidden email]