Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-4930

DELETEs and UPDATEs on Columnstore should prompt an error.

    XMLWordPrintable

Details

    • New Feature
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • Icebox
    • None
    • None

    Description

      Many Columnstore escalations are a result of users executing DELETE or UPDATE DML commands. When such commands modify over 1,000 rows, users tend to get impatient and attempt to exit out of the command with control+c. This leads to rollbacks that might never complete and table locks that cannot be easily undone. These actions can destroy the mapping to columnstore extents.

      I suggest creating a session variable "columnstore_allow_deletes_updates" with default value OFF. When a DELETE or UPDATE passes through the DMLProc, if the variable is set to OFF, an error occurs. The error message can point the user to a webpage that explains why DELETE and UPDATE should be used with caution or avoided in Columnstore. This is similar to the way that the global variable log_bin_trust_function_creators works.

      MariaDB [inventory]> DELETE from inventory.products where supplier=332;
      ERROR 1234 (HY000): Columnstore is not a transactional database. DELETE and UPDATE DML 
      can complete very slowly on Columnstore tables and lead to severe predicaments.  
      To allow these commands set columnstore_allow_deletes_updates=ON. 
      Refer to https://mariadb.com/columnstore/columnstore_allow_deletes_updates.html
      MariaDB [inventory]> SET columnstore_allow_deletes_updates=ON;
      Query OK, 0 rows affected (0.000 sec)
      MariaDB [inventory]> DELETE from inventory.products where supplier=332;
      Query OK, 1 row affected (0.240 sec)
      MariaDB [inventory]> 
      

      Attachments

        Activity

          People

            drrtuy Roman
            edward Edward Stoever
            Votes:
            2 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.