删除1行父表的扳手突变限制
删除1行父表的扳手突变限制
提问于 2020-06-23 14:00:01
- Insert和update操作与其影响的列数的多重性有关。例如，如果将值插入到五列中，则插入新记录可以算作五个突变。无论受影响的列数如何，删除和删除范围操作都算作一种突变。从具有ON DELETE级联注释的父表中删除行也算作一次突变，而不考虑存在多少交错子行。
今天，我试图从父表中删除1行，其子表(交错)表指定了ON DELETE CASCADE。
示例：DELETE FROM my_table WHERE some_primary_key='somevalue'
我收到了错误消息，说我达到了变异限制，因为子表中有超过20k行。根据上面的文档，不管子表中有多少行，这仍然应该算作1的变异。
注意，子表有一个辅助索引设置。
这些文档有问题吗，还是我遗漏了什么？
回答 1
Stack Overflow用户
回答已采纳
发布于 2020-06-23 19:07:20
似乎每个后续的索引更新都被计算为一个突变。我做了以下实验来验证这一点：
- 创建一个表
CREATE TABLE ParentTable (
Pid INT64 NOT NULL,
Data STRING(1024),
) PRIMARY KEY(Pid);
CREATE TABLE ChildTable (
Pid INT64 NOT NULL,
Cid INT64 NOT NULL,
Data STRING(1024),
) PRIMARY KEY(Pid, Cid),
INTERLEAVE IN PARENT ParentTable ON DELETE CASCADE;- 填充1行父行和30000行子行：
// Inserts 1 ParentTable rows
INSERT INTO ParentTable (Pid) (SELECT * FROM UNNEST([1]));
// Insert 30000 rows to ChildTable for pid=1
INSERT INTO ChildTable (Pid, Cid) (SELECT 1, child.Cid FROM
(SELECT 0+G.g AS Cid FROM (SELECT E.e*10+F.f AS g FROM (SELECT C.c*10+D.d AS e FROM (SELECT A.a*10 + B.b AS c FROM (SELECT * FROM UNNEST([0,1,2,3,4,5,6,7,8,9]) AS a) AS A, (SELECT * FROM UNNEST([0,1,2,3,4,5,6,7,8,9]) AS b) AS B) AS C, (SELECT * FROM UNNEST([0,1,2,3,4,5,6,7,8,9]) AS d) AS D) AS E, (SELECT * FROM UNNEST([0,1,2,3,4,5,6,7,8,9]) as f) AS F) AS G) AS Child);
INSERT INTO ChildTable (Pid, Cid) (SELECT 1, child.Cid FROM
(SELECT 10000+G.g AS Cid FROM (SELECT E.e*10+F.f AS g FROM (SELECT C.c*10+D.d AS e FROM (SELECT A.a*10 + B.b AS c FROM (SELECT * FROM UNNEST([0,1,2,3,4,5,6,7,8,9]) AS a) AS A, (SELECT * FROM UNNEST([0,1,2,3,4,5,6,7,8,9]) AS b) AS B) AS C, (SELECT * FROM UNNEST([0,1,2,3,4,5,6,7,8,9]) AS d) AS D) AS E, (SELECT * FROM UNNEST([0,1,2,3,4,5,6,7,8,9]) as f) AS F) AS G) AS Child);
INSERT INTO ChildTable (Pid, Cid) (SELECT 1, child.Cid FROM
(SELECT 20000+G.g AS Cid FROM (SELECT E.e*10+F.f AS g FROM (SELECT C.c*10+D.d AS e FROM (SELECT A.a*10 + B.b AS c FROM (SELECT * FROM UNNEST([0,1,2,3,4,5,6,7,8,9]) AS a) AS A, (SELECT * FROM UNNEST([0,1,2,3,4,5,6,7,8,9]) AS b) AS B) AS C, (SELECT * FROM UNNEST([0,1,2,3,4,5,6,7,8,9]) AS d) AS D) AS E, (SELECT * FROM UNNEST([0,1,2,3,4,5,6,7,8,9]) as f) AS F) AS G) AS Child);- 验证子表有30000行具有Pid=1：
// Verify counts
SELECT COUNT(*) FROM ChildTable WHERE Pid=1;- 尝试删除它(成功)
DELETE FROM ParentTable WHERE Pid=1- 现在再重复1到3次。这一次，我们在子表上创建辅助索引。
CREATE INDEX Cid_Increasing ON ChildTable (Cid) STORING (Data);- 再次尝试删除父行，这一次它失败了，并表示
This DML statement exceeds the mutation limit for a single transaction (20000). To reduce the mutation count, try a transaction with fewer writes, or use fewer indexes. This can help because the mutation count for an operation is equal to the number of columns it affects. Reducing writes or indexes reduces the number of affected columns, bringing your mutation count under the limit. Alternatively, try a Partioned DML statement using the client libraries or gcloud command-line tool.- 好的，尝试将行数减少到20000，然后删除。又失败了。
DELETE FROM ChildTable WHERE Cid > 19999; // deleted 10000 rows.
SELECT COUNT(*) FROM ChildTable WHERE Pid=1; // should be 20000 now.
DELETE FROM ParentTable WHERE Pid=1; // still failed.- 删除子表中的另一行。这一次父级删除成功。
DELETE FROM ChildTable WHERE Cid > 19998;
SELECT COUNT(*) FROM ChildTable WHERE Pid=1; // should be 19999 now.
DELETE FROM ParentTable WHERE Pid=1; // succeeded 最后两个实验表明：
- 父行删除以及其子行删除仅被计算为1突变。
- 每个后续的指数变化被计算为1突变。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/62536521
复制相关文章
相似问题
腾讯云开发者
