[MCOL-5115] Permit administrators to block DELETE and UPDATE DML on Columnstore tables Created: 2022-06-03  Updated: 2023-10-27  Resolved: 2023-10-27

Status: Closed
Project: MariaDB ColumnStore
Component/s: DMLProc
Affects Version/s: None
Fix Version/s: 23.10

Type: New Feature Priority: Major
Reporter: Edward Stoever Assignee: Leonid Fedorov
Resolution: Won't Do Votes: 2
Labels: triage


 Description   

Example:

MariaDB [inventory]> update inventory.products set supplier='MariaDB' where supplier='mySQL';
 
ERROR 1815 (HY000): Internal error:  WARNING: UPDATE DML is blocked for Columnstore tables. For more details, see the webpage https://
 
 
 
MariaDB [inventory]> delete from inventory.products where supplier='DML Imports';
 
ERROR 1815 (HY000): Internal error:  WARNING: DELETE DML is blocked for Columnstore tables. For more details, see the webpage https://

The Columnstore engine is not optimized for UPDATEs or DELETEs. In some situations, such DML will require an unforseen amount of time to complete, such as days or weeks. This can lead to user-interactive cancel operations. UPDATEs and DELETEs, and in particular the rollback of such commands can permanently damage Columnstore extent mapping.

By blocking such DML as a feature, we would encourage customers to become more knowledgeable about the Columnstore product. It would also allow customers to block UPDATE or DELETE commands. A customer would toggle the feature on or off with a global/session variable. For example:
In .cnf file:

columnstore_block_delete_dml = 1 # (default 1)
 
columnstore_block_update_dml = 1 # (default 1)

In session:

MariaDB [inventory]> set session columnstore_block_delete_dml=0;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [inventory]> set session columnstore_block_update_dml=0;
 
Query OK, 0 rows affected (0.000 sec)


Generated at Thu Feb 08 02:55:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.