Redshiftで再帰クエリを無限ループさせてみた

CX事業本部@大阪の岩田です。先日のアップデートでRedshiftが再帰クエリをサポートするようになりました。

私はかの有名な書籍「SQLアンチパターン」で再帰クエリの存在を知り、当時開発中だったシステムで再帰クエリを利用したことがあります。その中で今でも思い出に残っているのが、結合テスト実施中に再帰クエリが無限ループしてしまったという事件です。システムのバグで以下のようなデータが登録された状態で再帰クエリを実行したところ、レコード間の親子関係が循環しているためいつまでたってもクエリの実行が完了せずCPU使用率が100%で張り付いてしまったのです。

RedshiftのベースとなったPostgreSQLのドキュメントでは再帰クエリの無限ループについて以下のように紹介されています。

再帰的問い合わせを扱う場合、問い合わせの再帰部分が最終的にはタプルを返さないようにすることが重要です。 そうしなければ、問い合わせが永久にループしてしまうからです

7.8. WITH問い合わせ（共通テーブル式）

無限ループ対策はクライアント側の責務であり、サーバー側では無限ループを検出して中断するような機構は特に提供されていないのです。よって無限ループが発生するようなクエリを実行してしまうと、前述のように一生終わらないクエリが実行され続けることになります。ではRedshiftの振る舞いはどうなのでしょうか？実際にRedshiftで再帰クエリの無限ループを試してみました。

環境

今回検証に利用した環境は以下の通りです

• ノードの種類: dc2.large
• ノード数: 1
• クラスタバージョン: 1.0.26065

Redshiftの再帰クエリを無限ループさせてみる

まずは冒頭で紹介したブログの内容に沿ってテスト用のテーブル&データを用意します。

テスト用テーブルの作成

CREATE TABLE members(
  id int,
  name varchar(32),
  parent_id int
);

テスト用データのINSERT

INSERT INTO members(id, name, parent_id)
VALUES(1,'parent', null),
      (2,'child1', 1),
      (3,'child2', 1),
      (4,'gchild1', 2),
      (6,'gchild2', 3),
      (7,'gchild3', 3),
      (8,'ggchild1', 4),
      (9,'ggchild2', 4),
      (11,'ggchild3', 7)
;

とりあえず再帰クエリを試してみます

WITH RECURSIVE cte(id, name, parent_id) AS (
  SELECT id, name, parent_id FROM members WHERE name = 'child2'
UNION ALL
  SELECT child.id, child.name, child.parent_id
  FROM members AS child, cte
  WHERE cte.id = child.parent_id
)
SELECT * FROM cte;

無事に結果が取得できました

 id |   name   | parent_id
----+----------+-----------
  3 | child2   |         1
  7 | gchild3  |         3
  6 | gchild2  |         3
 11 | ggchild3 |         7
(4 rows)

ここからが本番です。親子関係が無限ループするようにデータを更新してみます。

UPDATE members SET parent_id = 7 WHERE id = 3;

更新後のテーブルの状況は以下の通りです。

SELECT * FROM members ORDER BY id;

 id |   name   | parent_id
----+----------+-----------
  1 | parent   |
  2 | child1   |         1
  3 | child2   |         7
  4 | gchild1  |         2
  6 | gchild2  |         3
  7 | gchild3  |         3
  8 | ggchild1 |         4
  9 | ggchild2 |         4
 11 | ggchild3 |         7

gchild3の親はchild2で、child2の親はgchild3となっています。親子関係が循環しています。この状態で再度先程の再帰クエリを実行してみると...

ERROR:  Hit recursive CTE max rows limit, please add correct CTE termination predicates or change the max_recursion_rows parameter.
DETAIL:
  -----------------------------------------------
  error:  Hit recursive CTE max rows limit, please add correct CTE termination predicates or change the max_recursion_rows parameter.
  code:      8001
  context:
  query:     251
  location:  step_common.cpp:1016
  process:   query0_127_251 [pid=11956]
  -----------------------------------------------

なんと、ちゃんとエラーになりました！エラーメッセージからすると無限ループを検出したというよりは再帰クエリで取得可能な行数に上限が設定されているような気がしますが、どうなんでしょうか？エラーメッセージで指摘されているパラメータmax_recursion_rowsを確認してみます

show max_recursion_rows;
 max_recursion_rows
--------------------
 2000000
(1 row)

デフォルトの設定だと再帰クエリの実行結果が2,000,000行を超えた時点でエラーになるようですね。通常の利用用途であれば2,000,000行も取得できれば困ることは無さそうです。

試しにmax_recursion_rowsを1に変更、無限ループしないようにデータを修正した上で再度クエリを実行してみます。パラメータを変更して

set max_recursion_rows=1;
SET

show max_recursion_rows;
 max_recursion_rows
--------------------
 1
(1 row)

データを修正

UPDATE members SET parent_id = 1 WHERE id = 3;

再度再帰クエリを実行

SELECT * FROM cte;
 id |   name   | parent_id
----+----------+-----------
  3 | child2   |         1
  7 | gchild3  |         3
  6 | gchild2  |         3
 11 | ggchild3 |         7
(4 rows)

max_recursion_rows=1の状態ですが、普通に4行SELECTできました。無限ループしていない場合はエラーにならないんでしょうか？このあたりの挙動はまた後日確認してみます。

最後にマネコンからクエリのモニタリングを確認してみます。

該当時間帯のヘルスステータスはこちら

CPU使用率が跳ね上がってるという感じでもないですね。

ちなみにPostgreSQL13 on Docker on AmazonLinux2な環境で同じクエリを実行してみると、以下のように簡単にCPU使用率が100%に張り付きました

top - 02:18:33 up  1:22,  3 users,  load average: 0.15, 0.03, 0.01
Tasks: 114 total,   3 running,  73 sleeping,   0 stopped,   0 zombie
%Cpu(s): 48.2 us,  6.8 sy,  0.0 ni, 45.0 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem :  7865400 total,  5983340 free,   690092 used,  1191968 buff/cache
KiB Swap:        0 total,        0 free,        0 used.  6866052 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 4519 libstor+  20   0  219856  18936  12104 R 100.0  0.2   0:11.36 postgres
 4509 libstor+  20   0  414072 375192   7052 R   9.6  4.8   0:01.13 psql
    1 root      20   0   43752   5460   3980 S   0.0  0.1   0:01.21 systemd
    2 root      20   0       0      0      0 S   0.0  0.0   0:00.00 kthreadd
...略

まとめ

Redshiftは再帰クエリの無限ループに対しても対策されていることが分かりました。SQLの記述を誤って無限ループさせてしまってもRedshiftがエラーを返してくれるので安心して再帰クエリが使えそうです。