『7つのデータベース 7つの世界』第2章を読んだ

『7つのデータベース 7つの世界』（2013年、オーム社）第2章を読んだ。

この記事は単なる日記であって、書籍の内容を要約しようとしたり、抜粋しようとしたりするものではありません。

PostgreSQL

第2章（最初）の題材は PostgreSQL だった。この本で取り上げられてゐる唯一の関係データベースである。1日目に入る前に、PostgreSQL とモジュールをインストールした[1]:

brew install postgres
brew services restart postgresql
createdb book
psql book

CREATE EXTENSION tablefunc;
CREATE EXTENSION dict_xsyn;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION pg_trgm;
CREATE EXTENSION cube;
\q

psql book -c \\dx とすると、インストールされてゐる拡張の一覧が印字される。

1日目はリレーションの基礎と CRUD、結合について学ぶ。適当に手を動かしながら読んだ。

  • Loompaland は『Charlie and the Chocolate Factory』に登場する地名らしい。
  • events.venue_id の型は integer にした。
  • 宿題では 5.4. Constraints や 52.11. pg_class などを参考にした。
  • 宿題のやってみよう1の選択は SELECT のこと。

2日目に入る前に、psql では補完がうまくいかないことが多かったので、pgcli をインストールした。こゝからは pgcli book でサーバーに接続する。

2日目も適当に読んだ。

  • p.21 の INSERT クエリを発行してゐないと、p.22 の min max を使ったクエリが

    +--------+--------+
| min    | max    |
|--------+--------|
| <null> | <null> |
+--------+--------+

    を返してしまふ。

  • pp.24–25 の

    我々は、PARTITION BY を GROUP BY と同じものだと考えている。ただし、SELECT の外側で結果をグループ化するのではなく（結果の行数をまとめるのではなく）、グループ化した値を別のフィールド（グループ化した変数や属性を計算したもの）として返すのである。SQL 的に言えば、結果セットの PARTITION に集約関数 OVER を適用した結果を返すのである。

    はよく理解できなかったので原文に当たった[2]。この部分の原文は

    We like to think of PARTITION BY as akin to GROUP BY, but rather than grouping the results outside of the SELECT attribute list (and thus combining the results into fewer rows), it returns grouped values as any other field (calculating on the grouped variable but otherwise just another attribute). Or in SQL parlance, it returns the results of an aggregate function OVER a PARTITION of the result set.

    だった。OVERが前置詞として使はれてゐることや、PARTITIONにもわざわざ不定冠詞が付けてあることから考へると、マークした部分の訳は、

    結果集合の分割PARTITION[3]全ぜん體たいに亙ってOVER集約関数を適用した結果を返す

    とでもすべきだったと思ふ。

    結果集合the result setといふのは恐らく、FROM 句や WHERE 句などの結果のことだと思ふ。

  • createlang は PostgreSQL 9.1 で非推奨になり、10 で廃止された。p.28 では createlang book --list の代はりに psql book -c \\dL を使った。

  • PostgreSQL 9.3 では自動的に更新可能な VIEW が導入された。リリースノート。CREATE VIEW には、

    ビューは、次の条件を全て満たす場合、自動的に更新可能です:

    • ビューが FROM リストにたゞ1つのエントリーを持つ。そのエントリーはテーブルまたは他の更新可能なビューである。
    • ビューの定義がトップレベルに WITH 句、DISTINCT 句、GROUP BY 句、HAVING 句、LIMIT 句および OFFSET 句のいづれも含まない。
    • ビューの定義がトップレベルに集合演算（UNION、INTERSECT および EXCEPT）を含まない。
    • ビューの選択selectリストがいかなる集約、ウィンドウ関数、あるいは集合を返す関数をも含まない。
     原文

    A view is automatically updatable if it satisfies all of the following conditions:

    • The view must have exactly one entry in its FROM list, which must be a table or another updatable view.
    • The view definition must not contain WITH, DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clauses at the top level.
    • The view definition must not contain set operations (UNION, INTERSECT or EXCEPT) at the top level.
    • The view's select list must not contain any aggregates, window functions or set-returning functions.

    とある。そのため、本の通りに進めると、p.30 の UPDATE クエリが成功してしまふ。そこで、同じく p.30 の CREATE OR REPLACE VIEW クエリに LIMIT 100 を附け加へて発行した。さうすると、無事に

    cannot update view "holidays"
DETAIL:  Views containing LIMIT or OFFSET are not automatically updatable.
HINT:  To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.

    と出力された。あとは学校のルール？から続行した。

  • 宿題のやってみよう3はかなり難しかった。次のやうにして解いた:

    1. ピボットテーブルは値を集約することはできるが、値の無い箇所を埋めることはできない。よって、目標の月に含まれる日の一覧が必要となる。これは、

      SELECT * FROM generate_series(
  '2012-02-01',
  '2012-03-01'::date - '1 day'::interval,
  '1 day'::interval
) date;

      のやうにして取得した。これをビュー Feb_2012 とする。

    2. 次に、日ごとにその日にあったイベントの個数を数へる。まづ、Feb_2012 に events を左結合する:

      SELECT date, title, starts
FROM Feb_2012
LEFT JOIN events
  ON date <= starts AND starts < date + '1 day'::interval

    3. そして、date でグループしてから events の個数を数へる:

      SELECT date, count(events)
FROM Feb_2012
LEFT JOIN events
  ON date <= starts AND starts < date + '1 day'::interval
GROUP BY date
ORDER BY date

    4. 次に、週と曜日を SELECT してみる:

      SELECT date,
       extract(week from date) week,
       extract(dow from date) dow
FROM Feb_2012

      すると、次のやうに出力される:

      +---------------------+------+-----+
| date                | week | dow |
|---------------------+------+-----|
| 2012-02-01 00:00:00 | 5    | 3   |
| 2012-02-02 00:00:00 | 5    | 4   |
| 2012-02-03 00:00:00 | 5    | 5   |
| 2012-02-04 00:00:00 | 5    | 6   |
| 2012-02-05 00:00:00 | 5    | 0   |
| 2012-02-06 00:00:00 | 6    | 1   |
| 2012-02-07 00:00:00 | 6    | 2   |
| 2012-02-08 00:00:00 | 6    | 3   |
| 2012-02-09 00:00:00 | 6    | 4   |
| 2012-02-10 00:00:00 | 6    | 5   |
⋮

      週が変はるところと曜日が0に戾るところとが異なってゐるが、これは、extract の week は ISO 週（月曜日で始まる。）を返し、dow は日曜始まりの値を返すからである。9.9. Date/Time Functions and Operators も参照。

    5. よって、日曜始まりのカレンダーを作るには、week を補正する必要がある。dow が0なら week を1だけ増やす。

      SELECT date,
       extract(week from date) + (CASE WHEN extract(dow from date) = 0 THEN 1 ELSE 0 END) week,
       extract(dow from date) dow
FROM Feb_2012

      とした。もっと巧い方法がありさう。

    6. 必要なものは date ではなくイベントの個数だった:

      SELECT extract(week from date) + (CASE WHEN extract(dow from date) = 0 THEN 1 ELSE 0 END) week,
       extract(dow from date) dow,
       count(events)
FROM Feb_2012
LEFT JOIN events
  ON date <= starts AND starts < date + '1 day'::interval
GROUP BY date
ORDER BY date

    7. 最後に、dow つまり 0–6 をカテゴリにして、このテーブルのピボットテーブルを作成した:

      SELECT *
FROM crosstab(
  'SELECT extract(week from date) + (CASE WHEN extract(dow from date) = 0 THEN 1 ELSE 0 END) week,
          extract(dow from date) dow,
          count(events)
   FROM Feb_2012
   LEFT JOIN events
     ON date <= starts AND starts < date + ''1 day''::interval
   GROUP BY date
   ORDER BY date',
  'SELECT * from generate_series(0, 6)')
AS (
  week int,
  Sun int, Mon int, Tue int, Wed int, Thu int, Fri int, Sat int
)
ORDER BY week

    8. 本では 0 や <null> を取り除くことになってゐるが、省略。

3日目は全文検索と多次元クエリについて学んだ。

  • p.38 に次のやうな入出力がある:

    SELECT show_trgm('Avatar');

                  show_trgm              
-------------------------------------
 {"  a"," av","ar ",ata,ava,tar,vat}

    手元の環境で実行しても同じ結果が得られた。多少引数を変へても同様だった。ava vat ata tar だけを返すか、r も返すかゞ適当だと思ふが、どういふ仕様なんだらう。なほ、

    SELECT show_trgm('Avatar  ')

    のやうに trailing spaces を補っても、r は含まれない。

  • pgcli では \dFd がうまく動作しなかった。psql なら動作する。

  • p.40 の

    「machst」（行う）や「gerade」（今）は語幹だ。

    はかなり違和感があった。machst は語幹ではなく定形なので、

    「machst」（行う）や「gerade」（今）は語幹化されるare stemmed。

    くらゐが妥当だと思ふ。

  • p.41 の3つ目の EXPLAIN クエリは、行数が少ないときは、上2つと同じ Seq Scan on movies プランを表示する。Seven Databases in Seven Weeks, Second Edition から[4] Source Code をダウンロードして、

    \i /path/to/code/postgres/movies_data.sql

    を発行すると、Bitmap Heap Scan on movies プランを表示するやうになった。恐らく Postgres がどこかのバージョンで、行数が少ないときは全表スキャンするやうになったのだらう[5]。

    事前に SET enable_seqscan = false; としておけば、行数が少なくても Bitmap Heap Scan on movies プランを表示する。enable_seqscan を false にしても、全表スキャンが完全に制御されるわけではない。20.7. Query Planning も参照。

まとめの節では PostgreSQL の強みと弱みが述べられてゐた。とくに、

他のオープンソースデータベースには複雑なライセンス規約があるが、PostgreSQL は純粋なオープンソースソフトウェアだ。誰もコードを所有していない。誰でも何でも好きなことができる（著作権を主張すること以外）。

は大きな強みだと思ふ。The PostgreSQL License はパーミッシブ・ライセンスの中でもかなり緩やかで、MIT ライセンスや1条項 BSD ライセンスによく似た性質を持つ。

  1. 本では PostgreSQL 9.0 が使はれてゐるが、あまり気にせずに最新版をインストールした。psql -V は

    psql (PostgreSQL) 14.4

    を印字する。バージョンによる差異があったら本文で触れる。 ↩︎

  2. わざわざ初版を買ふのは勿もっ體たいない気がしたので、Second Edition を買った。この部分には差異は無いと思ふ。 ↩︎

  3. 集合の分割とは、

    • 空集合を含まず、
    • 分割前の集合の被覆であり、
    • pairwise disjoint である

    やうな集合族のことである。集合の被覆は、こゝでは、その集合の部分集合族であって、その族の和集合が元の集合と一致するものを言ふ。 ↩︎

  4. First Edition 用のページは恐らくもう公開されてゐない。 ↩︎

  5. PostgreSQL 9.1 では、0行でも Bitmap Heap Scan on movies プランが表示された。 ↩︎