sql: disallow multiple modification subqueries of same table

[cockroach-teamcity]

Exalate commented:

cockroachdb/cockroach#71595 --- Release note (sql change): Statements containing multiple INSERT ON CONFLICT, UPSERT, UPDATE, or DELETE subqueries can cause data corruption if they modify the same row multiple times. For example, the following SELECT 1 statement will cause corruption of table t: sql CREATE TABLE t (i INT, j INT, PRIMARY KEY (i), INDEX (j)); INSERT INTO t VALUES (0, 0); WITH cte1 AS (UPDATE t SET j = 1 WHERE i = 0 RETURNING *), cte2 AS (UPDATE t SET j = 2 WHERE i = 0 RETURNING *) SELECT 1; Until this is fixed, this change disallows statements with multiple subqueries which modify the same table. Applications can work around this by rewriting problematic statements. For example, the query above can be rewritten as an explicit multi-statement transaction: sql BEGIN; UPDATE t SET j = 1 WHERE i = 0; UPDATE t SET j = 2 WHERE i = 0; SELECT 1; COMMIT; or, if it doesn't matter which update "wins", as multiple non-mutating CTEs on an UPDATE statement: sql WITH cte1 AS (SELECT 1), cte2 AS (SELECT 2) UPDATE t SET j = x.j FROM (SELECT * FROM cte1 UNION ALL SELECT * FROM cte2) AS x (j) WHERE i = 0 RETURNING 1; which in this case could be written more simply as: sql UPDATE t SET j = x.j FROM (VALUES (1), (2)) AS x (j) WHERE i = 0 RETURNING 1; (Note that in these last two rewrites the first update will win, rather than the last.) None of these rewrites suffer from the corruption problem. To override this change and allow these statements in spite of the risk of corruption, applications can: sql SET CLUSTER SETTING sql.multiple_modifications_of_table.enabled = true But be warned that with this enabled there is nothing to prevent this type of corruption from occuring if the same row is modified multiple times by a single statment. To check for corruption, use the EXPERIMENTAL SCRUB command: sql EXPERIMENTAL SCRUB TABLE t WITH OPTIONS INDEX ALL;

Jira Issue: DOC-1871