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

Inconsistencies of TRUNCATE on SYSTEM VERSIONED tables

    XMLWordPrintable

    Details

      Description

      MDEV-15966 changed the behavior of TRUNCATE TABLE on SYSTEM VERSIONED tables

      From: https://mariadb.com/docs/appdev/temporal-tables/
      "To protect historic data, the TRUNCATE statement raises Error 4137."

      From: https://mariadb.com/kb/en/system-versioned-tables/
      "The TRUNCATE TABLE statement drops all historical records from a system-versioned-table."

      From: https://mariadb.com/kb/en/truncate-table/
      "Currently, TRUNCATE TABLE drops all historical records from a system-versioned table."

      So most of the docs still show the original behavior, and personally I EXPECTED the original behavior after having used that considerably in the past, especially during the development phase to clean out a table while doing early development work on a new product.

      I also understand the desire to be "standards compliant", but arbitrarily changing things without clearly documenting those changes is not a good end-user experience. Removing existing functionality is also a bad end-user experience.

      As TRUNCATE behind the scenes is really a DROP/CREATE combination, it does more than just delete data from a table, it prevents TRIGGERS which could be costly and slow, and it also resets the AUTO_INCREMENT value for a table as well. These are all crucial things that I think were neglected to be considered when this feature was removed.

      I propose adding this feature back in, but possibly making it additional syntax. Much like we have "DELETE HISTORY FROM" and "CREATE TABLE WITH SYSTEM VERSIONING", why not a "TRUNCATE TABLE WITH SYSTEM VERSIONING" or something similar so an admin can explicitly declare they want to delete all historical data too, since "deleting historical data" seemed to be the only real objection on the other ticket. As stated, there are very real times where us engineer DO indeed want to TRUNCATE while also destroying all historical data.

      Basically, make is so "TRUNCATE TABLE" retains the newer functionality with the error message, "TRUNCATE WITH SYSTEM VERSIONING" to allow the previous functionality.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              darkain Vincent Milum Jr
              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.