How to Resolve Multi-Statement DML Errors on Reference Tables Using Child Aggregators?
{question}
{answer}
Error: Writing to a reference table in multi-statement transaction is not permitted on child aggregators
When does this error occur?
This error occurs when you attempt to perform DML operations (e.g., INSERT, UPDATE, DELETE) on reference tables within a multi-statement transaction through a Child Aggregator in SingleStore.
Why does this happen?
Reference tables in SingleStore are created on the Master Aggregator and automatically replicated to all nodes in the cluster. DML operations on these tables are allowed through aggregators if the engine variable enable_dml_query_forwarding is set to ON.
However, multi-statement transactions on reference tables are not currently supported on Child Aggregators, even if the variable enable_dml_query_forwarding is enabled. This results in the following error:
Writing to a reference table in multi-statement transaction is not permitted on child aggregators
How to resolve it?
Depending on your deployment type, you can work around this limitation:
-
For SingleStore On-Premises Clusters:
Connect directly to the Master Aggregator and execute your DML statements within the multi-statement transaction. -
For SingleStore Cloud DB Clusters:
Use the DDL endpoint, which connects you to the Master Aggregator, and perform the required DML operations there. Please open a support ticket, and the support team will assist you in obtaining the appropriate DDL endpoint for your workspace group/Cluster.
Additional Notes
We are actively working on enhancing support for multi-statement transactions on reference tables through Child Aggregators. In the meantime, using the Master Aggregate, as described above, is the recommended workaround.
{answer}