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, );
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 (), ,
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, , nullptr),
SQLITE_OK);
sqlite3* db;
CHECK_EQ (sqlite3_open (":memory:", ), SQLITE_OK);
LOG (INFO) << "Opened in-memory database";
Execute (db, R"(
CREATE TABLE `mytable`
(`id` INTEGER PRIMARY KEY,
`value` INTEGER,
UNIQUE (`value`));
INSERT INTO
[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, );
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 (), ,
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, , nullptr),
SQLITE_OK);
sqlite3* db;
CHECK_EQ (sqlite3_open (":memory:", ), 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", ), 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 to
undo the previous
