We're on

StarRocks 4.1.1

on an external Iceberg table, UPDATE fails (

ERROR 1064: table <t> does not support update

). Multi-statement txns don't apply either (

BEGIN → DML → databaseTransactionMgr does not exist

), so we can't wrap DELETE+INSERT atomically. Is UPDATE on external Iceberg V2 expected to work in 4.1.1? Any config/table-property we're missing ? For now what is the recommended workaround?

@Rocky answer this

I'd be happy to help! However, you haven't provided a question yet. Please let me know what you'd like to know about StarRocks, and I'll do my best to answer.

@Rocky We're on

StarRocks 4.1.1

on an external Iceberg table, UPDATE fails (

ERROR 1064: table <t> does not support update

). Multi-statement txns don't apply either (

BEGIN → DML → databaseTransactionMgr does not exist

), so we can't wrap DELETE+INSERT atomically. Is UPDATE on external Iceberg V2 expected to work in 4.1.1? Any config/table-property we're missing ? For now what is the recommended workaround?

As of StarRocks 4.1.1 (and the 4.x release line), support for the

UPDATE

statement on external Apache Iceberg tables is a relatively new and evolving feature. While documentation for 4.x describes the syntax, there are strict requirements and current architectural limitations regarding transactions. 1. Why

UPDATE

fails with "Table does not support update" Even in version 4.1.1, the

UPDATE

statement for Iceberg is only supported under specific conditions. If you receive this error, check the following: * Table Format: Only Parquet-formatted Iceberg tables are supported for DML (`UPDATE`/`DELETE`). ORC and Avro formats do not currently support

UPDATE

. * Iceberg Version: It must be a v2 (Row-level Deletes) table. StarRocks uses the Merge-On-Read (MoR) model to perform updates by writing position deletes and new data files. * Restricted Columns: You cannot update partition columns. If your

SET

clause includes a column used in the table's partition spec, it will be rejected. * WHERE Clause: A

WHERE

clause is mandatory to prevent accidental full-table updates. * Session Variable: Ensure that DML on external catalogs is enabled in your session:

sql
    SET enable_external_table_dml = true;

(Note: The exact variable name may vary by minor version; check

SHOW VARIABLES LIKE '%dml%';

if this doesn't work.) 2. Why

BEGIN

fails with

databaseTransactionMgr does not exist

The error

databaseTransactionMgr does not exist

occurs because multi-statement transactions (

BEGIN...COMMIT

) are currently tied to the Internal Catalog (

default_catalog

). * External Catalogs (Iceberg, Hive, etc.) do not yet support the StarRocks Transaction Manager for multi-statement DML. Each DML statement (like

INSERT

or

DELETE

) against an external catalog is executed as its own atomic transaction at the Iceberg metadata level. * Wrapping a

DELETE

and an

INSERT

in a

BEGIN...COMMIT

block is not supported for external catalogs in 4.1.1. 3. Recommended Workarounds Since you cannot wrap

DELETE+INSERT

atomically in a transaction for an external table, use one of the following strategies: A. Use

INSERT OVERWRITE

(Atomic) If you are updating a specific partition,

INSERT OVERWRITE

is the most reliable atomic operation.

sql
INSERT OVERWRITE iceberg_table 
SELECT ... FROM source_table 
WHERE partition_col = 'target_value';

This replaces the entire partition (or table) atomically. B. Native

UPDATE

(if requirements are met) If your table is Parquet v2 and you have the correct permissions, try the single-statement

UPDATE

. If it still fails, it may be due to a lack of a primary key-like identifier that StarRocks can use to generate the position deletes. C. Staging Table (The "Swap" Pattern) 1. Read the data from the target table into a StarRocks Internal Primary Key table. 2. Perform your `UPDATE`/`DELETE` logic on the internal table (which fully supports transactions). 3. Sink the final state back to Iceberg using

INSERT OVERWRITE

. Summary of Requirements for Iceberg UPDATE: Feature | Requirement Table Version | Iceberg v2 File Format | Parquet Atomic Operation |

INSERT OVERWRITE

Multi-statement | Not supported for External Catalogs (use internal tables for complex logic) If you are certain the table is Parquet V2 and the session variable is set, you may want to verify the table's capabilities via

DESC EXTENDED <table_name>

to ensure StarRocks recognizes it as a mutable V2 table. References • data_source/catalog/catalog_overview.md • data_source/External_table.md • integrations/dbt.md • sql-reference/sql-statements/Catalog/CREATE_EXTERNAL_CATALOG.md

https://github.com/StarRocks/starrocks/issues/72046 update will land in 4.2