Inverted changesets and UNIQUE constraints

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Inverted changesets and UNIQUE constraints

Daniel Kraft
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 <sqlite3.h>

#include <glog/logging.h>

#include <cstdlib>
#include <string>

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 to
     undo the previous changes.  This fails with the UNIQUE constraint
     violation because the order of delete/insert is wrong.  */
  int changeSize;
  void* changeBytes;
  CHECK_EQ (sqlite3session_changeset (session, &changeSize, &changeBytes),
            SQLITE_OK);
  sqlite3session_delete (session);
  int invertedSize;
  void* invertedBytes;
  CHECK_EQ (sqlite3changeset_invert (changeSize, changeBytes,
                                     &invertedSize, &invertedBytes),
            SQLITE_OK);
  sqlite3_free (changeBytes);
  LOG (INFO) << "Extracted inverted changeset";

  LOG (INFO) << "Applying inverted changeset...";
  CHECK_EQ (sqlite3changeset_apply (db, invertedSize, invertedBytes,
nullptr,
                                    &AbortOnConflict, nullptr),
            SQLITE_OK);
  sqlite3_free (invertedBytes);

  LOG (INFO) << "Restored state:";
  Print (db);

  CHECK_EQ (sqlite3_close (db), SQLITE_OK);

  return EXIT_SUCCESS;
}

=================================

--
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


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Inverted changesets and UNIQUE constraints

Dan Kennedy-4

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 <sqlite3.h>
>
> #include <glog/logging.h>
>
> #include <cstdlib>
> #include <string>
>
> 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 to
>       undo the previous changes.  This fails with the UNIQUE constraint
>       violation because the order of delete/insert is wrong.  */
>    int changeSize;
>    void* changeBytes;
>    CHECK_EQ (sqlite3session_changeset (session, &changeSize, &changeBytes),
>              SQLITE_OK);
>    sqlite3session_delete (session);
>    int invertedSize;
>    void* invertedBytes;
>    CHECK_EQ (sqlite3changeset_invert (changeSize, changeBytes,
>                                       &invertedSize, &invertedBytes),
>              SQLITE_OK);
>    sqlite3_free (changeBytes);
>    LOG (INFO) << "Extracted inverted changeset";
>
>    LOG (INFO) << "Applying inverted changeset...";
>    CHECK_EQ (sqlite3changeset_apply (db, invertedSize, invertedBytes,
> nullptr,
>                                      &AbortOnConflict, nullptr),
>              SQLITE_OK);
>    sqlite3_free (invertedBytes);
>
>    LOG (INFO) << "Restored state:";
>    Print (db);
>
>    CHECK_EQ (sqlite3_close (db), SQLITE_OK);
>
>    return EXIT_SUCCESS;
> }
>
> =================================
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Inverted changesets and UNIQUE constraints

Simon Slavin-3
On 26 Aug 2019, at 12:43pm, Dan Kennedy <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Inverted changesets and UNIQUE constraints

Daniel Kraft
Hi!

On 26.08.19 14:05, Simon Slavin wrote:
> On 26 Aug 2019, at 12:43pm, Dan Kennedy <[hidden email]> 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


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (849 bytes) Download Attachment