~これは、雑魚技術者の勉強記録～

f:id:g071067:20200613191805p:plain

第１回は、とりあえずインストールを行った経緯でした。
「データベースクラスタ」という、最も大枠に位置するものを作成し、起動停止およびログインを行って終了しました。

　今回は、テーブルを作成してSQLを発行、データの参照や更新を試みてデータベースらしい使い方ができるところまで参りたいと思います。

データベースクラスタのなかで、さらにデータベース、スキーマ・・・と細かく住み分けを定義する概念が出てまいりますが丹念に検証していきたいと思います。

シリーズ

tecsak.hatenablog.com

<第２回の概要>

シリーズ
<第２回の概要>
2-1．データベースの作成
- ▼データベース作成
2-2．スキーマの作成
- ▼ポイント
- ▼スキーマ作成
2-3．テーブルの作成
- ▼テーブル作成
- ▼スキーマ検索パス

2-1．データベースの作成
　データベースクラスタの次の枠組みにあたるデータベースについて説明し、作成します。

2-2.　スキーマの作成
　データベースに次いで、スキーマについても作成していきます。

2-3.　テーブルの作成
　最後に、作成したデータベース・スキーマ内にテーブルを作成してみます。

2-1．データベースの作成

　データベースは、データベースクラスタを仕切るものです。データベースクラスタはデータベースの集合、データベースはスキーマ(後述)の集合で、スキーマは、テーブルやビュー、関数といったオブジェクトの集合になります。簡単ですが、概要は単純な入れ子の関係です。

　データベースは物理的に独立していて、後述の、エンコーディングやロケールといった物理的概念の住み分けに役立ちます。データベースのアクセス制御は接続レベルで管理されています。 1つの接続で複数のデータベースにアクセスすることは(物理的に離れているため)基本的にはできません。

　接続と申しましても、詳細は別途説明しなくてはなりませんが、1つのインスタンスに対して1つのportが割り当てられ、ここにクライアント認証が関わります。第1回で説明しましたが、1インスタンスが1データベースクラスタを担いますので、これに複数のデータベースが同居していることとなります。物理的に離れていると申しましたが(マニュアルにも、そう書いてあるのですが)、クライアント認証を通過したセッションが、さらにどのデータベースと関わるのかを制御しているのは、実質、権限でしかありません。

　例えば、会社などの組織が、１つのサーバー、インスタンス、データベースクラスタ内で頑張ってコンパクトに運用しようと纏めたうえで、2つの完全に独立した事業部が相乗りするなどの場合、 2つのデータベースを作成して事業部ごとに割り当てることにした、といった使い方となります。

　2つのテーブルをジョインして用いるのに、異なるデータベースに配置していては、同時に2つのテーブルを参照できませんので、仕切りが強力すぎた、ということになります。

f:id:g071067:20200613191929j:plain

　第1回で用いた図を再掲してみました。「業務データ」が複数のデータベースもしくはスキーマに匿われた、テーブル等のオブジェクト群を表します。あらためて、設定ファイル(による設定対象)、システムファイル、ログ、トランザクションログ等はデータベースクラスタ単位で制御されます。

　データベース作成にあたってのポイントをいくつか紹介させていただきます(データベース単位で考慮可能な項目という見方も)。その後、実際に作成してみたいと思います。

▼ポイント
・エンコーディング
　 PostgreSQLにてサポートされている文字セットのことです。データベース毎に設定可能です。クライアント側では、全ての文字セットを使用可能ですが、サーバー内部(サーバーエンコーディング)は限られます。　主要なコードとして、UTF-8、EUC(EUC-JPほか)が使用可能ですが、SJISはサポート外です。クライアント側でSJISに変換することは可能なものの、厳密なパフォーマンス面であったり、SJISで格納された他DBとの連携や移行の際は注意が必要です。

・ロケール
　あまり深く記載いたしません。PostgreSQLでもデフォルトを推奨しており、変更の場合はパフォーマンス影響や十分な検証が必要となりますが、並び替えや書式などの文化圏の設定となります。通常は標準ISO Cで問題なく、デフォルトの設定にもなっております。データベース毎に設定可能です。

・テンプレート
　こちらも、ざっと紹介いたします。その名のとおり、テンプレート機能でして、上述のエンコーディングやロケールのほか、テーブル等をテンプレートに含めておけば作成が楽になるという機能です。データベース作成時に利用すれば、テンプレートの丸ごとコピーで以て作成してくれる機能となります。

▼データベース作成

　作成は、SQLコマンド「CREATE DATABASE」で行います。別途、OSコマンド「createdb」での方法も用意されていまして、SQLコマンドのラッパとして、シェルのコマンドラインから呼び出し可能な形で提供されたプログラムです。

　オプションにてエンコーディングやロケールの設定を行います。

　本稿では、OSコマンドで行ってみます。また、全ての設定はデフォルトとします。・・・そうですね、名前を適当ですが、"adb"とします。

まずは、データベースクラスタの出来立て状態である今現在の、データベースの状態を確認してみます。 OSユーザー"postgres"にてpsqlよりログインし、次のように確認します。また、PostgreSQLが停止している場合は、起動してください。

    -bash-4.2$ psql
    psql (9.6.3)
    "help" でヘルプを表示します.
    
    postgres=# \l
                                            データベース一覧
       名前    |  所有者  | エンコーディング | 照合順序 | Ctype(変換演算子) |      アクセス権
    -----------+----------+------------------+----------+-------------------+-----------------------
     postgres  | postgres | UTF8             | C        | C                 |
     template0 | postgres | UTF8             | C        | C                 | =c/postgres          +
               |          |                  |          |                   | postgres=CTc/postgres
     template1 | postgres | UTF8             | C        | C                 | =c/postgres          +
               |          |                  |          |                   | postgres=CTc/postgres 
    (3 行)
    
    postgres=#
    postgres=# \q
    -bash-4.2$

　第1回でも使用しましたが、￥マークが出てきました。 \はじまりのこれらは、メタコマンドと呼ばれます。その名の通り、SQLに対し、メタとしての位置づけとなり、長ったるいSQL文を書かなくても出来合いが動いてくれます。データベースの情報表示は、小文字のエルでした。

　・・・出来合いとは、具体的にどのようなものなの？　という質問をいただきまして、はい、長ったるいSQLを書いてみました。同じ結果が出せるか、チャレンジ！！

    postgres=# select
    postgres-#   pd.datname "名前",
    postgres-#   pu.usename "所有者",
    postgres-#   pg_encoding_to_char(pd.encoding) "エンコーディング",
    postgres-#   pd.datcollate  "照合順序",
    postgres-#   pd.datctype "CType(変換演算子)",
    postgres-#   pd.datacl "アクセス権"
    postgres-# from
    postgres-#   pg_database pd
    postgres-# left outer join pg_user pu
    postgres-# on pd.datdba = pu.usesysid
    postgres-# ;

       名前    |  所有者  | エンコーディング | 照合順序 | CType(変換演算子) |             アクセス権
    -----------+----------+------------------+----------+-------------------+-------------------------------------
     postgres  | postgres | UTF8             | C        | C                 |
     template1 | postgres | UTF8             | C        | C                 | {=c/postgres,postgres=CTc/postgres}
     template0 | postgres | UTF8             | C        | C                 | {=c/postgres,postgres=CTc/postgres}
    (3行)

    postgres=#

おそらく、裏でこんなSQLが動いているんだろうなぁ、という感じです。自前のSQLをテキストファイルなどに持っていて、使うときにコピペでも全然OKですが、まあ、メタも便利では？

　折角ですので、紹介しますと、「pg_database」というテーブルを参照しておりますが、これをシステムカタログと言い、そのままですが、システムの情報が格納されています。

　可読性を高めるため、他のテーブルも参照したSQLとなりましたが、メタとシステムカタログがおよそ1:1で存在しています。参考までに、似たような一例を挙げておきます。

情報名	メタ	システムカタログ
データベース	\l	pg_database
スキーマ	\dn	pg_namespace
テーブル	\dt	pg_tables

　3つのデータベースが存在しています。うち、2つはテンプレートです。名称もデフォルトで「template0」「template1」と決まっています。

　加えて、データベースクラスタ作成時にユーザー名と同名でデータベースが作成されます。「postgresql」が該当します。

　エンコーディングについて、UTF8となっています。これはデータベースクラスタ作成時(initdb)に-EオプションにてUTF8を指定した結果となります。前述のとおり、データベース単位で指定可能ですので、データベースの作り様によりますが、デフォルト自体の設定がデータベースクラスタ作成時に決まるということです。

　照合順序(コリレーション)およびCtype(変換演算子)について、Cとなっています。これはデータベースクラスタ作成時(initdb)に--no-localeオプションにてデフォルト採用した結果となります。照合順序がデータベースのロケールを、CtypeがOSロケールを示すそうですが、前述のとおり、Cで問題ありません。また、DB・OSにて相違させることもほぼ無いとされています(マニュアルより)。アクセス権(権限)については、次回以降で取り扱いたいと思います。

　それでは、作成してみましょうか。引き続き、postgresqlユーザーでまいります。

    -bash-4.2$ id
    uid=26(postgres) gid=26(postgres) groups=26(postgres) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
    -bash-4.2$
    -bash-4.2$ createdb adb
    -bash-4.2$
    -bash-4.2$ psql
    psql (9.6.3)
    "help" でヘルプを表示します.

    postgres=# \l
                                            データベース一覧
       名前    |  所有者  | エンコーディング | 照合順序 | Ctype(変換演算子) |      アクセス権
    -----------+----------+------------------+----------+-------------------+-----------------------
     adb       | postgres | UTF8             | C        | C                 |
     postgres  | postgres | UTF8             | C        | C                 |
     template0 | postgres | UTF8             | C        | C                 | =c/postgres          +
               |          |                  |          |                   | postgres=CTc/postgres
     template1 | postgres | UTF8             | C        | C                 | =c/postgres          +
               |          |                  |          |                   | postgres=CTc/postgres
    (4 行)

    postgres=#

はい、１つ増えました。全てデフォルトで作成したため、"adb"データベースと"postgresql"データベースは全く同じ感じで作成されていますね。 f:id:g071067:20200613191659j:plain

2-2．スキーマの作成

　スキーマは、オブジェクト(テーブルやビュー、関数など)の集合で、名前空間となります。前述のとおり、データベースはスキーマの集合ということで、スキーマは必ず1つのデータベースに属します。　と、理屈ばかり申しても、スキーマの用途が思い浮かびにくいと思います。PostgreSQLのスキーマが持つ、次のようなポイントの紹介を通じて、一緒に考えてみましょう。

▼ポイント

・名前空間
　理屈をご存知の方も多いかもしれません。同じ空間内でしたら、オブジェクトの名前が被ってはダメということです。面倒くさいような、しかしある意味では使い手である我々も、重複混同から守られている感があります。スキーマはこの重複ルールを課す範囲、単位を担います。 f:id:g071067:20200613191715j:plain

図2-2として、A・B・Cユーザーで共有出来、名前が被らない世界Xを作りたく、Xスキーマを作成しましたよ、を絵にしてみました。あくまで、どのディスクに配置されているかといった物理配置とは別の概念です。

　ユーザー作成とスキーマ作成を別個に行う感覚が無い方もいらっしゃることでしょう。 DB製品によっては、ユーザー名と同じ名称で、ユーザーの作成/削除のタイミングでスキーマが作成/削除されるものもあり、スキーマが、いかにもそのユーザーの所有スペースであるかのように使われていることもあります。厳密な名前空間としてのスキーマの定義とは理解が異なるものの、これが害と申すわけではなく、その製品の運用にあたっては障害やトラブルにつながるどころか、スムーズな理解の形と言えることでしょう。ただ、PostgreSQLにおいては本来の名前空間の認識が求められたり、そのような使い方ができるということです。

f:id:g071067:20200613191726j:plain

・publicスキーマ
　データベース作成直後の状態では、データベースには public というスキーマだけが存在する形となります。また、publicスキーマには、どのユーザでもオブジェクトを作成することができます。そして、全てのオブジェクトはデフォルトでpublicスキーマに作成されます。

　ここで、あらためてスキーマの在り方を考えたいと思います。逆をいいますと、イヤでもpublicという名の、全ユーザーがアクセス可能なスキーマのみが存在するところからスタートするということです。便利/解放感があるものの、セキュアな観点で不要なものは使用しないし、作成・存在すらすべきでない境地がここに適用されるべきではないかと思います。

　例えば"work"などといった中立な場所を作成した途端、ごちゃごちゃと資源が配置されてゆく光景を経験された方は多いのではないでしょうか。 publicにオブジェクトが作成されれば、業務用なのか、エビデンスなのかバックアップで以てゴミなのか、、とDBAを悩ませる種になること間違いありません。　また、他DBとの移植性を考えますとPostgreSQLならではのpublicスキーマは移植性の低下につながります。

　何だか、悪い面ばかりが浮かんでしまいましたが、適切な管理のもとであればpublicスキーマを用いた運用でも問題ありません。ユーザー専用のスキーマを設ける場合、ユーザーの増加にあわせ、スキーマも増やしていかなければなりませんので、スキーマが1つで十分なのであれば、publicスキーマ1つのほうがシンプルに決まるということもあるでしょう。

▼スキーマ作成

　作成した「adb」データベースにスキーマを作成したいと思います。・・・そうですね、名前を適当ですが、"XWORK"とします。

f:id:g071067:20200613191736j:plain

現状を確認してみます。

    -bash-4.2$ psql
    psql (9.6.3)
    "help" でヘルプを表示します.

    postgres=# \dn
       スキーマ一覧
      名前  |  所有者
    --------+----------
     public | postgres
    (1 行)

    postgres=#
    postgres=# --ちょっと待ったぁ！！
    postgres=#

　スキーマ一覧のメタは「dn」です。と、ちょっと待ったです。データベースは接続単位であったはずです。現在、どのデータベースに接続したのでしょうか？

　作成した「adb」データベースにスキーマを作成するということでしたので、adbなら良いのですが・・・確認しましょう。

    postgres=# select current_user,current_database();
     current_user | current_database
    --------------+------------------
     postgres     | postgres
    (1 行)

    postgres=#

　少し変なSQLが出ました。明らかに規格外です。

これらは、「セッション情報関数」の一部です。まあ、便利機能です。他にも、第1回でバージョンを表示「version()」しましたが、メタとあわせて適宜有効活用したいですね。今回用いた関数は、現在実行を行っているユーザー「current_user」と現在のデータベース名「current_database()」を問合せするものです。

後ろに括弧「()」が必要なものと不要なものがあり、ややこしいです(OSS-DB試験対策としては、暗記する必要があります)。

　話を戻しまして、今度こそabdに接続です！ここで、目的のデータベースへの接続方法として「既に接続の状態から接続先を変更する方法」と、「ログイン時に指定接続する方法」について触れておきましょう。

　まずは前者で、現状の"postgresql"データベースから"adb"データベースへ接続を変更してみます

    postgres=# \c adb
    データベース "adb" にユーザ"postgres"として接続しました。
    adb=#
    adb=# select current_user,current_database();
     current_user | current_database
    --------------+------------------
     postgres     | adb
    (1 行)

    adb=#

\c(\connect)を使うということです。続いて、後者です。一旦、psqlを終了し、その後"adb"データベースへ直接、接続します。

    adb=# \q
    -bash-4.2$ psql -d adb
    psql (9.6.3)
    "help" でヘルプを表示します.

    adb=# select current_user,current_database();
     current_user | current_database
    --------------+------------------
     postgres     | adb
    (1 行)

    adb=#

-dオプションで指定するということです。さあ、"adb"データベースに属するスキーマの確認です。

    adb=# \dn
       スキーマ一覧
      名前  |  所有者
    --------+----------
     public | postgres
    (1 行)

    adb=#

データベースを作成した直後は、publicスキーマのみ存在、であるという前述の内容があらためて確認された結果となります。

　準備OK。スキーマを作成しましょう。作成は、SQLコマンド「CREATE SCHEMA」で行います。名称は、XWORKでしたね。

    adb=# create schema XWORK;
    CREATE SCHEMA
    adb=# \dn
       スキーマ一覧
      名前  |  所有者
    --------+----------
     public | postgres
     xwork  | postgres
    (2 行)

    adb=#

作成されました。

　お気づきかもしれませんが、念のため。PostgreSQLは名称が基本的に小文字に統一されます。ただし、二重引用符を用いますと、大文字にできます。

　以下に、ちょっと実験してみます(脱線ばっかりですね・・)。

    adb=# create schema "TESTwork";
    CREATE SCHEMA
    adb=# \dn
        スキーマ一覧
       名前   |  所有者
    ----------+----------
     TESTwork | postgres
     public   | postgres
     xwork    | postgres
    (3 行)

    adb=# drop schema TESTwork;
    ERROR:  schema "testwork" does not exist
    adb=# drop schema "TESTwork";
    DROP SCHEMA
    adb=#
    adb=# \dn
       スキーマ一覧
      名前  |  所有者
    --------+----------
     public | postgres
     xwork  | postgres
    (2 行)

    adb=#

　うーん、めんどくさ。・・ではありますが、障害の火種にもなりますので、PostgreSQLに限った話ではなく、ネーミングルールの制定等を以て安全対策を。　二重引用符を用いてテスト作成した後、これを消そうとしましたが、あえて二重引用符なしで一度試み、やはり怒られています。

2-3．テーブルの作成

　さくっと作成いたします。テーブル作成のみに関しては、もう罠はありません。標準SQLに従って普通のCREATE TABLE文を発行し、単純なテーブルを設けたいと思います。別途、テーブルの話になりますと、独特の型があったり、商用DBとの互換性や比較が気になるところですが、別な機会での紹介とさせてください。

▼テーブル作成

・現状確認

    adb=# select current_user,current_database();
     current_user | current_database
    --------------+------------------
     postgres     | adb
    (1 行)

    adb=# \dt xwork.*
    マッチするリレーションが見つかりません
    adb=#

「dt」メタを用いて、テーブル一覧を表示しています。引数に、「xwork.*」とし、xwork スキーマに限っての一覧表示としております。現状、空っぽです。

・作成
　普通に。

    adb=# create table xwork.testtbl(id integer,namae varchar(20));
    CREATE TABLE
    adb=#

・事後確認

    adb=# \dt xwork.*
                リレーションの一覧
     スキーマ |  名前   |    型    |  所有者
    ----------+---------+----------+----------
     xwork    | testtbl | テーブル | postgres
    (1 行)

    adb=#

おっ、出来た出来た。

▼スキーマ検索パス

　前述のとおり、デフォルトの状態では、新しいデータベースではpublicスキーマしか存在せず、オブジェクト操作にあたっての対象スキーマとしましても、publicスキーマがデフォルトにて対象とされます。これらはデフォルトの"設定"によるもので「スキーマ検索パス」と呼ばれる設定となります。

　はじめに、この設定について申してしまいますと、スキーマ指定なきSQLに対して、取扱対象とするスキーマを自動指定する機能です。デフォルトでは、「ユーザー名と同名」→「public」の優先度で設定されています。ここで、新しいデータベースではpublicスキーマしか存在しないため、publicスキーマにて取り扱いされるカラクリです。　　デフォルト・・・暗黙や当たり前は人間の思い込みにつながり、事故につながることもあります。 DBの種類に関わらず、スキーマの取り扱いにおいてもスキーマ名の記載を義務付けしている運用も多いのではないでしょうか。

　さてここで、DBAとしてはスキーマ指定を怠ってしまったときの対応や安全性について、確保する必要があるでしょう。前述のとおり、怠った場合は(デフォルトの状態では、新しいデータベースでは)publicスキーマに格納されます。そして、適切な運用を以てすれば、publicスキーマを用いた運用も悪ではないとしました。当機能を紹介しておいて、いきなり難ですが、スキーマ指定を行うに越したことはありません。

　それでは逆に、運用形態の如何に関わらず、安全なスキーマ運用のスタイルとはどのような形になるのでしょうか？これを考慮いたしますと、1つとしては、共用を極限まで廃止することです。つまり、1ユーザー当たり専用のスキーマを1つ作成するスタイルが多く採用されているようです。

PostgreSQLでは、ユーザー名と同名のスキーマを作成可能です。1:1にするのであれば、折角ですからユーザー名とスキーマ名を同名にして、1セットで作成してはどうでしょうか。結局、他DBと同様の構成を手動で作ることになるというオチです。

加えて、publicスキーマを使用不可もしくは削除することをお忘れなく(やり方は次回以降の説明とさせてください)。

　ここまできて、話をスキーマ検索パスの"設定"に戻しますと、1:1のスタイルでは、デフォルトをユーザー名と同じ名称のスキーマとしたいところです。そして、前述のとおり、その設定自体がデフォルトとして、既に反映できているということでした。

　スキーマ検索パスの設定は、postgresql.conf($PGDATA/postgresql.conf)内のsearch_pathという設定で可能です。一応、確認してみます。

    -bash-4.2$ grep search_path postgresql.conf
    #search_path = '"$user", public'        # schema names
    -bash-4.2$

grepしてみました。コメントアウトされておりますが、設定なき場合、デフォルトということで前述のとおりです。かつ、右辺の記述がデフォルトと同様の記述内容となっておりまして、せっかく見ましたので、設定の書き方について説明してしまいます。

右辺の記述は、2か所が設定されている形となります。左に記載されているほど優先度が高いです。見た目で伝わるかと思いますが、"$user"という記述が、そのセッションでのユーザー名を示します。したがって、ユーザー名と同様のスキーマを第1優先として取り扱われます。

ここで、ユーザー名と同名のスキーマが無かったり、権限が不足するなどして条件を満たさない場合に、次の優先度を持つスキーマが対象となります。カンマを隔てて、次に記述されているのが"public"となっています。

スキーマ指定せずにテーブルを作成してみます。

    adb=# create table testtbl(id integer,namae varchar(20));
    CREATE TABLE
    adb=#

先ほどと全く同じSQLで、スキーマ指定が無いだけです。正常終了しています。我々としては、意図した結果なのか、確認します。

・確認

    adb=# \dt public.*
                リレーションの一覧
     スキーマ |  名前   |    型    |  所有者
    ----------+---------+----------+----------
     public   | testtbl | テーブル | postgres
    (1 行)

    adb=# \dt xwork.*
                リレーションの一覧
     スキーマ |  名前   |    型    |  所有者
    ----------+---------+----------+----------
     xwork    | testtbl | テーブル | postgres
    (1 行)

    adb=#

　予想通り、デフォルトに格納されるということで、第1優先としてはユーザー名と同名のスキーマですが、 "postgres"という名称のスキーマが無いため、第2優先であるpublicスキーマに格納されています。後者のxwork スキーマに対しては、先ほど作ったものですが、あらためて、同じDB内で異なるスキーマでは、同名称のオブジェクトが居合わせることができている、という絵になっています。

・スキーマ検索パス設定
　それでは、スキーマ検索パス(search_path：サ～チパス)とやらを、試してみましょう。と、その前に現状設定の確認とユーザー名と同名のスキーマ作成もしておきます。

    postgres=# show search_path;
       search_path
    -----------------
     "$user", public
    (1 行)

    postgres=# select current_schema();
     current_schema
    ----------------
     public
    (1 行)

    postgres=#

　はじめにshow文で設定を確認しています。次いで、「current_schema()」関数で現在のセッションにおけるスキーマのカレントを表示しています。ユーザー名と同名のスキーマが存在しないため、第2優先であるpublicが示された結果です。

　ここで、同名のスキーマを作成します。

    adb=# \dn
       スキーマ一覧
      名前  |  所有者
    --------+----------
     public | postgres
     xwork  | postgres
    (2 行)

    adb=#
    adb=# create schema postgres;
    CREATE SCHEMA
    adb=#
    adb=# \dn
        スキーマ一覧
       名前   |  所有者
    ----------+----------
     postgres | postgres
     public   | postgres
     xwork    | postgres
    (3 行)

    adb=#

反映確認

    adb=# select current_schema();
     current_schema
    ----------------
     postgres
    (1 行)

    adb=#

　第1優先である、ユーザー名と同名のスキーマが存在につき、カレントの認識が"postgres"スキーマへと変更になっています。　ここで、あらためてスキーマ指定なしでcreateを発行してみたいところです。一応、publicに作成したテーブルを消してからチャレンジしたいと思います。

    adb=# drop table testtbl;
    DROP TABLE
    adb=# \dt postgres.*
    マッチするリレーションが見つかりません
    adb=# \dt public.*
    マッチするリレーションが見つかりません
    adb=# \dt xwork.*
                リレーションの一覧
     スキーマ |  名前   |    型    |  所有者
    ----------+---------+----------+----------
     xwork    | testtbl | テーブル | postgres
    (1 行)

    adb=#

　何気に、いきなりスキーマ指定なしでdropを仕掛けています。作成したばかりのスキーマ(postgresスキーマ)には、当然ながらテーブルは存在しません。この状態で、dropを発行したところ、正常終了。テーブルの存在確認を行い、第2優先であるpublicスキーマから消えています(後ほど、もう一回見ます)。

作成します。

    adb=# create table testtbl(id integer,namae varchar(20));
    CREATE TABLE
    adb=# \dt postgres.*
                リレーションの一覧
     スキーマ |  名前   |    型    |  所有者
    ----------+---------+----------+----------
     postgres | testtbl | テーブル | postgres
    (1 行)

    adb=# \dt public.*
    マッチするリレーションが見つかりません
    adb=# \dt xwork.*
                リレーションの一覧
     スキーマ |  名前   |    型    |  所有者
    ----------+---------+----------+----------
     xwork    | testtbl | テーブル | postgres
    (1 行)

    adb=#

"postgresql"スキーマに作成され、何より。

さらに作成！？　してみます。

    adb=# create table testtbl(id integer,namae varchar(20));
    ERROR:  relation "testtbl" already exists
    adb=#
    adb=# create table public.testtbl(id integer,namae varchar(20));
    CREATE TABLE
    adb=#
    adb=# \dt postgres.*
                リレーションの一覧
     スキーマ |  名前   |    型    |  所有者
    ----------+---------+----------+----------
     postgres | testtbl | テーブル | postgres
    (1 行)

    adb-# \dt public.*
                リレーションの一覧
     スキーマ |  名前   |    型    |  所有者
    ----------+---------+----------+----------
     public   | testtbl | テーブル | postgres
    (1 行)

    adb-# \dt xwork.*
                リレーションの一覧
     スキーマ |  名前   |    型    |  所有者
    ----------+---------+----------+----------
     xwork    | testtbl | テーブル | postgres
    (1 行)

    adb-#

さらに作成を試みたら、怒られてしまいました。既に存在する、ということで。

当然といいますか、public(第2)に作成を試みるのかな？　と思われたためです。その後、スキーマ指定にて成功しております。

削除します。

    adb=# drop table testtbl;
    DROP TABLE
    adb=# \dt postgres.*
    マッチするリレーションが見つかりません
    adb-# \dt public.*
                リレーションの一覧
     スキーマ |  名前   |    型    |  所有者
    ----------+---------+----------+----------
     public   | testtbl | テーブル | postgres
    (1 行)

    adb-# \dt xwork.*
                リレーションの一覧
     スキーマ |  名前   |    型    |  所有者
    ----------+---------+----------+----------
     xwork    | testtbl | テーブル | postgres
    (1 行)

    adb-#

第1である、"postgres"スキーマより、テーブルの存在が消えています。想定どおりです。

ここで、さらに同文による削除を行ってみます。

さらに削除

    adb=# drop table testtbl;
    DROP TABLE
    adb=# \dt postgres.*
    マッチするリレーションが見つかりません
    adb=# \dt public.*
    マッチするリレーションが見つかりません
    adb=# \dt xwork.*
                リレーションの一覧
     スキーマ |  名前   |    型    |  所有者
    ----------+---------+----------+----------
     xwork    | testtbl | テーブル | postgres
    (1 行)

    adb=#
    adb=# drop table testtbl;
    ERROR:  table "testtbl" does not exist
    adb=#
    adb=# \dt xwork.*
                リレーションの一覧
     スキーマ |  名前   |    型    |  所有者
    ----------+---------+----------+----------
     xwork    | testtbl | テーブル | postgres
    (1 行)

    adb=#

今度は、"public"スキーマより消えています。

　考えられることは、第1に削除対象が存在しなかったため、第2に対象を移した、ということです。 createとの動きの相違も感じつつ、何より危険ではないでしょうか。

　仕様として説明がつくものなのか調査を行いましたが、すみません、見当たりませんでした。 (PostgreSqlの世界では、こういうこと、よくある気がします。。)

　上記結果より、publicスキーマを用いた運用について、publicスキーマ＋他スキーマで行うことは、何らかのリスクがあるように思えます。

第２回はここで終了したいと思います。

冒頭で、データの挿入、参照を行うと宣言したのに、オブジェクトの作成削除で終了となってしまいました。ですが、ここまで来てしまえばあとは簡単でしょう。同名のテーブルで、異なるデータを各スキーマに忍ばせるなどして遊んでみてください。

　データベースとスキーマを、データベースクラスタを分割するもの、としての観点で紹介する回となりましたが、途中で幾度か説明を割愛した、権限との関係についても考慮していく必要があります。権限の話題となりますと、ユーザーについても知る必要があります。

　次回は、ユーザー・使い手の住み分けとしての観点で、再度、データベースとスキーマを捉えるとともに、ユーザー、権限の紹介をさせていただきたいと思います。

加えて、publicスキーマを使用不可もしくは削除することをお忘れなく。危なっかしいpublicスキーマの封印についても触れたいと思います。

　おつかれさまでした。ありがとうございました。

tecsak’s diary

【PostgreSQL】第2回　データベース・スキーマを作成してテーブルをつくろう