Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24064

Utility columns for system-versioned tables

    XMLWordPrintable

Details

    Description

      Concurrent threads may produce history rows with equal row_end and primary key values. Since key uniqueness is now solved by adding row_end column to all unique keys such concurrent operation may generate duplicate key errors. That would be acceptable if we skipped generating history for the identical updates but the requirement of "guaranteed history" in MDEV-23446 forbids such history skipping.

      Another problem that special column can solve is to unambiguously link current row and its history rows. This is usable for systems that require "guaranteed history" concept as when you need such guaranteed history you probably want to know which row change produced it.

      HISTORY_ID nullable unique column identifies history row in unique keys instead of row_end.

      ROW_ID non-nullable non-unique auto-incremented column links current row and history row.

      When new row is inserted its ROW_ID is assigned new value from incremented counter. HISTORY_ID is set to NULL. When row is updated its ROW_ID is copied to history row and new unique value is assigned to its HISTORY_ID.

      As ROW_ID and HISTORY_ID increase data size and processing time if not significantly but noticeably, the presence of these columns should be configurable on per-table basis.

      Also replication and data/dump restore may lift up additional requirements which is subject for further elaboration. Like HISTORY_ID and ROW_ID value clashes and whether they should be dumped/replicated.

      Attachments

        Issue Links

          Activity

            People

              midenok Aleksey Midenkov
              midenok Aleksey Midenkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.