Re: [sqlite] Inverted changesets and UNIQUE constraints
Hi! On 26.08.19 14:05, Simon Slavin wrote: > On 26 Aug 2019, at 12:43pm, Dan Kennedy wrote: >> When sqlite3changeset_apply() hits a UNIQUE constraint, it puts the change >> into a "retry buffer". Then, once it has attempted all changes in the >> changeset, it goes back and retries those in the retry buffer. It keeps >> retrying like this until no further progress can be made. Thanks a lot for the explanation, Dan! That's good to know. > That's clever. You'd think that in something as ordered and predictable as > SQL it wouldn't be necessary. I wonder whether there's a changeset it > doesn't work for. Indeed there is. If you replace my modification with this: UPDATE `mytable` SET `value` = 50 WHERE `id` = 1; UPDATE `mytable` SET `value` = 42 WHERE `id` = 2; UPDATE `mytable` SET `value` = 100 WHERE `id` = 1; Then it works processing forward, but the changeset does not apply anymore (because the three UPDATE's get changed into two (one per ID), and the change is not possible to do at all with only two UPDATE's). This case is not so relevant for me in practice, but nevertheless could in theory happen as well. Is there something I can do to work around that as well? For instance, can I record individual changesets for each distinct change (rather than combining them)? That will of course hurt performance, but may be ok in my situation. E.g., is it possible to use the pre-update hooks that the sessions module itself uses to start and extract a changeset right around each change? Or would that not work (e.g. due to interactions with the hooks for sessions itself) / be prohibitively expensive? Thanks! Yours, Daniel -- https://www.domob.eu/ OpenPGP: 1142 850E 6DFF 65BA 63D6 88A8 B249 2AC4 A733 0737 Namecoin: id/domob -> https://nameid.org/?name=domob -- 3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz To go: Arc-Cav-Hea-Kni-Mon-Tou signature.asc Description: OpenPGP digital signature ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inverted changesets and UNIQUE constraints
On 26 Aug 2019, at 12:43pm, Dan Kennedy wrote: > When sqlite3changeset_apply() hits a UNIQUE constraint, it puts the change > into a "retry buffer". Then, once it has attempted all changes in the > changeset, it goes back and retries those in the retry buffer. It keeps > retrying like this until no further progress can be made. That's clever. You'd think that in something as ordered and predictable as SQL it wouldn't be necessary. I wonder whether there's a changeset it doesn't work for. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inverted changesets and UNIQUE constraints
On 26/8/62 15:12, Daniel Kraft wrote: Hi! I'm using the SQLite session extension to create changesets, invert them and apply them to undo previous changes in the database. (Essentially what I need to do is persistent savepoints.) This works well so far, but I recently wondered about the interaction with UNIQUE constraints. In particular, let's say that I record a changeset of the following modification: I remove some rows from a table, and *then* I insert new rows that have the same values in UNIQUE columns as the previously removed ones. That obviously works fine because I delete first and *then* insert. However, my understanding of how the session extension works (according to its docs) is that when I invert and apply the changeset, it will *first* insert the deleted rows, and *then* delete the inserted ones. (Because it inverts each operation but not the order of them.) I created a simplified example program that performs exactly this on an in-memory database. The code (plus the shell script to build it with the flags I used) is below and can also be found on Github: https://gist.github.com/domob1812/a3b78739772a1ff0c001be6cbc600f17 (Perhaps that is easier to view than in the email.) When I run that on my system (with SQLite 3.16.2 from Debian Stretch), I get errors printed from ErrorLogger like this: E0826 10:08:16.840441 21876 test.cpp:16] SQLite error (code 2067): abort at 16 in [INSERT INTO main."mytable" VALUES(?, ?)]: UNIQUE constraint failed: mytable.value E0826 10:08:16.840520 21876 test.cpp:16] SQLite error (code 2067): abort at 16 in [INSERT INTO main."mytable" VALUES(?, ?)]: UNIQUE constraint failed: mytable.value This matches what I expect. However, it seems that applying the changeset still works fine, and I get the correct "old" state restored. Is this just "by chance", or can I safely ignore these errors in this context and rely on the ability to apply inverted changesets even if they (intermittently) violate UNIQUE constraints? They can be ignored I think. When sqlite3changeset_apply() hits a UNIQUE constraint, it puts the change into a "retry buffer". Then, once it has attempted all changes in the changeset, it goes back and retries those in the retry buffer. It keeps retrying like this until no further progress can be made. So in a case like yours - where there does exist an order in which the changes can be successfully applied without hitting constraints - it eventually succeeds in applying the entire changeset. But, as the sessions module works through the normal SQL interface, each time it hits an intermittent constraint, an error message is emitted on the log. Dan. Thanks a lot for any insights! Yours, Daniel = Build script: #!/bin/sh -e PKGS="sqlite3 libglog" CFLAGS="`pkg-config --cflags ${PKGS}` -std=c++14 -Wall -Werror -pedantic" CFLAGS="${CFLAGS} -std=c++14 -Wall -Werror -pedantic" CFLAGS="${CFLAGS} -DSQLITE_ENABLE_SESSION -DSQLITE_ENABLE_PREUPDATE_HOOK" LIBS=`pkg-config --libs ${PKGS}` g++ ${CFLAGS} ${LIBS} test.cpp -o test = test.cpp: /* Test code for UNIQUE keys and inverting SQLite changesets. */ #include #include #include #include namespace { void ErrorLogger (void* arg, const int errCode, const char* msg) { LOG (ERROR) << "SQLite error (code " << errCode << "): " << msg; } void Execute (sqlite3* db, const std::string& sql) { VLOG (1) << "Executing SQL:\n" << sql; char* err; const int rc = sqlite3_exec (db, sql.c_str (), nullptr, nullptr, &err); if (rc != SQLITE_OK) LOG (FATAL) << "SQL error: " << err; sqlite3_free (err); } void Print (sqlite3* db) { const std::string sql = R"( SELECT `id`, `value` FROM `mytable` ORDER BY `id` ASC )"; sqlite3_stmt* stmt; CHECK_EQ (sqlite3_prepare_v2 (db, sql.c_str (), sql.size (), &stmt, nullptr), SQLITE_OK); while (true) { const int rc = sqlite3_step (stmt); if (rc == SQLITE_DONE) break; CHECK_EQ (rc, SQLITE_ROW); LOG (INFO) << " Row: (" << sqlite3_column_int (stmt, 0) << ", " << sqlite3_column_int (stmt, 1) << ")"; } CHECK_EQ (sqlite3_finalize (stmt), SQLITE_OK); } int AbortOnConflict (void* ctx, const int conflict, sqlite3_changeset_iter* it) { LOG (ERROR) << "Changeset application has conflict of type " << conflict; return SQLITE_CHANGESET_ABORT; } } // anonymous namespace int main () { LOG (INFO) << "Using SQLite version " << SQLITE_VERSION << " (library version: " << sqlite3_libversion () << ")"; CHECK_EQ (sqlite3_config (SQLITE_CONFIG_LOG, &ErrorLogger, nullptr), SQLITE_OK); sqlite3* db; CHECK_EQ (sqlite3_open (":memory:", &db), SQLITE_OK); LOG (INFO) << "Opened in-memory database"; Execute (db, R"( CREATE TABLE `mytable` (`id` INTEGER PRIMARY KEY, `value` INTEGER, UNIQUE (`value`))
[sqlite] Inverted changesets and UNIQUE constraints
Hi! I'm using the SQLite session extension to create changesets, invert them and apply them to undo previous changes in the database. (Essentially what I need to do is persistent savepoints.) This works well so far, but I recently wondered about the interaction with UNIQUE constraints. In particular, let's say that I record a changeset of the following modification: I remove some rows from a table, and *then* I insert new rows that have the same values in UNIQUE columns as the previously removed ones. That obviously works fine because I delete first and *then* insert. However, my understanding of how the session extension works (according to its docs) is that when I invert and apply the changeset, it will *first* insert the deleted rows, and *then* delete the inserted ones. (Because it inverts each operation but not the order of them.) I created a simplified example program that performs exactly this on an in-memory database. The code (plus the shell script to build it with the flags I used) is below and can also be found on Github: https://gist.github.com/domob1812/a3b78739772a1ff0c001be6cbc600f17 (Perhaps that is easier to view than in the email.) When I run that on my system (with SQLite 3.16.2 from Debian Stretch), I get errors printed from ErrorLogger like this: E0826 10:08:16.840441 21876 test.cpp:16] SQLite error (code 2067): abort at 16 in [INSERT INTO main."mytable" VALUES(?, ?)]: UNIQUE constraint failed: mytable.value E0826 10:08:16.840520 21876 test.cpp:16] SQLite error (code 2067): abort at 16 in [INSERT INTO main."mytable" VALUES(?, ?)]: UNIQUE constraint failed: mytable.value This matches what I expect. However, it seems that applying the changeset still works fine, and I get the correct "old" state restored. Is this just "by chance", or can I safely ignore these errors in this context and rely on the ability to apply inverted changesets even if they (intermittently) violate UNIQUE constraints? Thanks a lot for any insights! Yours, Daniel = Build script: #!/bin/sh -e PKGS="sqlite3 libglog" CFLAGS="`pkg-config --cflags ${PKGS}` -std=c++14 -Wall -Werror -pedantic" CFLAGS="${CFLAGS} -std=c++14 -Wall -Werror -pedantic" CFLAGS="${CFLAGS} -DSQLITE_ENABLE_SESSION -DSQLITE_ENABLE_PREUPDATE_HOOK" LIBS=`pkg-config --libs ${PKGS}` g++ ${CFLAGS} ${LIBS} test.cpp -o test = test.cpp: /* Test code for UNIQUE keys and inverting SQLite changesets. */ #include #include #include #include namespace { void ErrorLogger (void* arg, const int errCode, const char* msg) { LOG (ERROR) << "SQLite error (code " << errCode << "): " << msg; } void Execute (sqlite3* db, const std::string& sql) { VLOG (1) << "Executing SQL:\n" << sql; char* err; const int rc = sqlite3_exec (db, sql.c_str (), nullptr, nullptr, &err); if (rc != SQLITE_OK) LOG (FATAL) << "SQL error: " << err; sqlite3_free (err); } void Print (sqlite3* db) { const std::string sql = R"( SELECT `id`, `value` FROM `mytable` ORDER BY `id` ASC )"; sqlite3_stmt* stmt; CHECK_EQ (sqlite3_prepare_v2 (db, sql.c_str (), sql.size (), &stmt, nullptr), SQLITE_OK); while (true) { const int rc = sqlite3_step (stmt); if (rc == SQLITE_DONE) break; CHECK_EQ (rc, SQLITE_ROW); LOG (INFO) << " Row: (" << sqlite3_column_int (stmt, 0) << ", " << sqlite3_column_int (stmt, 1) << ")"; } CHECK_EQ (sqlite3_finalize (stmt), SQLITE_OK); } int AbortOnConflict (void* ctx, const int conflict, sqlite3_changeset_iter* it) { LOG (ERROR) << "Changeset application has conflict of type " << conflict; return SQLITE_CHANGESET_ABORT; } } // anonymous namespace int main () { LOG (INFO) << "Using SQLite version " << SQLITE_VERSION << " (library version: " << sqlite3_libversion () << ")"; CHECK_EQ (sqlite3_config (SQLITE_CONFIG_LOG, &ErrorLogger, nullptr), SQLITE_OK); sqlite3* db; CHECK_EQ (sqlite3_open (":memory:", &db), SQLITE_OK); LOG (INFO) << "Opened in-memory database"; Execute (db, R"( CREATE TABLE `mytable` (`id` INTEGER PRIMARY KEY, `value` INTEGER, UNIQUE (`value`)); INSERT INTO `mytable` (`id`, `value`) VALUES (1, 42), (2, 100); )"); LOG (INFO) << "Initial state:"; Print (db); /* Now we modify the table and record the result in a changeset. The modification is valid with respect to the UNIQUE constraint, but only because we delete the existing entries first and insert afterwards. */ sqlite3_session* session; CHECK_EQ (sqlite3session_create (db, "main", &session), SQLITE_OK); CHECK_EQ (sqlite3session_attach (session, nullptr), SQLITE_OK); Execute (db, R"( DELETE FROM `mytable`; INSERT INTO `mytable` (`id`, `value`) VALUES (3, 42), (4, 100); )"); LOG (INFO) << "Modified state:"; Print (db); /* Extract the changeset, invert it and apply the inverted changeset