PostgreSQL : 9.4より古いなら、マテビューをFROM句に入れるビューは良くない
月花です。
PostgreSQL の MATERIALIZED VIEW を REFRESH すると、 その MATERIALIZED VIEW を FROM句とする VIEW がどうなってしまうのか、
ということをよく知らずに、ばかなことをしてしまったなー、という記事です。
これは検索ワードのための文なので、ここから日本人になります。
- なにをしたのか
- 前提
- マテビュー
- ビュー
- マテビューのリフレッシュとロック
- リフレッシュ時のロック対策
- あるビューのFROM句にいるマテビューをリネームするとどうなるのか
- 実際に何が起こっていたか
- 解決したかった
- じゃあどうすればよかったのか
なにをしたのか
あるマテビューを作り、そのマテビューをFROM句とするビューを作った。
そこで、ロジックで分岐させてどのビューを読むのか、というようなことをしていた。
使っていたpostgresのバージョンが9.4より古かったので、マテビューのリフレッシュ時は ACCESS EXCLUSIVE LOCK という、SELECTすら許さない強いロックがかかる。
それを嫌って、同じマテビューを2つ用意して交互にリフレッシュとリネームをしながら、常にリフレッシュしていないマテビューが存在するようにしていた。
そう、マテビューをFROM句とするビューがあるとき、そのマテビューをリネームするとビューはどうなるのか。実装時の私は、全く気づいていなかったのである。
前提
ここではサンプルとして、下記のような構成とする。
マテビュー
商品テーブルで、名前とカテゴリ、値段を持っている。
この話で重要なのはカテゴリだけなので、他はてきとう。
sample_products
product_id | name | category_id | price |
1 | apple | 1 | 100 |
2 | beef | 3 | 110 |
3 | carrot | 2 | 120 |
4 | durian | 1 | 130 |
5 | eggplant | 2 | 140 |
6 | fish | 3 | 150 |
7 | grape | 1 | 160 |
ビュー
商品マテビューのカテゴリごとにビューが作ってある。
なんでだろうね?インデックス貼っとけば速いしviewじゃなくていいじゃんなんだけど、サンプルってことで許して欲しい。
CREATE VIEW sample_view_fluits AS SELECT * FROM sample_products WHERE category_id=1; CREATE VIEW sample_view_vegetables AS SELECT * FROM sample_products WHERE category_id=2; CREATE VIEW sample_view_meats AS SELECT * FROM sample_products WHERE category_id=3;
マテビューのリフレッシュとロック
postgresのバージョンが9.4よりも古い場合、マテビューをリフレッシュする際には、そのマテビューにACCESS EXCLUSIVE LOCKという強い排他ロックがかかる。
つまり、リフレッシュ中は他からのSELECTを待たせることになる。
大した事ないマテビューならよいが、10秒もユーザは待ってくれないので、対策が必要となる。
リフレッシュ時のロック対策
いろいろやり方はあるのだが、私がとったのは、2つのマテビューを交互にリフレッシュしていく方式。
まず全く同じ構成のマテビューを2つ用意し、片方をリザーブとする。
その上で、リフレッシュの際にはリザーブだけ更新し、リザーブとそうでない方をリネームで入れ替える。
すると、ビジネスロジックから発行されるクエリは常にリザーブでない方をSELECTするため、ロックの対象にはならない、という方式である。
これは具体的には、下記のようなクエリになる。
BEGIN; REFRESH MATERIALIZED VIEW sample_products_reserve; ALTER MATERIALIZED VIEW sample_products RENAME TO sample_products_temp; ALTER MATERIALIZED VIEW sample_products_reserve RENAME TO sample_products; ALTER MATERIALIZED VIEW sample_products_temp RENAME TO sample_products_reserve; COMMIT;
このときの流れは、元々のマテビューのバージョンが1で、リフレッシュすると1増えて2になると表現すると、下記のようになる
sample_products_reserve のリフレッシュ( 1 -> 2 ) sample_products( 1 ) -リネーム-> sample_products_temp( 1 ) sample_products_reserve( 2 ) -リネーム-> sample_products( 2 ) sample_products_temp( 1 ) -リネーム-> sample_products_reserve( 1 )
そして結果的に、バージョンが1つ上がった sample_products と元のバージョンのままの sample_products_reserve が残る。
tempは交代のためのただのプレースホルダなので、処理が終わればもう存在しない。
次に同じ処理が走ると、下記のようになる。
sample_products_reserve のリフレッシュ( 1 -> 3 ) sample_products( 2 ) -リネーム-> sample_products_temp( 2 ) sample_products_reserve( 3 ) -リネーム-> sample_products( 3 ) sample_products_temp( 2 ) -リネーム-> sample_products_reserve( 2 )
つまり、常に最新バージョンの sample_products と ひとつ古いバージョンの sample_products_reserve が存在するサイクルとなる。
これらをトランザクションに包み込んでしまえば、うまくいくはずだった。
あるビューのFROM句にいるマテビューをリネームするとどうなるのか
あたりまえの話だけど、ひも付けが変わる。
たとえば、前章で紹介したビューの、
CREATE VIEW sample_view_fluits AS SELECT * FROM sample_products WHERE category_id=1;
をみると、先程のサイクル上にいるマテビューをFROM句としている。
このマテビューがあのサイクルにのるとどうなるのかを、このFROM句に対応するマテビューに☆印をつけることで追いかけてみる。
sample_products_reserve のリフレッシュ( 1 -> 2 ) ☆sample_products( 1 ) -リネーム-> ☆sample_products_temp( 1 ) sample_products_reserve( 2 ) -リネーム-> sample_products( 2 ) ☆sample_products_temp( 1 ) -リネーム-> ☆sample_products_reserve( 1 )
なんと、 sample_products_reserve( 1 ) になってしまったのである。
ちなみに、次のサイクルでは、
☆sample_products_reserve のリフレッシュ( 1 -> 3 ) sample_products( 2 ) -リネーム-> sample_products_temp( 2 ) ☆sample_products_reserve( 3 ) -リネーム-> ☆sample_products( 3 ) sample_products_temp( 2 ) -リネーム-> sample_products_reserve( 2 )
どういうことかというと、マテビューがリネームされても、そんなマテビューないよってことにならないように、追跡してくれるのだ。
あったりまえじゃ〜ん。
他言語によるビジネスロジックからの読み込みはテーブル名による参照で、ビューからの読み込みは内部参照値によるものなのだ。
だから、ビジネスロジックからマテビューを検索するぶんにはこれでよい。だけど、ビューがこのマテビューを使う場合は話が違ったということだ。
そしてこの流れを繰り返すと、このビューは、奇数回ではバージョンが上がらず、偶数回で一気に2段階バージョンが上がる奇妙なビューとなった。
奇数回のリフレッシュは実質意味がない。
その上、これがやっかいなのだが、偶数回ではビューが見てしまっているリザーブに対してそのままリフレッシュをしてしまう。
つまり、あれだけ避けたくてローテーションまで組んだ ACCESS EXCLUSIVE LOCK が、実際には2回に1回発生していたことになる。
悲しい以外の言葉がない。
実際に何が起こっていたか
- なんか反映遅いよね
- 2回連続でリフレッシュすると変わる気がする
- よくわかんないねえ
- とりあえずこのマテビューに修正入るんで確認します
- あ〜〜〜そういうことか!!!!!
解決したかった
とりあえず一旦の対策として、リフレッシュして3回リネーム後、改めてビューを再定義して、リザーブじゃないほうに紐付けるとしてみた。
deadlock detected
あ〜あ
たぶん、ビューの再定義でローテーションせず単純に CREATE OR REPLACE VIEW を使ったので、検索されてきたSELECT文が ACCESS SHARE LOCK を取っている間に、この再定義が ACCESS EXCLUSIVE LOCK を取り、 ハマってしまったのではないか、と予想している。
幸いにもすぐロックは解除され、無限にガッチリハマってしまったというほどの事故ではなかったのだが、それは混雑具合に左右されうる話。ちょっと許容できなかった。
じゃあどうすればよかったのか
どうすればいいんですか?
- 9.4以降にバージョンアップする
- そもそもマテビューをFROM句とするビューを作らない
- DB設計を最初からちゃんとする
のどっちかが正解っていうか両方やるのが正解だと思う。
なかなかうまくはいかないけど。