contrib/ltreeを使ってみる(2) さまよえる町田問題

はじめに

ちょっと前にcontrib/ltreeを使った検索をやってみたんだけど、今度はltreeをどう更新するのかを検証してみる。
なお、検証データ内の地名等はだいたいフィクションです。

初期状態

スキーマは前回と同様にltree型のカラムを1つだけもったmapテーブルを使う。

map=# \d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | map  | table | postgres
(1 row)

前回は都道府県のノードを最上位に置いたが、今回はその上にNipponというノードを置いて全都道府県の親とする。こんな感じ。

map=# SELECT data FROM map ORDER BY data;
                  data                   
-----------------------------------------
map=# SELECT data FROM map ORDER BY data;
                  data                   
-----------------------------------------
 Nippon
 Nippon.Kanagawa
 Nippon.Kanagawa.Kawasaki
 Nippon.Kanagawa.Kawasaki.Kawasaki
 Nippon.Kanagawa.Kawasaki.Sachi
 Nippon.Kanagawa.Kawasaki.Tama
 Nippon.Kanagawa.Minamiashigara
 Nippon.Kanagawa.Minamiashigara.Hakone
 Nippon.Kanagawa.Minamiashigara.Yugawara
 Nippon.Kanagawa.Sagamihara
 Nippon.Kanagawa.Sagamihara.Chuuou
 Nippon.Kanagawa.Sagamihara.Midori
 Nippon.Kanagawa.Sagamihara.Minami
 Nippon.Kanagawa.Yamato
 Nippon.Kanagawa.Yokohama
 Nippon.Kanagawa.Yokohama.Aoba
 Nippon.Kanagawa.Yokohama.Kouhoku
 Nippon.Kanagawa.Yokohama.Midori
 Nippon.Kanagawa.Yokohama.Minami
 Nippon.Kanagawa.Yokohama.Nishi
 Nippon.Kanagawa.Yokohama.Tsurumi
 Nippon.Kanagawa.Yokosuka
(22 rows)

Tokyo配下のノードは面倒なので省略。

神は「町田あれ」と言われた。

神は「町田あれ」と言われた。すると神奈川に町田があった。

町田を神奈川に追加するには、Noppon.Kanagawaの配下にMachidaを追加したノードを生成してINSERTすれば良い。

ltree書式そのままをINSERTするとこうなる。

map=# SELECT data FROM map WHERE data ~ '*.Machida';
 data 
------
(0 rows)

map=# INSERT INTO map VALUES ('Nippon.Kanagawa.Machida');
INSERT 0 1
map=# SELECT data FROM map WHERE data ~ '*.Machida';
          data           
-------------------------
 Nippon.Kanagawa.Machida
(1 row)

神奈川が日本の配下である、という前提があればこういうシンプルな考えでも良いのだが、どこの国かは知らないけれど神奈川の下に町田を置きたいという場合には、以下のようにltreeを組み立ててからINSERTすべきなのだろう。

map=# SELECT * FROM map WHERE data ~ '*.Machida';
 data 
------
(0 rows)

map=# INSERT INTO map SELECT data || 'Machida' FROM (SELECT data FROM map WHERE data ~ '*.Kanagawa') t;
INSERT 0 1
map=# SELECT * FROM map WHERE data ~ '*.Machida';
          data           
-------------------------
 Nippon.Kanagawa.Machida
(1 row)

いずれにせよ町田は神奈川になりました。
もうこの時点で、ltree情報の更新の面倒くささが漂ってきましたね・・・

町田は相模原

(東西に)長い相模原には巻かれろ

いや、町田も十分東西に長いんですけどね。

地勢的にも文化的にも町田は相模原じゃね？という人は多いと思う。
なので、「町田を相模原市配下の町田区にしたい」のだが、これが意外と面倒くさい。

このためには、相模原市自身とその祖先のノードを取得して、相模原市の子孫として町田を連結したltreeを構築し、その値をUPDATEする必要がある。

更新前の状態。町田は神奈川に属しているが相模原には属していない。

map=# SELECT * FROM map WHERE data ~ '*.Machida';
          data           
-------------------------
 Nippon.Kanagawa.Machida
(1 row)

map=# SELECT * FROM map WHERE data ~ '*.Sagamihara.*';
               data                
-----------------------------------
 Nippon.Kanagawa.Sagamihara
 Nippon.Kanagawa.Sagamihara.Chuuou
 Nippon.Kanagawa.Sagamihara.Minami
 Nippon.Kanagawa.Sagamihara.Midori
(4 rows)

町田市を相模原市町田区にするためのUPDATE文。ああ、面倒くさい。

map=# UPDATE map SET data =                                                 
  (SELECT data FROM (SELECT data FROM map WHERE data ~ '*.Sagamihara') t ) || 'Machida'
  WHERE data ~ '*.Machida';
UPDATE 1

UPDATEするとこうなる。町田は相模原。

map=# SELECT * FROM map WHERE data ~ '*.Machida';
                data                
------------------------------------
 Nippon.Kanagawa.Sagamihara.Machida
(1 row)

map=# SELECT * FROM map WHERE data ~ '*.Sagamihara.*';
                data                
------------------------------------
 Nippon.Kanagawa.Sagamihara
 Nippon.Kanagawa.Sagamihara.Chuuou
 Nippon.Kanagawa.Sagamihara.Minami
 Nippon.Kanagawa.Sagamihara.Midori
 Nippon.Kanagawa.Sagamihara.Machida
(5 rows)

町田市を区として取り込んだ相模原市は、ついに神奈川県からの独立を考える・・・

復活の相模

復ッ活ッ！相模国復ッ活ッ！

町田市を取り込んだ相模原市は、ついに100万人を超える自治体となった。
100万人を超えれば県として十分独立可能ではないか。
ということで、相模原市は相模国（現状だと相模県か）として、神奈川県からの独立しようとするかもしれない。

ltree的には中間ノードの移動、ということになる。ああ、面倒くさい。

更新前の相模原市は当然ながら神奈川県に属している。

map=# SELECT * FROM map WHERE data ~ '.Sagamihara.';
data

Nippon.Kanagawa.Sagamihara
Nippon.Kanagawa.Sagamihara.Chuuou
Nippon.Kanagawa.Sagamihara.Minami
Nippon.Kanagawa.Sagamihara.Midori
Nippon.Kanagawa.Sagamihara.Machida
(5 rows)

相模原市を相模原県に昇格させる場合には、以下のようなUPDATE文を発行する。

map=# UPDATE map SET data =
subpath(data, 0,index(data, 'Nippon')+1) || subpath(data, index(data, 'Sagamihara'))
WHERE data ~ '.Sagamihara.';
UPDATE 5

UPDATE文実行後の相模原がどうなっているかというと

map=# SELECT data FROM map WHERE data ~ '.Sagamihara.';
data

Nippon.Sagamihara
Nippon.Sagamihara.Chuuou
Nippon.Sagamihara.Minami
Nippon.Sagamihara.Midori
Nippon.Sagamihara.Machida
(5 rows)

相模県は神奈川県と同レベルになりました。
（なんと、相模原県は我が故郷、島根県よりも人口多いんだぜ！）

# 町田は死なず、ただ消え去るのみ

> 町田は死なず、ただ消え去るのみ」

町田が相模原に統合された場合、神奈川県からの相模原市の独立（そして神奈川県との果てしなき抗争）を避けるために、やっぱり、神奈川県の管轄から町田は外れるべきかもしれない。
ということで、町田をltreeから削除することにする。
これは比較的シンプル。

削除前の状態。

map=# SELECT data FROM map WHERE data ~ '.Sagamihara.';
data

Nippon.Sagamihara
Nippon.Sagamihara.Chuuou
Nippon.Sagamihara.Minami
Nippon.Sagamihara.Midori
Nippon.Sagamihara.Machida
(5 rows)

この状態で末端がMachidaのものを削除する。

```                                    ^
map=# DELETE FROM map WHERE data ~ '*.Machida';
DELETE 1
map=# SELECT data FROM map WHERE data ~ '*.Sagamihara.*';
           data           
--------------------------
 Nippon.Sagamihara
 Nippon.Sagamihara.Chuuou
 Nippon.Sagamihara.Minami
 Nippon.Sagamihara.Midori
(4 rows)

町田は神奈川県の管理外に戻った。しかし、神奈川県は相模原を失い、相模原の奪還のために神奈川県は多大な労力と血を流すことになるのであった・・・。

※このエントリはフィクションですよ！

おわりに

ltteeによる階層情報の管理は、検索だけ考えるとシンプルに記述出来るのだが、更新はかなり面倒くさい印象がある。
ツリー情報の更新が頻繁なら、ltreeのような静的な階層情報管理よりも、CTEをベースとしたツリー管理にすべきかもしれないなあと。

こういうツリー構造をガチで管理したければ、更新はNeo4jのようなグラフデータベースの機能に任せ、PostgreSQLからはForeign Data Wrapperを作成して、FDW経由でグラフデータベースへの参照をするほうがいいのかもしれない。