勉強日記

毎日投稿

OSS-DB試験対策 緑本 ch10 トランザクション

www.shoeisha.co.jp


トランザクションの概念

トランザクションについて

  • 複数の処理をグループ化して論理的に1つの処理とするやつ

ACID特性について

トランザクションを制御するコマンド

BEGIN / START TRANSACTION / COMMIT / ROLLBACK / ABORT

postgres=# \echo :AUTOCOMMIT
on
postgres=# CREATE TABLE tbl (c1 int);
CREATE TABLE

postgres=# BEGIN;
BEGIN

postgres=# INSERT INTO tbl VALUES (1);
INSERT 0 1

postgres=# SELECT * FROM tbl;
 c1 
----
  1
(1 row)

postgres=# COMMIT;
COMMIT


postgres=# SELECT * FROM tbl;
 c1 
----
  1
(1 row)
  • ROLLBACK
postgres=# BEGIN;
BEGIN

postgres=# INSERT INTO tbl VALUES (2);
INSERT 0 1

postgres=# SELECT * FROM tbl;
 c1 
----
  1
  2
(2 rows)

postgres=# ROLLBACK;
ROLLBACK


postgres=# SELECT * FROM tbl;
 c1 
----
  1
(1 row)

SAVEPOINT

  • 公式
  • 部分的にROLLBACKしたいときに使用
  • 同名のSAVEPOINTはstackされる
    • RELEASEでpopする
postgres=# BEGIN;
BEGIN

postgres=# INSERT INTO tbl VALUES(1);
INSERT 0 1

postgres=# SAVEPOINT sp1;
SAVEPOINT

postgres=# INSERT INTO tbl VALUES(2);
INSERT 0 1

postgres=# SAVEPOINT sp1;
SAVEPOINT

postgres=# INSERT INTO tbl VALUES(3);
INSERT 0 1

postgres=# SELECT * FROM tbl;
 c1 
----
  1
  2
  3
(3 rows)
  • sp1まで戻す
postgres=# ROLLBACK TO sp1;
ROLLBACK

postgres=# SELECT * FROM tbl;
 c1 
----
  1
  2
(2 rows)
  • ここで(新しいほうの)sp1に戻す:何も起きない
    • すでにsp1にいるから
postgres=# ROLLBACK TO sp1;
ROLLBACK

postgres=# SELECT * FROM tbl;
 c1 
----
  1
  2
(2 rows)
  • sp1をpopして、古いsp1に戻す
postgres=# RELEASE sp1;
RELEASE

postgres=# ROLLBACK TO sp1;
ROLLBACK

postgres=# SELECT * FROM tbl;
 c1 
----
  1
(1 row)
postgres=# ROLLBACK;
ROLLBACK

postgres=# SELECT * FROM tbl;
 c1 
----
(0 rows)

トランザクション分離性

  • ACIDのIの話
    • 分離性レベルに応じてトランザクションがどのような振る舞いを見せるか
    • 分離性の設定はどう行うか

トランザクションの分離レベル

phenomina

Dirty Fuzzy
(Unrepeatable Read)
Phantom Serialization Anomaly
Read Uncommitted
Read Committed 起こらない
Repeatable Read 起こらない 起こらない
Serializable 起こらない 起こらない 起こらない 起こらない

postgres.conf

#default_transaction_isolation = 'read committed'

分離レベルの設定

postgres=# SELECT name,setting,enumvals,context FROM pg_settings WHERE name~'transaction_isolation';
             name              |    setting     |                               enumvals                               | context 
-------------------------------+----------------+----------------------------------------------------------------------+---------
 default_transaction_isolation | read committed | {serializable,"repeatable read","read committed","read uncommitted"} | user
 transaction_isolation         | read committed | {serializable,"repeatable read","read committed","read uncommitted"} | user
(2 rows)
postgres=# SET transaction_isolation TO 'serializable';
SET

postgres=# SHOW transaction_isolation;
 transaction_isolation 
-----------------------
 serializable
(1 row)

postgres=# COMMIT;
COMMIT

postgres=# SHOW transaction_isolation;
 transaction_isolation 
-----------------------
 read committed
(1 row)
postgres=# SET default_transaction_isolation = 'serializable';
SET

postgres=# SHOW transaction_isolation;
 transaction_isolation 
-----------------------
 serializable
(1 row)

postgres=# SHOW default_transaction_isolation;
 default_transaction_isolation 
-------------------------------
 repeatable read
(1 row)
postgres=# BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN

postgres=# SHOW transaction_isolation;
 transaction_isolation 
-----------------------
 read committed
(1 row)

分離性による振る舞いの違い

  • SERIALIZABLEの場合、直列化異常が起きうるような更新はエラーが出る
(tx1)
postgres=# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
(tx2)
postgres=# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN

postgres=# SELECT * FROM tbl WHERE c1 = 1;
 c1 
----
  1
(1 row)
(tx1)
postgres=# UPDATE tbl SET c1 = 0 WHERE c1 = 1;
UPDATE 1

postgres=# COMMIT;
COMMIT;
(tx2)
postgres=# UPDATE tbl SET c1 = c1 - 1 WHERE c1 = 1;
ERROR:  current transaction is aborted, commands ignored until end of transaction block

ロック

  • 更新処理時の矛盾を避けるための排他処理
  • 公式
    • いっぱいある

行ロック

| Requested\Current | For Key Share | For Share | For No Key Update | For Update | | For Key Share | | | | X | | For Share | | | X | X | | For No Key Update | | X | X | X | | For Update | X | X | X | X |

  • 「key」はFKのこと
  • 直列化異常を防ぐやつ
    • SERIALIZABLEならば、そもそもロックが必要な更新をするとエラーになる
BEGIN;

SELECT col FROM tbl FOR UPDATE;
...

COMMIT;

テーブルロック

Command:     LOCK
Description: lock a table
Syntax:
LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]

where lockmode is one of:

    ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
    | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

URL: https://www.postgresql.org/docs/12/sql-lock.html
  • EXCLUSIVE
    • CUDブロック
  • ACCESS EXCLUSIVE
  • あまり積極的に使うものではない
    • ALTER TABLEで暗黙的にACCESS EXCLUSIVEが取得されたりする

デッドロック

Laravel Shibuya 6 IRTまとめ + 個人的所感・補足等


laravel-shibuya.connpass.com

IRT1 PHP Track

自分の職場はテストを書く文化がないんですが、テストやCIといったツールを会社に広めて導入につなげていったような体験談とか、うかがってみたいです。

  • (より一般に)「新しい文化」を広めるために皆さんどうしていますか?
  • 自動テストを書かない理由
    • 慣れるまで大変だから
    • 工数に関する心配があるから
      • 工数をかけた割に意味がなかったら…?
      • 「(仕様の)変更が多いから工数がかさむのでテスト書かない」
        • 変更が多いからこそ書くのでは?
      • すぐにリターンがあるわけではない
        • 【補】Economics of Test Automation (xUnit Test Patterns)
  • 書く理由
    • バグを防ぐため
      • その場合、テストをpushする必要ある?
        • 自分用ローカルで良いのでは
        • 他人を巻き込むと学習コスト、スイッチングコストが生じる
          • 「なんで今までのやり方から変えなきゃいけないの」
    • プロダクションコードと同じバージョン管理下の「動く仕様書」として
      • テストがテストであることはたまたま、副次的なもの
      • とはいえ技術者じゃないと読めないよね
        • 【所感】非技術者でも読み書きできるようにBDDフレームワークが生まれたはず
          • 実際どんなものかは使ったことがないのでわからない…
    • 開発のサイクルを快適に回すため
      • 【所感】進捗のバロメータとしてのテスト、ということかな
    • 安心感をもたらすため
      • 言語やフレームワークのバージョンアップ等、影響範囲が甚大な変更
        • テストがないと怖くて無理
      • 仕様どおり作れている
        • 手戻りが少なくなり、結局工数を圧縮できることが多い(経験則)
      • 【補】バグ埋め込みを恐れずに変更・掃除できる
  • 導入失敗事例: 勝手に新しい文化を導入したら評価(査定)が下がった話
    • テストやCI、静的型付け等
    • 「技術に寄りすぎ」 by エンジニアの2番目に偉い人
    • 上の人が何を求めているかをまず知り、その人向けにカスタマイズした説得を
      • 根本的な考え方にすれ違いがあったりするもの
    • こっそり進めて、外堀を埋めてから上の人を説得すべきだった
      • 同僚が上司についてしまった…
  • 導入成功事例: Cake2 -> Laravelに載せ替えた際にCI・自動テストを導入した話
    • 抵抗勢力がなかったのが救い
      • 「慣れるための時間をくれればいいよ」
      • 「余計の工数は心配だけれど、上司が許してくれるなら安心」
  • 説得材料
    • 数字
        • バグ発生率
        • 長い目で見て工数がどれくらい減る
          • xUnit本の最初のほうに「長い目で効いてくる」というグラフが載っていたりはする
            • 【補】Economics of Test Automation (xUnit Test Patterns)
      • とはいえ難しい
        • その組織でうまくいくかどうかは、結局導入してみて比較してみないとわからない
        • 「よそはよそ、うちはうち」
    • コスパの良いテストから提案する
    • 説明して説得するよりも「まず書いてみろ」

IRT2 Laravel Track

みんなが思うlaravelのいい!こと、うーん?なところ

  • いい!
    • 機能が多い、便利
    • DIのしくみが強い
    • bladeが便利
    • Contract
      • 「わかってる人」が乗っかると強い
      • ドキュメントに載せるくらい重要視しているのがLaravelのえらいところ
    • 使う中で設計原則が学べる
    • 初動が速い
      • ビルトインサーバーでサクッと動かせる
  • うーん?
    • 便利だけれど、FWのコードを掘っていくと設計がまずい
    • 自由
      • CoCの逆な感じ
      • いい!ことと表裏一体ではある
      • Eloquent
        • 詳しい人がいないと危ない使い方ができてしまう
      • (rspecと比べて)テストの命名がバラバラ
      • わかっていない人がContractに乗っからずに散らかした話(ぼくの前職)
        • AWS Cognitoを用いた独自認証
        • 謎のAuthLibを毎回呼ぶ。Auth::user()とか使えない
      • ロジックを分割しよう、となったときの分け方
      • 総論、牽引役がチームにしないと散らかる
    • わかってない人がContractに乗っからずに散らかすとつらい

bladeテンプレート内のロジックをヘルパモジュールに逃がしつつ、デザイナー(非プログラマ)と協業するには(ぼく質問)

【補】補足

index.blade.php

...
@isset($someInfo)
  <p>
    <ul>
      @foreach($someInfo as $id => $line)
        <li>{{ $line }}</li>
      @endforeach
    </ul>
  </p>
@else
  <p>
    情報がありません
  <\p>
@endisset
...
  • isset等の制御構造がbladeに書かれている
  • デザイナー「あたしHTMLとCSSしかわからないんですぅ〜」となりがち

index.blade.php

...
{{ $someHelper->showInfo() }}
...
  • かといって↑こうするとデザイナーが触れなくなる
  • ので、最近はbladeを小分けにしている

parts/info.blade.php

<p>
  <ul>
    @foreach($someInfo as $id => $line)
      <li>{{ $line }}</li>
    @endforeach
  </ul>
</p>

parts/noinfo.blade.php

<p>
  情報がありません
<\p>

SomeHelper.php

<?php
...

public function showInfo(): HtmlString
{
    if(!$this->hasInfo()) {
        return new HtmlString(
            view('path/to/parts/noinfo')->toHtml()
        );
    }
    
    $someInfo = $this->someInfo;
    return new HtmlString(
        view('path/to/parts/info', compact('someInfo'))->toHtml()
    );
}
  • 皆様どうされてますか…?という質問

議論

  • 自分もblade小分けにしてます派
  • 逆意見
    • 今時、デザイナーだからといって「if文とかわからないです…」では生き残れない
    • AngularだとうとReactだろうと必ず直面する
    • ifforくらい慣れてくれ
      • Laravelに限らず、他の言語他のフレームワークでも活かせる
      • else, switchはfat気味かも…?
  • 案件次第、組織次第
    • issetPHP固有だから、強制するのは忍びないところはある
      • デザイナーさんが関わるのはLaravelだけとは限らない
    • その組織において、そのデザイナーが関わるのがずっとPHP/Laravelだけならば覚えてもらうはアリ

IRT3 Laravel Track

Laravel+ GitHub Travis使ってる人ってどれくらいいるのでしょう。

  • (TravisCI利用者はあまりいなかった)
  • CIサービスは多種多様なので
    • ぶっちゃけ、CIのみならなんでもいい…
      • Jenkinsだろうが、自分で作ろうが構わない
      • 作ったことがあれば、特定のサービスを使ったことがなくてもちょっと調べればわかる
  • サービス別の色
  • CI環境付属のプロセス -- RDBとか使ってますか?(ぼく質問)
    • ランナーのlocalhostMySQL 3306とかPostgres 5432とか生えてるやつ
    • 便利なんだろうけれど、CI環境に依存してしまうので、本番環境で同様に動く保証がないですよね
      • 究極的には本番まで全部コンテナにしないと解決しない…

皆さん、現場ではどのようなスケジュールで仕事を回していますか?また、1画面作るのにどのくらい時間かかっていますか?

  • 画 面 数 で ガ ン ト チ ャ ー ト 引 く な
    • 画面じゃなくて機能で引け
  • Trivariate Estimates
    • 【補】The Clean Coder
    • 「見積もりは単一の値ではなく、分布である」という前提に基づく
    • 確度1%以下の楽観的見積もり(O)、悲観的見積もり(P)、および最も見込みの高い見積もり(N)の3つの値を用意する
    • 期待値μ = (O + 4N + P) / 6
    • 標準偏差σ = (P - O) / 6
    • 期待値μは、たいていNよりも悲観寄りになる
  • 誰向けの見積もりなのかによっても変わってくる
    • クライアント向け
    • 内部向け
      • 開発を円滑に回すためのやつ
  • とはいえ、画面数で見積もるのがわかりやすいのは確か
    • 営業に「機能で見積もれ」と言うのは酷
    • でも、画面数で見積もると現場が回らなくなっちゃう
    • 技術がわかる営業がいるか、に尽きる
    • アーキテクトが機能ごとに見積もって、画面ごとにならして「それっぽい見積もりを出す」のが良い落とし所かも

環境変数のバージョン管理

  • (時間切れ)
  • 【補】AWSマネージドサービス
    • ECSでは「タスク定義」に環境変数を持たせてバージョン管理できる
    • Lambdaも関数に環境変数を持たせてバージョン管理できる

OSS-DB試験対策 緑本 ch8 (2/2)

www.shoeisha.co.jp


テーブルの定義

テーブルの作成と削除

CREATE TABLE also automatically creates a data type that represents the composite type
corresponding to one row of the table. Therefore, tables cannot have the same name as any
existing data type in the same schema.

  • 【補】タプル自体が型なので、データ型と名前が衝突する
  • 【補】[USAGE権限]がGRANTされている必要あり
    • いろいろなデータベースオブジェクトを作れたりする権限
      • 手続きとか
      • 型とか
    • SQL標準由来
  • 作ってみる
postgres=# CREATE TABLE tbl(
postgres(# c1 int DEFAULT 10,
postgres(# c2 text,
postgres(# c3 text DEFAULT 'ABC',
postgres(# c4 timestamp DEFAULT now()
postgres(# );
CREATE TABLE


postgres=# \d tbl


                            Table "public.tbl"
 Column |            Type             | Collation | Nullable |   Default   
--------+-----------------------------+-----------+----------+-------------
 c1     | integer                     |           |          | 10
 c2     | text                        |           |          | 
 c3     | text                        |           |          | 'ABC'::text
 c4     | timestamp without time zone |           |          | now()


postgres=# INSERT INTO tbl(c2) VALUES('test');
INSERT 0 1

postgres=# SELECT * FROM tbl;
 c1 |  c2  | c3  |             c4             
----+------+-----+----------------------------
 10 | test | ABC | 2020-01-23 13:37:39.033761
(1 row)

制約

主キー(PRIMARY KEY)制約

  • 追加
postgres=# ALTER TABLE tbl ADD PRIMARY KEY (c1);

ALTER TABLE
\d tbl;
                            Table "public.tbl"
 Column |            Type             | Collation | Nullable |   Default   
--------+-----------------------------+-----------+----------+-------------
 c1     | integer                     |           | not null | 10
 c2     | text                        |           |          | 
 c3     | text                        |           |          | 'ABC'::text
 c4     | timestamp without time zone |           |          | now()
Indexes:
    "tbl_pkey" PRIMARY KEY, btree (c1)
  • 落とす
postgres=# ALTER TABLE tbl DROP CONSTRAINT tbl_pkey;
ALTER TABLE

postgres=# \d tbl
                            Table "public.tbl"
 Column |            Type             | Collation | Nullable |   Default   
--------+-----------------------------+-----------+----------+-------------
 c1     | integer                     |           | not null | 10
 c2     | text                        |           |          | 
 c3     | text                        |           |          | 'ABC'::text
 c4     | timestamp without time zone |           |          | now()
  • 名前pkeyを指定して主キー制約を追加する
postgres=# ALTER TABLE tbl ADD CONSTRAINT pkey PRIMARY KEY(c1);

ALTER TABLE

postgres=# \d tbl
\d tbl
                            Table "public.tbl"
 Column |            Type             | Collation | Nullable |   Default   
--------+-----------------------------+-----------+----------+-------------
 c1     | integer                     |           | not null | 10
 c2     | text                        |           |          | 
 c3     | text                        |           |          | 'ABC'::text
 c4     | timestamp without time zone |           |          | now()
Indexes:
    "pkey" PRIMARY KEY, btree (c1)

ユニーク制約

  • 追加
postgres=# \d tbl
                            Table "public.tbl"
 Column |            Type             | Collation | Nullable |   Default   
--------+-----------------------------+-----------+----------+-------------
 c1     | integer                     |           | not null | 10
 c2     | text                        |           |          | 
 c3     | text                        |           |          | 'ABC'::text
 c4     | timestamp without time zone |           |          | now()
Indexes:
    "pkey" PRIMARY KEY, btree (c1)
    "tbl_c2_key" UNIQUE CONSTRAINT, btree (c2)
  • 落とす
postgres=# ALTER TABLE tbl DROP CONSTRAINT tbl_c2_key;
ALTER TABLE

postgres=# \d tbl
                            Table "public.tbl"
 Column |            Type             | Collation | Nullable |   Default   
--------+-----------------------------+-----------+----------+-------------
 c1     | integer                     |           | not null | 10
 c2     | text                        |           |          | 
 c3     | text                        |           |          | 'ABC'::text
 c4     | timestamp without time zone |           |          | now()
Indexes:
    "pkey" PRIMARY KEY, btree (c1)
  • 名前指定して追加
postgres=# ALTER TABLE tbl ADD CONSTRAINT unq_c2 UNIQUE(c2);
ALTER TABLE

postgres=# \d tbl;
                            Table "public.tbl"
 Column |            Type             | Collation | Nullable |   Default   
--------+-----------------------------+-----------+----------+-------------
 c1     | integer                     |           | not null | 10
 c2     | text                        |           |          | 
 c3     | text                        |           |          | 'ABC'::text
 c4     | timestamp without time zone |           |          | now()
Indexes:
    "pkey" PRIMARY KEY, btree (c1)
    "unq_c2" UNIQUE CONSTRAINT, btree (c2)

NOT NULL制約

  • 【補】公式/カラムのconstraint
    • 制約名がついてない
    • のでALTER TABLE <テーブル名> DROP CONSTRAINT <制約名> では落とせない
  • つけはずし
postgres=# \d tbl
                            Table "public.tbl"
 Column |            Type             | Collation | Nullable |   Default   
--------+-----------------------------+-----------+----------+-------------
 c1     | integer                     |           | not null | 10
 c2     | text                        |           | not null | 
 c3     | text                        |           |          | 'ABC'::text
 c4     | timestamp without time zone |           |          | now()
Indexes:
    "pkey" PRIMARY KEY, btree (c1)
    "unq_c2" UNIQUE CONSTRAINT, btree (c2)

postgres=# ALTER TABLE tbl ALTER COLUMN c2 DROP NOT NULL;
ALTER TABLE

postgres=# \d tbl
                            Table "public.tbl"
 Column |            Type             | Collation | Nullable |   Default   
--------+-----------------------------+-----------+----------+-------------
 c1     | integer                     |           | not null | 10
 c2     | text                        |           |          | 
 c3     | text                        |           |          | 'ABC'::text
 c4     | timestamp without time zone |           |          | now()
Indexes:
    "pkey" PRIMARY KEY, btree (c1)
    "unq_c2" UNIQUE CONSTRAINT, btree (c2)

外部キー制約

  • 参照整合性制約とも
  • primary key もしくは unique 制約のある列を参照できる
    • 【補】nullable
  • テーブル生成時に指定してみる
postgres=# CREATE TABLE master(id int PRIMARY KEY);
CREATE TABLE

postgres=# CREATE TABLE tbl(id int REFERENCES master(id));
CREATE TABLE


postgres=# \d master
               Table "public.master"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
Indexes:
    "master_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "tbl" CONSTRAINT "tbl_id_fkey" FOREIGN KEY (id) REFERENCES master(id)


postgres=# \d tbl
                Table "public.tbl"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 
Foreign-key constraints:
    "tbl_id_fkey" FOREIGN KEY (id) REFERENCES master(id)
  • データ挿入してみる
postgres=# INSERT INTO master VALUES(1);
INSERT INTO master VALUES(1);
INSERT 0 1

postgres=# INSERT INTO tbl VALUES(1);
INSERT INTO tbl VALUES(1);
INSERT 0 1

postgres=# INSERT INTO tbl VALUES(2);
INSERT INTO tbl VALUES(2);
ERROR:  insert or update on table "tbl" violates foreign key constraint "tbl_id_fkey"
DETAIL:  Key (id)=(2) is not present in table "master".


postgres=# SELECT * FROM master;
 id 
----
  1
(1 row)

postgres=# SELECT * FROM tbl;
 id 
----
  1
(1 row)
  • 【補】複数行INSERT時、一部の行がFK制約に違反した場合、全部失敗する
    • Atomicity
postgres=# INSERT INTO tbl VALUES(1),(2);
ERROR:  insert or update on table "tbl" violates foreign key constraint "tbl_id_fkey"
DETAIL:  Key (id)=(2) is not present in table "master".


postgres=# SELECT * FROM tbl;
SELECT * FROM tbl;
 id 
----
  1
(1 row)
postgres=# ALTER TABLE tbl DROP CONSTRAINT tbl_id_fkey;
ALTER TABLE

postgres=# \d tbl
                Table "public.tbl"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 



postgres=# ALTER TABLE tbl ADD CONSTRAINT tbl_id_fkey_cascade FOREIGN KEY(id) REFERENCES master(id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE

postgres=# \d tbl
                Table "public.tbl"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 
Foreign-key constraints:
    "tbl_id_fkey_cascade" FOREIGN KEY (id) REFERENCES master(id) ON UPDATE CASCADE ON DELETE CASCADE
  • 更新してみる
    • 子テーブルの更新は制約違反
    • 親テーブルの更新は波及する
postgres=# UPDATE tbl SET id = 2 WHERE id = 1;
ERROR:  insert or update on table "tbl" violates foreign key constraint "tbl_id_fkey_cascade"
DETAIL:  Key (id)=(2) is not present in table "master".

postgres=# UPDATE master SET id = 2 WHERE id = 1;
UPDATE 1
postgres=# 


postgres=# SELECT * FROM master;
 id 
----
  2
(1 row)

postgres=# SELECT * FROM tbl;
 id 
----
  2
(1 row)
  • 削除
    • 親テーブルの行削除は子に波及する
postgres=# DELETE FROM master WHERE id = 2;
DELETE 1


postgres=# SELECT * FROM master;
 id 
----
(0 rows)

postgres=# SELECT * FROM tbl;
 id 
----
(0 rows)
  • 【補】親テーブルDROP
  • 怒られる
postgres=# DROP table master;
ERROR:  cannot drop table master because other objects depend on it
DETAIL:  constraint tbl_id_fkey_cascade on table tbl depends on table master
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
  • 子テーブルのFK制約もDROPするには、CASCADEを指定する
postgres=# DROP table master CASCADE;
NOTICE:  drop cascades to constraint tbl_id_fkey_cascade on table tbl
DROP TABLE
  • 子テーブル自体は残る
postgres=# \d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | tbl  | table | postgres
(1 row)
  • FK制約はDROPされる
postgres=# \d tbl
                Table "public.tbl"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 

チェック制約

  • 偶数のみ受け入れるカラムを定義してみる
postgres=# CREATE TABLE tbl(even int CHECK(mod(even, 2) = 0));
CREATE TABLE


postgres=# \d tbl
                Table "public.tbl"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 even   | integer |           |          | 
Check constraints:
    "tbl_even_check" CHECK (mod(even, 2) = 0)
  • 制約が効くことを確認
postgres=# INSERT INTO tbl (even) VALUES (2);
INSERT 0 1

postgres=#  INSERT INTO tbl (even) VALUES (3);
ERROR:  new row for relation "tbl" violates check constraint "tbl_even_check"
DETAIL:  Failing row contains (3).

ドメイン制約

  • チェック制約を再利用するためのやつ
postgres=# CREATE DOMAIN even AS int CHECK (mod(VALUE, 2) = 0);
CREATE DOMAIN

postgres=# CREATE TABLE tbl (id int, num even);
CREATE TABLE

postgres=# \d tbl
                Table "public.tbl"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 
 num    | even    |           |          | 
  • DROP
  • 使われているものをDROPしようとすると怒られる
postgres=# DROP DOMAIN even;
ERROR:  cannot drop type even because other objects depend on it
DETAIL:  column num of table tbl depends on type even
HINT:  Use DROP ... CASCADE to drop the dependent objects too.


postgres=# DROP DOMAIN even CASCADE;
NOTICE:  drop cascades to column num of table tbl
DROP DOMAIN


postgres=# \d tbl
                Table "public.tbl"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 
  • カラムごと消えた

テーブル定義の変更

  • テーブルの
    • 名前変更
    • 所有者変更
  • 列の
    • 名前変更
    • 追加
    • 削除
  • 制約も追加・削除できる(先述)

パーティション

  • 公式
  • テーブルを水平分割
  • 利点
    • 読み出しパフォーマンス向上
      • アクセス対象の絞り込み
    • 更新パフォーマンス向上
    • 大量データの追加・削除
      • partitionの付け外しで実現できる
    • アクセス頻度の低いデータを遅くて安いストレージに置ける
  • 作ってみる
postgres=# CREATE TABLE tbl (c1 serial, c2 text, c3 date) PARTITION BY range (c3);
CREATE TABLE


postgres=# \d tbl
                      Partitioned table "public.tbl"
 Column |  Type   | Collation | Nullable |             Default             
--------+---------+-----------+----------+---------------------------------
 c1     | integer |           | not null | nextval('tbl_c1_seq'::regclass)
 c2     | text    |           |          | 
 c3     | date    |           |          | 
Partition key: RANGE (c3)
Number of partitions: 0
  • 【補】partitionのない状態でデータを投入しようとするとエラー
postgres=# INSERT INTO tbl(c2, c3) VALUES ('hoge', '2020-01-01');
ERROR:  no partition of relation "tbl" found for row
DETAIL:  Partition key of the failing row contains (c3) = (2020-01-01).
postgres=# CREATE TABLE tbl_y2020m04 PARTITION OF tbl FOR VALUES FROM ('2020-04-01') TO ('2020-04-30');
CREATE TABLE
postgres=# CREATE TABLE tbl_y2020m05 PARTITION OF tbl FOR VALUES FROM ('2020-05-01') TO ('2020-05-31');
CREATE TABLE
postgres=# CREATE TABLE tbl_y2020m06 PARTITION OF tbl FOR VALUES FROM ('2020-06-01') TO ('2020-06-30');
CREATE TABLE


postgres=# \d
                  List of relations
 Schema |     Name     |       Type        |  Owner   
--------+--------------+-------------------+----------
 public | tbl          | partitioned table | postgres
 public | tbl_c1_seq   | sequence          | postgres
 public | tbl_y2020m04 | table             | postgres
 public | tbl_y2020m05 | table             | postgres
 public | tbl_y2020m06 | table             | postgres
(5 rows)


postgres=# \d+ tbl
                                          Partitioned table "public.tbl"
 Column |  Type   | Collation | Nullable |             Default             | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------------------------------+----------+--------------+-------------
 c1     | integer |           | not null | nextval('tbl_c1_seq'::regclass) | plain    |              | 
 c2     | text    |           |          |                                 | extended |              | 
 c3     | date    |           |          |                                 | plain    |              | 
Partition key: RANGE (c3)
Partitions: tbl_y2020m04 FOR VALUES FROM ('2020-04-01') TO ('2020-04-30'),
            tbl_y2020m05 FOR VALUES FROM ('2020-05-01') TO ('2020-05-31'),
            tbl_y2020m06 FOR VALUES FROM ('2020-06-01') TO ('2020-06-30')



postgres=# \d tbl_y2020m04
                        Table "public.tbl_y2020m04"
 Column |  Type   | Collation | Nullable |             Default             
--------+---------+-----------+----------+---------------------------------
 c1     | integer |           | not null | nextval('tbl_c1_seq'::regclass)
 c2     | text    |           |          | 
 c3     | date    |           |          | 
Partition of: tbl FOR VALUES FROM ('2020-04-01') TO ('2020-04-30')
postgres=# INSERT INTO tbl(c2, c3) VALUES('hoge', '2019-01-01');
ERROR:  no partition of relation "tbl" found for row
DETAIL:  Partition key of the failing row contains (c3) = (2019-01-01).
postgres=# SELECT * FROM tbl;
 c1 | c2 |     c3     
----+----+------------
  3 | GW | 2020-05-03
(1 row)


postgres=# SELECT * FROM tbl_y2020m05;
 c1 | c2 |     c3     
----+----+------------
  3 | GW | 2020-05-03
(1 row)
  • 小分けのテーブル側に範囲外データをINSERTするとpartition constraint違反でエラー
postgres=# INSERT INTO tbl_y2020m05(c2, c3) VALUES('hoge', '2019-01-01');
ERROR:  new row for relation "tbl_y2020m05" violates partition constraint
DETAIL:  Failing row contains (4, hoge, 2019-01-01).
  • 【補】分割元をDROPすると小分けのテーブルも容赦なく消える
postgres=# \dt
                  List of relations
 Schema |     Name     |       Type        |  Owner   
--------+--------------+-------------------+----------
 public | tbl          | partitioned table | postgres
 public | tbl_y2020m04 | table             | postgres
 public | tbl_y2020m05 | table             | postgres
 public | tbl_y2020m06 | table             | postgres
(4 rows)

postgres=# DROP TABLE tbl;
DROP TABLE

postgres=# \dt
Did not find any relations.

シーケンス

postgres=# CREATE SEQUENCE even_seq START 2 INCREMENT BY 2 MAXVALUE 10 MINVALUE 0;
INCREMENT BY 2 MAXVALUE 10 MINVALUE 0;
CREATE SEQUENCE


postgres=# \ds+ even_seq
                         List of relations
 Schema |   Name   |   Type   |  Owner   |    Size    | Description 
--------+----------+----------+----------+------------+-------------
 public | even_seq | sequence | postgres | 8192 bytes | 
(1 row)
  • 【補】状態確認
postgres=# SELECT * FROM even_seq;
 last_value | log_cnt | is_called 
------------+---------+-----------
          2 |       0 | f
(1 row)
  • 値読み出し
postgres=# SELECT currval('even_seq');
ERROR:  currval of sequence "even_seq" is not yet defined in this session
  • 一度も呼び出していない (is_calledf) 状態だとエラー
  • 値セット
postgres=# SELECT setval('even_seq', 2);
 setval 
--------
      2
(1 row)


postgres=# SELECT * from even_seq;
 last_value | log_cnt | is_called 
------------+---------+-----------
          2 |       0 | t
(1 row)
  • is_calledがtになった
  • 再度値読み出し
    • 冪等
postgres=# SELECT currval('even_seq');
 currval 
---------
       2
(1 row)


postgres=# SELECT currval('even_seq');
 currval 
---------
       2
(1 row)
  • 次の値払い出し・返却
postgres=# SELECT nextval('even_seq');
 nextval 
---------
       4
(1 row)

postgres=# SELECT nextval('even_seq');
 nextval 
---------
       6
(1 row)

postgres=# SELECT nextval('even_seq');
 nextval 
---------
       8
(1 row)

postgres=# SELECT nextval('even_seq');
 nextval 
---------
      10
(1 row)

postgres=# SELECT nextval('even_seq');
ERROR:  nextval: reached maximum value of sequence "even_seq" (10)

ビュー

postgres=# CREATE TABLE numbers(num int);
CREATE TABLE

postgres=# INSERT INTO numbers VALUES(1),(2),(3);
INSERT 0 3


postgres=# CREATE VIEW squared AS SELECT num * num FROM numbers;
CREATE VIEW

postgres=# SELECT * FROM squared;
 ?column? 
----------
        1
        4
        9
(3 rows)


postgres=# \dv
\dv
         List of relations
 Schema |  Name   | Type |  Owner   
--------+---------+------+----------
 public | squared | view | postgres
(1 row)
  • DROP VIEWで落とす

インデックス

  • 【補】SQL標準にインデックスに関する規定はない

インデックスの概要

  • さくいん

インデックスの種類

PostgreSQL provides the index methods B-tree, hash, GiST, SP-GiST, GIN, and BRIN. Users can also
define their own index methods, but that is fairly complicated.

  • B-Tree
    • 一般的に使われるやつ
    • スカラの一致、大小不等号検索
      • 【補】NOT EQUALには使えない
  • SP-GiST
    • 公式
    • Space-Partitioned GiST
  • GiST
    • 公式
    • Generalized Search Tree
      • B木とかもこれで実装されうる
      • いろいろなデータ型を検索できる
      • 公式
    • 主に空間情報の検索に用いられる
  • GIN
  • ハッシュ
    • 値の一致検索がはやいやつ
  • BRIN

インデックスの作成

postgres=# CREATE TABLE tbl (c1 int, c2 text[], c3 point);
CREATE TABLE

postgres=# CREATE INDEX tbl_c1_index ON tbl (c1);
CREATE INDEX
postgres=# CREATE INDEX tbl_c2_index ON tbl USING gin (c2);
CREATE INDEX
postgres=# CREATE INDEX tbl_c3_index ON tbl USING gist (c3);
CREATE INDEX

postgres=# \di
                List of relations
 Schema |     Name     | Type  |  Owner   | Table 
--------+--------------+-------+----------+-------
 public | tbl_c1_index | index | postgres | tbl
 public | tbl_c2_index | index | postgres | tbl
 public | tbl_c3_index | index | postgres | tbl
(3 rows)
  • 【補】intに対してginとかgistとか作ろうとすると「演算子ねーぞ」と怒られる
  • DROP INDEXで消す
    • テーブルを消しても消える

マルチカラムインデックス

postgres=# CREATE TABLE tbl2 (c1 int, c2 int);
CREATE TABLE

postgres=# CREATE INDEX multi_idx ON tbl(c1,c2);
CREATE INDEX

postgres=# CREATE INDEX multi_idx ON tbl USING hash (c1,c2);
ERROR:  access method "hash" does not support multicolumn indexes
  • ハッシュ方式の複合インデックスは作れない

関数インデックス/式インデックス

  • 式や関数の結果をインデックス化できる
postgres=# CREATE INDEX added_index ON tbl2 ((c1 + c2));
CREATE INDEX

postgres=# CREATE INDEX squared_index_1 ON tbl2 (pow(c1,2));
CREATE INDEX

部分インデックス

  • テーブルの特定の範囲のデータのみにインデックスを張る
postgres=# CREATE INDEX partial_index ON tbl (c1) WHERE c1 < 100;
CREATE INDEX

トリガ

  • 挿入・更新・削除時にユーザ定義関数/プロシージャを実行

トリガの作成

Command:     CREATE TRIGGER
Description: define a new trigger
Syntax:
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

where event can be one of:

    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE

URL: https://www.postgresql.org/docs/12/sql-createtrigger.html
When Event Row-level Statement-level
BEFORE INSERT/UPDATE/DELETE Tables and foreign tables Tables, views, and foreign tables
TRUNCATE - Tables
AFTER INSERT/UPDATE/DELETE Tables and foreign tables Tables, views, and foreign tables
TRUNCATE - Tables
INSTEAD OF INSERT/UPDATE/DELETE Views -
TRUNCATE - -
  • SQL実行ごと/行ごと
  • いつ実行する
    • CUD前
    • CUD後
    • かわりに何かする
      • Viewの行ごとでのみ
CREATE TRIGGER view_insert
    INSTEAD OF INSERT ON my_view
    FOR EACH ROW
    EXECUTE FUNCTION view_insert_row();

ルール

ルールの作成

Command:     CREATE RULE
Description: define a new rewrite rule
Syntax:
CREATE [ OR REPLACE ] RULE name AS ON event
    TO table_name [ WHERE condition ]
    DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

where event can be one of:

    SELECT | INSERT | UPDATE | DELETE

URL: https://www.postgresql.org/docs/12/sql-createrule.html
  • SQLの置き換え(INSTEAD) or 別の処理を付け加える(ALSO)

スキーマ

  • 名前空間とも
  • 【補】だから\dnメタコマンド

スキーマの作成

Command:     CREATE SCHEMA
Description: define a new schema
Syntax:
CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification

where role_specification can be:

    user_name
  | CURRENT_USER
  | SESSION_USER

URL: https://www.postgresql.org/docs/12/sql-createschema.html
postgres=# CREATE USER syaro;
CREATE ROLE

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 syaro     |                                                            | {}


postgres=# CREATE SCHEMA my_schema AUTHORIZATION syaro;
CREATE SCHEMA


postgres=# \dn
   List of schemas
   Name    |  Owner   
-----------+----------
 my_schema | syaro
 public    | postgres
(2 rows)
  • 【補】schema_elementで、スキーマに含められるデータベースオブジェクトを指定する

スキーマへのテーブルの作成とスキーマ検索パス

  • 同名のデータベースオブジェクト(テーブルとか)が複数のスキーマに存在する場合の特定方法
    • データベースオブジェクトをスキーマ名で修飾する
      • インデックスは特定のスキーマ名で修飾できない
        • テーブルと一緒
    • スキーマ検索パスを指定する
  • スキーマ検索パス: search_path パラメータ
postgres=# SELECT name,setting,context FROM pg_settings WHERE name = 'search_path';
pg_settings WHERE name = 'search_path';
    name     |     setting     | context 
-------------+-----------------+---------
 search_path | "$user", public | user
(1 row)
  • デフォルト値: "$user", public
    • 現在のデータベースユーザ名と同名のスキーマを優先
  • 一般ユーザのSET文で更新できる
postgres=# CREATE TABLE public.tbl(col int);
CREATE TABLE

postgres=# CREATE TABLE my_schema.tbl(col int);
CREATE TABLE


postgres=# INSERT INTO public.tbl VALUES (1);
INSERT 0 1

postgres=# INSERT INTO my_schema.tbl VALUES (715);
INSERT 0 1


postgres=# SELECT * FROM tbl;
 col 
-----
   1
(1 row)

postgres=# SET search_path = 'my_schema';
SET

postgres=# SELECT * FROM tbl;
 col 
-----
 715
(1 row)

関数とプロシージャ

  • 利点
    • アプリケーション側での複雑な処理をDBMS側に任せられる
    • ネットワークの負荷軽減

関数とプロシージャの違い

  • returnの有無
  • 関数はSELECT、プロシージャはCALLで呼ぶ
  • プロシージャ内ではトランザクションのCOMMIT/ROLLBACKができる
    • 関数ではできない
postgres=# CALL nextval('hoge');
CALL nextval('hoge');
ERROR:  nextval(unknown) is not a procedure
LINE 1: CALL nextval('hoge');
             ^
HINT:  To call a function, use SELECT.

関数定義

関数の構造

テーブルスペース

  • データベースオブジェクトの物理的な格納先を設定する
    • 高性能なストレージを使う
    • 複数のテーブルスペースに分散してアクセス効率を上げる
  • 【補】公式
postgres=# SELECT name,setting,context FROM pg_settings WHERE name ~ 'tablespace';
        name        | setting | context 
--------------------+---------+---------
 default_tablespace |         | user
 temp_tablespaces   |         | user
(2 rows)
  • 一般ユーザのSET文で変更可能
  • テーブルスペース作る
  • ディレクトリがないと怒られる
postgres=# CREATE TABLESPACE tblspc1 LOCATION '/var/lib/postgresql/data/tablespace';
N '/var/lib/postgresql/data/tablespace';
WARNING:  tablespace location should not be inside the data directory
ERROR:  directory "/var/lib/postgresql/data/tablespace" does not exist
$ mkdir /var/lib/postgresql/data/tablespace

$ ls -ld /var/lib/postgresql/data/tablespace
drwxr-xr-x 2 postgres postgres 4096 Jan 23 17:06 /var/lib/postgresql/data/tablespace

$ chmod 700 /var/lib/postgresql/data/tablespace
postgres=# CREATE TABLESPACE tblspc1 LOCATION '/var/lib/postgresql/data/tablespace';
WARNING:  tablespace location should not be inside the data directory
CREATE TABLESPACE


postgres=# SELECT * FROM pg_tablespace;
SELECT * FROM pg_tablespace;
  oid  |  spcname   | spcowner | spcacl | spcoptions 
-------+------------+----------+--------+------------
  1663 | pg_default |       10 |        | 
  1664 | pg_global  |       10 |        | 
 16513 | tblspc1    |       10 |        | 
(3 rows)
  • テーブルスペースの場所確認
postgres=# SELECT pg_tablespace_location(16513);

       pg_tablespace_location        
-------------------------------------
 /var/lib/postgresql/data/tablespace
(1 row)

マテリアライズドビュー

  • 実体を持つビュー
    • 即時性はない
    • インデックス持てる
  • つくる
postgres=# CREATE TABLE tbl (number int);
CREATE TABLE

postgres=# CREATE MATERIALIZED VIEW squared AS SELECT number * number FROM tbl;
SELECT 0
  • テーブル側にデータ追加
postgres=# INSERT INTO tbl VALUES(1),(2),(3);
INSERT 0 3

postgres=# SELECT * FROM tbl;
 number 
--------
      1
      2
      3
(3 rows)
postgres=# SELECT * FROM squared;
 ?column? 
----------
(0 rows)
  • 必要に応じて更新を行う必要がある
postgres=# REFRESH MATERIALIZED VIEW squared;
REFRESH MATERIALIZED VIEW

postgres=# SELECT * FROM squared;
 ?column? 
----------
        1
        4
        9
(3 rows)

OSS-DB試験対策 緑本 ch8 SQLとオブジェクト (1/2)

www.shoeisha.co.jp


データの参照

  • SELECT

文字の扱いについて

キーワードの大文字と小文字は区別しない

文字や日付情報はシングルクォートで囲む

  • COPY文はシングルクォートが必要
    • cf. \copyメタコマンドは不要

列(カラム)、テーブル、検索条件の指定

Command:     SELECT
Description: retrieve rows from a table or view
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]

where from_item can be one of:

    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
                [ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]
    [ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
    with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias ( column_definition [, ...] )
    [ LATERAL ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
    [ LATERAL ] ROWS FROM( function_name ( [ argument [, ...] ] ) [ AS ( column_definition [, ...] ) ] [, ...] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

and grouping_element can be one of:

    ( )
    expression
    ( expression [, ...] )
    ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )
    CUBE ( { expression | ( expression [, ...] ) } [, ...] )
    GROUPING SETS ( grouping_element [, ...] )

and with_query is:

    with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( select | values | insert | update | delete )

TABLE [ ONLY ] table_name [ * ]

URL: https://www.postgresql.org/docs/12/sql-select.html

ORDER BY

  • ORDER BY 1 とかできたりする
    • select listの1つめ
    • 【所感】やめたほうがいいと思う
  • 【補】USING operator

LIMITとOFFSET

LIMIT { count | ALL }
OFFSET start
  • 公式
  • 【補】
    • countがNULLだとLIMIT ALLと同義
      • no limit
    • startがNULLだとOFFSET 0と同義

DISTINCT

  • 公式
  • 【補】DISTINCTじゃないやつ: SELECT ALL
    • ふだん使ってるSELECT
  • DISTINCT ON ( expression [, ...] )
    • 特定のカラムについて重複排除とかできる
    • 【補】式を指定できるので、特性関数を噛ませて重複除去もできる
  • ONを省略するとselect listの全カラムを用いて重複除去
  • 【補】DISTINCT ON試してみる
postgres=# SELECT * FROM alphabets;

 id | ch 
----+----
  1 | A
  2 | B
  3 | C
  4 | D
  5 | E
  6 | F
(6 rows)
  • DISTINCT ONすると行の順番が変わる
postgres=# SELECT DISTINCT * FROM alphabets;

 id | ch 
----+----
  3 | C
  1 | A
  2 | B
  4 | D
  5 | E
  6 | F
(6 rows)
  • mod(id, 3)について重複除去してみる
postgres=# SELECT DISTINCT ON (mod(id, 3)) * FROM alphabets;

 id | ch 
----+----
  3 | C
  1 | A
  2 | B
(3 rows)

GROUP BYとHAVING

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition [, ...] ]
...
  • WHERE, GROUP BY, HAVINGはこの順番に評価される

副問合せ

  • 普通のサブクエリはとくに面白くないので略
  • 【補】WITHクエリ
  • 公式
WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;
  • ネスト回避

INとNOT IN

  • 【補】例: SETで書き換え可能な設定を列挙する
postgres=# SELECT name, context FROM pg_settings WHERE context IN('user', 'superuser');

                name                 |  context  
-------------------------------------+-----------
 application_name                    | user
 array_nulls                         | user
 backend_flush_after                 | user
 backslash_quote                     | user
 bytea_output                        | user
 check_function_bodies               | user
 client_encoding                     | user
 client_min_messages                 | user
 commit_delay                        | superuser
 commit_siblings                     | user
 constraint_exclusion                | user
 cpu_index_tuple_cost                | user
 cpu_operator_cost                   | user
 cpu_tuple_cost                      | user
 cursor_tuple_fraction               | user
 DateStyle                           | user
...
  • 【補】NULLが交じるとNULLになることに留意する

ANY

  • 特徴
    • 副問合せ部分はかならず1列であること
      • 値を指定する場合は配列形式
    • 演算子を使える
  • 【補】例: 'transaction'と名のつくコンフィグと同じcontextのコンフィグを列挙する
  • 'transaction'と名のつくコンフィグ一覧
postgres=# SELECT name, context FROM pg_settings WHERE name LIKE '%transaction%';

                name                 |  context   
-------------------------------------+------------
 default_transaction_deferrable      | user
 default_transaction_isolation       | user
 default_transaction_read_only       | user
 idle_in_transaction_session_timeout | user
 log_transaction_sample_rate         | superuser
 max_locks_per_transaction           | postmaster
 max_pred_locks_per_transaction      | postmaster
 max_prepared_transactions           | postmaster
 transaction_deferrable              | user
 transaction_isolation               | user
 transaction_read_only               | user
(11 rows)
  • このいずれか(any)と同じcontextのコンフィグ
postgres=# SELECT name, context FROM pg_settings WHERE context = ANY(SELECT context FROM pg_settings WHERE name LIKE '%transaction%')

                name                 |  context   
-------------------------------------+------------
 allow_system_table_mods             | postmaster
 application_name                    | user
 archive_mode                        | postmaster
 array_nulls                         | user
 autovacuum_freeze_max_age           | postmaster
 autovacuum_max_workers              | postmaster
 autovacuum_multixact_freeze_max_age | postmaster
 backend_flush_after                 | user
 backslash_quote                     | user
 bonjour                             | postmaster
 bonjour_name                        | postmaster
 bytea_output                        | user
 check_function_bodies               | user
 client_encoding                     | user
 client_min_messages                 | user
 cluster_name                        | postmaster
...
  • 副問合せ部分が2カラム以上だとエラー
postgres=# SELECT name, context FROM pg_settings WHERE context = ANY(SELECT name, context FROM pg_settings WHERE name LIKE '%transaction%');

ERROR:  subquery has too many columns
LINE 1: ...LECT name, context FROM pg_settings WHERE context = ANY(SELE...
  • 値で指定するときは配列形式
    • ARRAY[...]
postgres=# SELECT name, context FROM pg_settings WHERE context = ANY(ARRAY['user', 'superuser', 'postmaster']);

                name                 |  context   
-------------------------------------+------------
 allow_system_table_mods             | postmaster
 application_name                    | user
 archive_mode                        | postmaster
 array_nulls                         | user
 autovacuum_freeze_max_age           | postmaster
 autovacuum_max_workers              | postmaster
 autovacuum_multixact_freeze_max_age | postmaster
 backend_flush_after                 | user
 backslash_quote                     | user
 bonjour                             | postmaster
 bonjour_name                        | postmaster
 bytea_output                        | user
 check_function_bodies               | user
 client_encoding                     | user
 client_min_messages                 | user
 cluster_name                        | postmaster
...

BETWEEN

  • BETWEEN A AND B
  • A < Bの場合、単にFALSEに評価される

結合

INNER JOIN

  • INNER省略可
  • ONで結合条件指定
postgres=# SELECT * FROM numbers JOIN alphabets ON numbers.id = alphabets.id;

 id | num | id | ch 
----+-----+----+----
  1 |   1 |  1 | A
  2 |   2 |  2 | B
  3 |   3 |  3 | C
(3 rows)
  • 【補】結合条件ON TRUECROSS JOIN相当
postgres=# SELECT * FROM numbers n1 JOIN numbers n2 ON TRUE;

 id | num | id | num 
----+-----+----+-----
  1 |   1 |  1 |   1
  1 |   1 |  2 |   2
  1 |   1 |  3 |   3
  2 |   2 |  1 |   1
  2 |   2 |  2 |   2
  2 |   2 |  3 |   3
  3 |   3 |  1 |   1
  3 |   3 |  2 |   2
  3 |   3 |  3 |   3
(9 rows)
  • USING ( join_column [, ...] ) で結合カラム名指定
    • select listを*とした場合、USINGで結合すると、結合カラムは1つにまとまる
      • cf. ONだと両テーブルのカラムが出てくる
postgres=# select * from numbers join alphabets using (id);

 id | num | ch 
----+-----+----
  1 |   1 | A
  2 |   2 | B
  3 |   3 | C
(3 rows)
  • NATURAL
    • USINGのショートハンド
    • 同名のカラム自動検出
    • 【補】なければON TRUE相当(直積になる)
postgres=# SELECT * FROM numbers NATURAL JOIN alphabets;

 id | num | ch 
----+-----+----
  1 |   1 | A
  2 |   2 | B
  3 |   3 | C
(3 rows)

CROSS JOIN

  • 直積
  • FROM複数指定でも
postgres=# SELECT * FROM numbers n1, numbers n2;

 id | num | id | num 
----+-----+----+-----
  1 |   1 |  1 |   1
  1 |   1 |  2 |   2
  1 |   1 |  3 |   3
  2 |   2 |  1 |   1
  2 |   2 |  2 |   2
  2 |   2 |  3 |   3
  3 |   3 |  1 |   1
  3 |   3 |  2 |   2
  3 |   3 |  3 |   3
(9 rows)

OUTER JOIN

  • OUTER省略可能
  • LEFT, RIGHT, FULLが使える

EXISTSとNOT EXISTS

  • 【補】必ずTRUE, FALSEのいずれかを返す
    • NULLは返さない

UNION / EXCEPT / INTERSECT

select_statement UNION [ ALL | DISTINCT ] select_statement
  • select_statementとは、下記を含まないSELECT文
    • カッコで囲まれていないORDER BY, LIMIT
      • 囲まれていれば含められる
      • 囲まれていない場合、UNION結果にLIMIT, ORDER BYが適用される
    • ロック
      • 公式
      • FOR NO KEY UPDATE
      • FOR UPDATE
      • FOR SHARE
      • FOR KEY SHARE
  • デフォルトDISTINCT
    • 重複行を除去する
    • ので、ソートが走る
  • INTERSECT, EXCEPTも同様
  • 優先順位
    • 積 > 和 = 差
    • 左 > 右

INSERT / UPDATE / DELETE

INSERT

Command:     INSERT
Description: create new rows in a table
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    [ OVERRIDING { SYSTEM | USER} VALUE ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

where conflict_target can be one of:

    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
    ON CONSTRAINT constraint_name

and conflict_action is one of:

    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
                    ( column_name [, ...] ) = ( sub-SELECT )
                  } [, ...]
              [ WHERE condition ]

URL: https://www.postgresql.org/docs/12/sql-insert.html
postgres=# SELECT * FROM alphabets;

 id | ch 
----+----
  1 | A
  2 | B
  3 | C
(3 rows)
postgres=# INSERT INTO alphabets (id, ch) VALUES (4, 'D'),(5, 'E'),(6, 'F') RETURNING *;

 id | ch 
----+----
  4 | D
  5 | E
  6 | F
(3 rows)

INSERT 0 3
postgres=# select * from alphabets;

 id | ch 
----+----
  1 | A
  2 | B
  3 | C
  4 | D
  5 | E
  6 | F
(6 rows)
  • SELECT文のresult setも流し込める
postgres=# CREATE TABLE alphabets2 (id integer, ch char(1));
CREATE TABLE

postgres=# INSERT INTO alphabets2 SELECT * FROM alphabets;
INSERT 0 6

postgres=# SELECT * FROM alphabets2;
 id | ch 
----+----
  1 | A
  2 | B
  3 | C
  4 | D
  5 | E
  6 | F
(6 rows)
  • 【補】ON CONFLICT DO UPDATEでUPSERTできる

UPDATE

Command:     UPDATE
Description: update rows of a table
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

URL: https://www.postgresql.org/docs/12/sql-update.html
  • 公式
  • SELECTのresult setも流し込める
  • 【補】UPDATE ONLY
    • ONLY指定: 指定のテーブルのみ更新
    • ONLY指定なし: 指定のテーブルを継承した派生テーブルも更新

DELETE

  • 全行消すときはTRUNCATEを使おうね
    • 公式
    • 高速
    • 【補】即座にディスク領域が回収される
      • VACUUMを手動で実行する必要がない
    • 【補】D権限を要する

データ型

数値

型名 種類 サイズ/バイト
smallint 整数 2
integer, int 整数 4
bigint 整数 8
decimal 固定小数点数 可変
numeric 固定小数点数 可変
real 浮動小数点数 4
double precision 浮動小数点数 8
  • decimal型とnumeric型とは同じもの
    • いずれもSQL標準の型
  • 【補】decimalは物理的にはゼロ埋めされずに保存される
    • この点、char(n)よりはvarchar(n)に似ている
  • decimalの小数部があふれると丸めが行われる
  • 丸めの結果、整数部の桁数があふれるとエラー
postgres=# CREATE TABLE tbl (col numeric(3,2));
CREATE TABLE

postgres=# INSERT INTO tbl (col) VALUES (1.23);
INSERT 0 1

postgres=# INSERT INTO tbl (col) VALUES (1.234);
INSERT 0 1

postgres=# INSERT INTO tbl (col) VALUES (9.99);
INSERT 0 1

postgres=# INSERT INTO tbl (col) VALUES (9.995);
ERROR:  numeric field overflow
DETAIL:  A field with precision 3, scale 2 must round to an absolute value less than 10^1.
  • 整数型も同様
postgres=# DROP TABLE tbl;
DROP TABLE

postgres=# CREATE TABLE tbl (col smallint);
CREATE TABLE

postgres=# INSERT INTO tbl (col) VALUES (32767.4);
INSERT 0 1

postgres=# INSERT INTO tbl (col) VALUES (32767.5);
ERROR:  smallint out of range

文字列

型名 長さ パディング
character varying(n), varchar(n) 最大n なし
character(n), char(n) 固定n あり
text 無制限 なし

バイナリ型

  • 公式
  • 標準SQLでは「BLOB」
  • PostgreSQLでは伝統的に「bytea」
  • 可変長バイナリ列
    • 画像、音声、各種ファイルなど

日付/時刻

型名 サイズ/B 日付 時刻 TZ 分解能
timestamp [without time zone] [(p)] 8 o o x 1 ms
timestamp with time zone [(p)] 8 o o o 1 ms
date 4 o x x 1 day
time [without time zone] [(p)] 8 x o x 1 ms
time with time zone [(p)] 12 x o o 1 ms
interval [fields] [(p)] 16 - - - 1 ms
  • TZ: +09とかそういうの
  • fields
    • '1 day'とか
    • '0.5 hour'とか
  • 【補】(p): 秒の小数点以下分解能 (0-6)

論理値

  • 数値の1,0は使用不可能
postgres=# CREATE TABLE tbool (col boolean);
CREATE TABLE


postgres=# INSERT INTO tbool (col) VALUES (1);
ERROR:  column "col" is of type boolean but expression is of type integer
LINE 1: INSERT INTO tbool (col) VALUES (1);
                                        ^
HINT:  You will need to rewrite or cast the expression.
  • 文字の'1'はOK
postgres=#  INSERT INTO tbool (col) VALUES ('1');
INSERT 0 1
  • '2'はダメ
postgres=# INSERT INTO tbool (col) VALUES ('2');
ERROR:  invalid input syntax for type boolean: "2"
LINE 1: INSERT INTO tbool (col) VALUES ('2');
true false
't' 'f'
'true' 'false'
'y' 'n'
'yes' 'no'
'on' 'off'
'1' '0'
TRUE FALSE
  • 大文字小文字の区別はない

連番

型名 サイズ/バイト
smallserial 2
serial 4
bigserial 8
  • 連番型に対応するシーケンスが自動生成される
postgres=# \d
           List of relations
 Schema |   Name    | Type  |  Owner   
--------+-----------+-------+----------
 public | alphabets | table | postgres
 public | numbers   | table | postgres
(2 rows)


postgres=# CREATE TABLE tbl(id bigserial);
CREATE TABLE


postgres=# \d
             List of relations
 Schema |    Name    |   Type   |  Owner   
--------+------------+----------+----------
 public | alphabets  | table    | postgres
 public | numbers    | table    | postgres
 public | tbl        | table    | postgres
 public | tbl_id_seq | sequence | postgres
(4 rows)
  • tblのほか、tbl_id_seqが自動生成されている

OID

  • 公式
  • PostgreSQL固有のデータ型
  • 種々のデータベースオブジェクトのID
    • データベースクラスタで一意
    • システムテーブルの主キーとして使用されている
postgres=# SELECT oid, relname FROM pg_class ORDER BY oid ASC LIMIT 3;
 oid |              relname              
-----+-----------------------------------
 112 | pg_foreign_data_wrapper_oid_index
 113 | pg_foreign_server_oid_index
 174 | pg_user_mapping_oid_index
(3 rows)
  • キャストを用いて、テーブル名やインデックス名をOIDに変換できる
postgres=# SELECT 'pg_foreign_data_wrapper_oid_index';
             ?column?              
-----------------------------------
 pg_foreign_data_wrapper_oid_index
(1 row)


postgres=# SELECT 'pg_foreign_data_wrapper_oid_index'::regclass;
             regclass              
-----------------------------------
 pg_foreign_data_wrapper_oid_index
(1 row)


postgres=# SELECT 'pg_foreign_data_wrapper_oid_index'::regclass::int;
 int4 
------
  112
(1 row)


postgres=# SELECT oid, relname FROM pg_class WHERE oid = 'pg_foreign_data_wrapper_oid_index'::regclass;
 oid |              relname              
-----+-----------------------------------
 112 | pg_foreign_data_wrapper_oid_index
(1 row)

配列

  • 1つのタプルに配列を放り込める
    • 挿入
    • 検索
    • 更新
  • テーブル定義
postgres=# CREATE TABLE array_tbl(col int[]);
CREATE TABLE


postgres=# \d array_tbl 
              Table "public.array_tbl"
 Column |   Type    | Collation | Nullable | Default 
--------+-----------+-----------+----------+---------
 col    | integer[] |           |          | 
  • 挿入
postgres=# insert into array_tbl (col) values ('{1,2,3}');
INSERT 0 1

postgres=# insert into array_tbl (col) values (Array[4,5,6]);
INSERT 0 1
  • 検索
postgres=# SELECT * FROM array_tbl;
   col   
---------
 {1,2,3}
 {4,5,6}
(2 rows)


postgres=#  SELECT * FROM array_tbl WHERE (col[1] % 2) <> 0;
   col   
---------
 {1,2,3}
(1 row)


postgres=#  SELECT * FROM array_tbl WHERE 4 = ANY(col);
   col   
---------
 {4,5,6}
(1 row)
  • 更新
postgres=# UPDATE array_tbl SET col[2] = 999;
UPDATE 2

postgres=# SELECT * FROM array_tbl;
    col    
-----------
 {1,999,3}
 {4,999,6}
(2 rows)

NULL

NULLは通常の演算子で比較などができない

  • 【補】「値がないことのマーカー」
    • unknown (Applicable, A-Mark)
    • N/A (Inapplicable, I-Mark)
  • なので、値用の演算子は使えない
    • 軒並みNULLを返す
  • IS NULL, IS NOT NULL使え

NULLを含む列のソートはNULLS FIRST / NULLS LASTを活用する

  • 公式
  • 【補】ORDER BYのデフォルトの振る舞い
    • ASC: NULLS LAST
    • DESC: NULLS FIRST
    • 要するに、NULLは非NULLよりも大きな値であるかのように並ぶ

キャスト

postgres=# SELECT CAST('123' AS int), '123'::int;
 int4 | int4 
------+------
  123 |  123
(1 row)

postgres=# SELECT 123::text || '-' || 456::text;
 ?column? 
----------
 123-456
(1 row)