VScodeのPostgreSQL拡張が便利

 

　私がお仕事で作っているシステムが扱っているデータベースは、現状すべてがPostgreSQLだったりします。システム開発しているとプログラムだけじゃなくてSQLも色々書いたりしますし、日によっては一日中SQLとにらめっこしているなんてことも。
　プログラムの編集はVScodeで快適にやってますが、ちょっとしたSQLを実行したり、PL/pgSQLの関数書いたりするときに、編集だけならVScodeで可能ですが実行時にはいちいちツール切り替えるのが面倒だなぁ、って思っていました。（そんなに頻繁には切り替えませんが）

　で、そこはやはりVScode。探せばええ感じの拡張機能あるやろと思って調べたら、個人的に気に入ったものがありました。今回はそれを紹介します。

 

◆拡張機能「PostgreSQL」

　https://marketplace.visualstudio.com/items?itemName=ckolkman.vscode-postgres

　名前がまんまですが、これ非公式です。（その名前でええんか……と思いつつも）
　開発者はChris Kolkman氏。

　こいつをさくっとインストールすると、サイドメニューのとこにPostgreSQLの象のアイコンが出てきます。そいつを選んで上にある「＋」のアイコンをクリックすると、DB接続を設定できます。

 

　設定は、コマンドパレットの入力のように画面上部に入力欄が出てきます（下みたいな感じ）ので、そこに入力していく形式です。

　この記事を書いている時点では、それぞれ順番に

　・接続先ホスト名
　・接続ユーザー名
　・パスワード
　・ポート番号
　・SSL接続するかどうか（選択）
　・表示するDB（全てか、選択したものだけか）
　・この接続の名前

　となっているので、入力していきましょう。

 

　全部入力が終わると、左側のパネル内に作った定義が表示されます。これを展開していけば、そのDBのテーブル、ビュー、ファンクションなどの各オブジェクトが表示されます。テーブルをさらに展開すればカラムの一覧も出てきます。定義の確認などに使えますね。

 

　ここからテーブルの中身も見たい場合は、テーブルを右クリックして「Select Top 1000」を選べば先頭1000行が表示されます。ただしこれは参照だけであり、中身を書き換えたりすることは出来ません。
　また、Create/Delete Tableとかのデータ定義は、GUI上からは出来ません。あくまでもこのGUI部分はクエリ作成の補助機能である、と拡張機能の説明ページにも記載がありますのでご注意を。まあクエリを自分で書けば実行できますけど。

 

 

 

◆クエリを実行してみる

　定義が済んだら、VScodeのステータスバー上に出ている「Select Postgres Server」をクリックしましょう。そして先ほど定義しておいた接続を選択し、続けてDBを選択します。選択したものがステータスバー上に表示されたら準備完了！

　以上でVScode上から簡単にクエリが実行できるようになりました。これで、例えばVScodeでつれづれなるままにファイル編集などしている最中に、ふと急にクエリを実行したくてしかない症候群になったときでも大丈夫。

 

 

 

　このように、プログラムだろうがただのテキストファイルだろうが関係なく、エディタ上にいきなりSQL文をガガガっと書き込みます。書き込んだらその部分を選択して右クリックし、メニューから「Run Query」を選択すれば・・・。

 

 

 

 

　この通り。

　実行できるクエリはSELECTに限らず、INSERT/UPDATE/DELETEはおろか、TRUNCATE/DROPなども。ようはクエリで書けるなら何でも。保存してあるPL/pgSQLのファイルを開いた場合も、全選択してRun Queryしてやれば実行できます。これでDB関数更新するためにいちいちpsql叩いたりpgAdmin起動したりしなくて済むぞ！やった！！

 

 

　なんでもかんでもこれを使えばいいというわけではありませんが、この使い方で十分という人も結構いるのではないでしょうか。もし興味のある方がいらっしゃれば是非、お試しあれ。

PostgreSQL:テーブル・カラム情報をクエリで取得する

　テーブル一覧を取得したりカラム一覧を取得して、あれこれしたい、というときは結構あると思います。PostgreSQLでも当然そういう要望はあってぐぐれば出てくるんですが、いちいちクエリ書くの面倒くさいのでViewとして定義して使っています。折角なので自分が使ってるやつをここにペタペタしときますので、ご参考までにどうぞ。

--DROP VIEW public.view_columnlist;
CREATE OR REPLACE VIEW public.view_columnlist AS
    SELECT      cls.oid          AS object_id
               ,nms.nspname      AS schema_name
               ,cls.relname      AS table_name
               ,des1.description AS table_logical_name
               ,att.attnum       AS column_no
               ,att.attname      AS column_name
               ,des2.description AS column_logical_name
               ,typ.typname      AS column_type
               ,COALESCE(idx.indisprimary, FALSE) AS column_is_primary_key
               ,att.attnotnull   AS column_is_not_null
               ,cls.relkind      AS object_type
    FROM        pg_catalog.pg_class       cls
    LEFT  JOIN  pg_catalog.pg_namespace   nms
            ON  cls.relnamespace = nms.oid
    LEFT  JOIN  pg_catalog.pg_attribute   att
            ON  cls.oid = att.attrelid
           AND  att.attnum > 0
    LEFT  JOIN  pg_catalog.pg_type typ
            ON  att.atttypid = typ.oid
    LEFT  JOIN  pg_catalog.pg_description des1
            ON  cls.oid = des1.objoid
           AND  des1.objsubid = 0
    LEFT  JOIN  pg_catalog.pg_description des2
            ON  cls.oid = des2.objoid
           AND  att.attnum = des2.objsubid
    LEFT  JOIN  (SELECT indrelid,UNNEST(indkey) AS pk_column_no,indisprimary FROM pg_catalog.pg_index WHERE indisprimary) idx
            ON  cls.oid = idx.indrelid
           AND  att.attnum = idx.pk_column_no
    WHERE       cls.relkind IN ('r','v','m')
      AND       nms.nspname = 'public'
    ORDER BY    schema_name
               ,table_name
               ,column_no
    ;

ALTER TABLE public.view_columnlist
    OWNER TO ユーザー名;


COMMENT ON VIEW view_columnlist IS 'VIEW_テーブルカラム一覧取得';
COMMENT ON COLUMN view_columnlist.object_id IS 'OID';
COMMENT ON COLUMN view_columnlist.schema_name IS 'スキーマ名';
COMMENT ON COLUMN view_columnlist.table_name IS 'テーブル名';
COMMENT ON COLUMN view_columnlist.table_logical_name IS 'テーブル論理名';
COMMENT ON COLUMN view_columnlist.column_no IS 'カラム番号';
COMMENT ON COLUMN view_columnlist.column_name IS 'カラム名';
COMMENT ON COLUMN view_columnlist.column_logical_name IS 'カラム論理名';
COMMENT ON COLUMN view_columnlist.column_type IS 'カラム型';
COMMENT ON COLUMN view_columnlist.object_type IS 'オブジェクト型';
COMMENT ON COLUMN view_columnlist.column_is_primary_key IS '主キー制約';
COMMENT ON COLUMN view_columnlist.column_is_not_null IS '非NULL制約';

　ながい！
　けどそんなに複雑なことはしてないので、簡単に解説しておきます。

◆ざっくり解説

　全てPostgreSQLのシステムカタログテーブルです。まあ当然ですよね。詳細は公式ドキュメントを参照していただくとして、何のテーブルで何のために結合しているかだけ羅列しておきます。尚、各システムカタログテーブルは内部的にoid列をもっており、これが各テーブル間を結合するためのキーとなっています。（ただしoid同士を直接結び付けるわけではない）

テーブル名	説明
pg_class	テーブルやビューなどの一覧が定義されている。クエリのメインのテーブル。
pg_namespace	各オブジェクトの名前空間を管理する。 取得対象を絞り込むために使用。（システム系のものを排除） pg_class.relnamespace と同じ値がこのテーブルのoidになっている。
pg_attribute	テーブルやビューの列情報を管理する。 列番号や名前を取得するために使用。 このテーブルのattrelidがpg_class.oidと同じ値になっている。 列番号が1以上の行が列情報。
pg_type	型情報が定義されている。 各カラムの型情報を取得するために使用。 pg_attribute.atttypidと同じ値がこのテーブルのoidになっている。
pg_description	各オブジェクトのコメントを管理する。 テーブルやカラムのコメント情報を取得するために使用。 pg_class.oidと同じ値がこのテーブルのobjoidに格納される。 テーブル情報（objsubid = 0）とカラム情報の両方を取得。
pg_index	インデックス情報の一部を管理する。 主キー情報を取得するために使用。（サブクエリでindisprimary(bool)を絞り込んで使用） indrelidが対象となるテーブルのOID(pg_class.oid)、indkeyが列番号(pg_attribute.attnum)と同じ値になる。 ※ただしindkeyは配列型なので扱いに注意

　各テーブルの結合部分がTry&Errorで作ってったので面倒くさかったです。とはいえ、ドキュメントと実テーブルの内容さえわかってしまえば、あとは時間の問題だけでした。

　残りについては各カラムにつけた別名を見て頂ければ意味はわかってもらえると思います。また、WHERE句にある「cls.relkind IN (‘r’,’v’,’m’)」についてですが、これはオブジェクトの種類を絞り込んでおり、具体的には「r:テーブル」「v:ビュー」「m:マテリアライズドビュー」となります。詳細は公式ドキュm（略）

◆実際に使ってみる

　まずはテキトーに、テーブルを用意します。こんな具合に。

　そして、先ほど作成したビューを開いてみます。

　ちゃんと取得できてますね。なんか表示されてないテーブルがある気がするかもしれませんが、目の錯覚です。

　つたないクエリですが、よければご参考までに。（結構前に作ったやつなので、環境によってはうまく動かないかもしれませんが）

◆補足

　テーブルやカラムのコメント欄を論理名としているのは、普段はフリーツールの「A5:SQL Mk-2」を使用している関係からです。このツールではコメントを論理名として扱ってくれます。
　尚、論理名を指定しつつコメントも設定したい場合は、コメント部分をタブ区切りにすると認識してくれます。（その場合は上のクエリもタブで区切るように手直ししてください。具体的には↓みたいに）

--               ,des2.description AS column_logical_name
,COALESCE(CASE POSITION(CHR(9) IN des2.description) 
		 WHEN 0 THEN des2.description
		 ELSE        SUBSTRING(des2.description FROM 0 FOR POSITION(CHR(9) IN des2.description))
		 END
		,att.attname) 
				 AS column_logical_name
,CASE POSITION(CHR(9) IN des2.description) 
	WHEN 0 THEN NULL
	ELSE        SUBSTRING(des2.description FROM POSITION(CHR(9) IN des2.description))
END              AS column_comment

　んでもってこんなクエリを発行しておきます。

COMMENT ON COLUMN my_favorite_koto_hono.ss_evaluation IS 'SS評価    やっぱりことほのなんだよちゅんなぁ(･8･)';

※わかりづらいですが、間のはスペースではなくタブです

　そうするとこうなります。

　ばっちりね！

PostgreSQL: すべてのfunctionを一括で削除したい

 

　前に書いた「PostgreSQL：複数定義があるFunctionの一覧をクエリで取得する」って記事の続き的なもの。すっかり書くの忘れてましたが。

　さて、上にあげた記事のように、うちのPJではめちゃくちゃFunctionが多くて色々と問題が起きておりまして。そのうえ、環境ごとにDBが乱立してしまい、あるDBのFunctionが最新版では無かったりして環境ごとに挙動が異なり、混乱を招いたりするのは日常茶飯事でした。
　もう完全に管理できてへんやんけ！という本音は横に置いておき、ひとまず事態を収めねばなりません。幸い、FunctionそのものはテキストファイルにしてSVN上で保存してあったので、最新版を当て直すだけならば、さほど難しくありません。

　しかし、どうせ当て直しするなら消し忘れの古い定義のFunctionとか、不要になったFunctionとか、一括して整理したいなぁと思いまして。なんか一括で消せないのかなーと思って調べたのですが、特にそういう機能は無いらしく、1個ずつ手動で消すのはあまりにも面倒だったので、↓みたいな方法で消すことにしました。

 

◆FunctionでFunctionを消す

　やり方は単純で、システムテーブルからFunction名とか引数定義とかを引っ張ってきて、動的SQL文を作って削除するってのを繰り返すだけです。らくちんね。
　自分自身を消さないようにしたり、対象を絞るようにするのを忘れないでください。例えば弊社の場合は、関数名の先頭に「fn_」ってつけることが規則になっていたので、絞り込むのは簡単でした。

・システムテーブルからFunction名と引数定義を取得する

SELECT
    nmsp.nspname || '.' || pgp.proname || '(' || pg_catalog.oidvectortypes(pgp.proargtypes) || ')' AS functionName
FROM
    pg_catalog.pg_proc pgp 
    LEFT JOIN pg_catalog.pg_namespace nmsp
    ON nmsp.oid = pgp.pronamespace
WHERE
        pgp.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
    AND NOT pgp.proisagg
    AND nmsp.nspname = 'public'
    AND pgp.proname like 'fn_%'
;

　これを実行すると、publicに定義されてる、名前がfn_ではじまるfunction一覧が引数の型定義とセットで手に入ります。「名前(引数1の型, 引数2の型)」みたいな形で。あとはこれを、「DROP FUNCTION 名前(引数1の型, 引数2の型);」という文字列に加工してやって、ループで動的SQLとして実行し続ければOKです。

・動的SQLとしてDROP文を実行する

DECLARE
    rec_funcname_list  record;
    txt_sql            text;
BEGIN
    FOR rec_funcname_list IN
    SELECT
            nmsp.nspname || '.' || pgp.proname || '(' || pg_catalog.oidvectortypes(pgp.proargtypes) || ')' AS functionName
    FROM
            pg_catalog.pg_proc pgp
            LEFT JOIN pg_catalog.pg_namespace nmsp 
                   ON nmsp.oid = pgp.pronamespace
    WHERE   pgp.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
      AND   NOT pgp.proisagg
      AND   nmsp.nspname = 'public' 
      AND   pgp.proname like 'fn_%'
    LOOP
        txt_sql = 'DROP FUNCTION ' || rec_funcname_list.functionName;
        execute txt_sql;
    END LOOP;
END;

　エラー処理とかは省略してます。実際に使ってるやつからは若干改変してます。（ほんとちょとだけ）
　先に書いたSQLの結果件数だけループさせて、「execute txt_sql;」で実行し続けるだけの簡単なfunctionです。

　使う場合には自己責任でどうぞ。（事前に、SELECT部分だけ実行してちゃんと目的のものだけになってるか、は目を通しておいたほうがいいと思います）

PostgreSQL Conference Japan 2018 に参加してきました

 

　11/22に東京で行われた「PostgreSQL Conference Japan 2018」に参加してきました。

　本イベントは日本PostgreSQLユーザー会主催のイベントで、PostgreSQLに関する色々なお話が聞ける楽しいイベントでした。有料イベントではありますが一般参加なら5,000円(早割なら半額)と比較的安価なので、気軽に参加できました。とはいっても、私はお仕事として参加したので会社持ちです。(しかし懇親会費用は出なかった)

　当日はTwitterでハッシュタグ付きでツイートしまくってたので、最近私をフォローした人はビックリしたかもしれません。普段はずっと「ラブライブがー！」「Aqoursがー！」とかばかりツイートしてますが、実は私、本当は技術者側の人間なんですよ！（ﾃﾞﾃﾞﾝ

　ということで、ざっくり振り返りです。自分のメモとかツイートとかベースに振り返ってます。
　AMは基調講演が2つ、PMは3つのトラックに分かれて一般公演、という感じです。受付でノベリティとしてロゴ入りの水とシールなどをもらいました。わーい。

　

◆基調講演

　基調講演1つ目は、NTT OSSセンタの坂田氏による、企業ユーザーから見たPostgreSQLの歴史と展望。Ver8あたりから使って・コミュニティに貢献されてきたとのことで、自分が知らない時代の話が聞けて面白かったです。
　私がさわりはじめたのがVer9.4からなんですが、この話に出てきたVer8のころは色々と課題も多かったようで……チェックポイント時に大幅に性能が低下するとか、VACUUM時の課題とか……。そういった課題を確実に対応していって、今ではビジネスユースにも十分に応えられるものになっていったんですね。
　また、レプリケーション機能に関して、当時コミュニティとしては「外部機能として実装」というスタンスでいたものの、8.1のころにNTT内部で開発を勧めて8.3でOSS版を公開し、それの必要性を各所で語って回った結果本体機能として段階的に取り込まれていったそうです。中々に胸熱な話でした。
　現状もPostgreSQLはまだ課題を抱えていたり機能として中途半端なものもあるけれど、上記のように段階的に進んでいくとのことなので、今後の更なる改善に期待がもてそうです。

　2つ目の基調講演は、Microsoftの藤田氏。
　なんとMicrosoftはこのイベントのプラチナサポンサーとのこと。随所で、MSはOSSに力いれてますよって話とセットで古き悪しきMS帝国みたいな自虐的な話が入り、正直自分はその手のネタはもう食傷気味なんですが、参加者にはそれなりにウケていました。MSに対してはまだそんな感じのイメージの人が多いんだなぁ、とぼんやり思ったり。
　前半ではAzureの話、後半からはAzure上の「Azure Database for PostgreSQL」の紹介。Azureに限らずクラウドのフルマネージドなDBサービス使うと楽ですよー、といった感じ。これは自分も全面的に同意です。
　AzurenoPostgreSQLは、MS独自のカスタマイズはしておらず、コミュニティ版をそのまま使っているそうです。何か手を入れたくなったときはコミュニティにパッチを投げているとのこと。現状は9.5、9.6、10をサポート。11はもう少し待ってください、とのことでそう遠くないうちにサポートされそうですね。
　ひとつ驚いたのは、「Azure Database for PostgreSQL」はダウンタイム無しでスケーリング可能とのことです。AmazonのRDSは1分程度ダウンすると聞いていたので、Azureもそうだと思ってたんですけど。ちなみにこれはService Fabricのおかげとかなんとか。（詳細はよくわかってないです）

　ちなみにAzure上のPostgreSQLは、実体としてはWindowsコンテナとして動作しているとのこと。前半の内容ではAzure上で動いているOSはLinuxが50%以上になった的な話もしてたので、これにはビックリしました。てっきりLinuxかと……。Windowsコンテナで動いてるってことは、PostgreSQLもWindows版ってことですから、ちょっとだけ注意が必要になりそうですね。

 

◆一般公演

　PMからの一般公演は3つのトラックにわかれており、そのうちの1つはチュートリアルコースとなっていました。私はこれには参加してないのですが、10月にリリースされたVer11の解体新書とか、チューニングに関する話とか、結構興味のある内容もありました。身体が3つあれば……！
　というわけで参加したやつだけ、簡単に振り返りをば。

 

・A1.MySQLからPostgreSQLへの移行とDBリファクタリング

　株式会社オミカレの高橋氏による、実例に基づいた話。いわゆる「秘伝のソース」状態と化していたため、例えばDBテーブルの性別欄に入っている値が「0、1、2、男性、女性」という状態になっているといった、「あーあるある」ってみんなの目が死ぬような状況にあったものを、PostgreSQLへ移行したというお話。
　僕個人としては、担当してるシステムのDBは最初からPostgreSQLなので移行という観点ではあまり関係ないのですが、移行時にMySQLと比較した場合のメリットとか(トリガー関連が決め手になったようです)が聞けて、中々に新鮮な話でした。

 

・A2.EDB Postgres はここまできた！パフォーマンス問題解決のヒント

　株式会社アシストの佐瀬氏による、PostgreSQL11での性能改善に関する話が中心でした。セッションタイトル的にEDB版の話がメインのように感じられますが、実際にはそんなにEDB要素は無かったです。
　パーティショニングに関する性能改善がかなり大きいらしく、パーティション化していない9.6のDBとパーティション化したPostgreSQL11のDBとでは、最大350倍もの性能差があったとのこと。350……これは大きいですね。どうしてそこまでの性能差が出たのか、内部的な動きも一部含めて解説。
　また、アシスト社への問い合わせが多い内容や実例などから、パフォーマンスチューニングで実際に起こっていた問題と対処方法などをいくつか紹介してくれました。Oracleから移行してきたパターンだとOracleのヒントをそのまま使っている場合に誤った実行計画になるとか、大量にテーブルを結合している場合の「join_collapse_limit 」「from_collapse_limit」パラメータに関する話など。このあたりは結構参考になりそうで、助かります。

 

・B3.Citusを使ってPostgreSQLをスケールアウトしてみよう

　ここだけBトラックに移動して、スケールアウトの話を聞いてきました。
　日本HPの篠田氏によるCitus（サイタス。シータスではないらしいです）の概要とその導入方法。CitusはExtensionsとして提供されているため、導入は簡単に行えるというのが最大のメリットかと。コーディネーターと複数のワーカーによって構成されて、実データはワーカー側に分散して保存する、というもの。
　しかし意外と制約が多く、一例として「分散キーの更新不可能」「SELECT FOR UPDATE不可能（レプリカ作成してる場合のみ）」「WITH RECURSIVE句使用不能」「PARTITION BY句の使用に制限あり」など。WITH RECURSIVEが使えないのは結構痛いですね……。しっかり吟味する必要はありますが、条件があうなら簡単に分散化できるので検討の余地はありかと。

 

・A4.運用が大分変わるよ。オンプレ PostgreSQL から AWS のマネージド PostgreSQL の担当になっての知見

　オイシックス・ラ・大地株式会社の林氏による、ECサイト上の分析基盤としてAWS上のPostgreSQLを使うようになった話。それ以前はMySQLを使用されていたそうです。
　この直前に行われていたA3トラックがAWSの紹介だったことと、そのまま継続してA4を受けている人が多かったからか、クラウドを採用するメリットを説明する際にAWSへの熱いダイマが入っていました（サーバーを即用意できるとか、フルマネージドなので管理が楽とか）。 AWSにしろAzureにしろ、特に理由がない限りはもうクラウド上のマネージドサービス使ったほうが圧倒的に楽なんですよねぇ……と改めて実感。
　また、AWSのRDS PostgreSQLはスケーリングに応じて一定の閾値でチューニングが自動的に行われるため、ある程度はチューニングを意識せずに使える、というメリットもあるとのこと。これは初耳でした。しかしスケーリング時には一時停止があるため注意、とのこと。無停止でスケーリング可能なAzureと、チューニング不要のAWSと……どっちもメリットデメリットがある感じですね。

 

◆LT

　最後にLTがいくつかありました。
　「デッドロックが趣味」という人とか、「PostgreSQLで山手線ゲーム」をした人など、中々に濃い？内容でした。今までMS系の勉強会とかカンファレンスへの参加が多めな私でしたが、ああ、どこ行っても頭おかしい（誉め言葉）人はいるんだなぁ、と感じました。（小並感）

　その後は懇親会でしたが、私は不参加なのでここで終了と相成りました。

 

　個人的には、結構収穫の多いカンファレンスだったなと思います。
　最近の自分は、なんだかんだでPostgreSQLにどっぷりになりつつあるので、今後も勉強会とかカンファレンスがあれば積極的に参加していきたいですね。

　あ、あとA4で紹介されてた「[改訂新版]内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 (Amazonへのリンク)」は、早速買ってきました。まずはざっくり読んでおこうかなと。

PostgreSQL:お仕事の関係でAzure Database for PostgreSQLを立てたので、そのメモ

　仕事のほうではずっと社内のWindows版PostgreSQLしか使ってこなかった自分ですが、ここ最近になって、DBをクラウドのフルマネージド環境に移行させる流れがあがってきまして。その流れでAzure上に1つ作成したのですが、折角なので、メモを残しておきます。そんなに大して難しくないですけど。

　尚、Azureのアカウント作ったりとかそういうところはバッサリとカットです。

◆AzureにPostgreSQLのDBを作成する

　まず、ポータル画面にアクセスしたら、左のメニューの「すべてのサービス」を開きます。
　Azureにはいっぱいサービスがありますが、「データベース」カテゴリの中に「AzureDatabase for PostgreSQL」があります。名前の右側にある「★」のマークをクリックすれば、左メニューのお気に入り欄に表示されるようになるので、押しておきましょう。

 

　メニューをクリックしたら、PostgreSQLの作成したインスタンス一覧が表示されます。最初は何もないですけど、スクリーンショット取ったのが作成後なので既に出来ちゃってますが、気にしないでください。
　新しく作成するためには、左上にある「追加」をクリックします。

 

　新規作成にあたって入力する内容はそんなに多くないです。

　サーバー名：任意のもの
　サブスクリプション：契約してるやつを
　リソースグループ：とりあえずテキトーなリソースグループを作成しました
　サーバー管理者ログイン名：任意のもの（ここでは定番の「postgres」に）
　パスワード：任意のもの（パスワード文字列にはルールあり）
　場所：任意の場所
　バージョン：11月2日現在で選べたのは 9.5、9.6、10 の3つ

　そして最後の「価格レベル」の欄です。
　サーバーに割り当てるスペックに応じて金額が変わります。高スペックであれば当然、高価格になりますし、低スペックならばそれなりに安くなります。

　ほぼほぼ最低状態にして、毎月のコスト見積もりがおおよそ4400円。これ、実際には使用した分に応じての課金っぽいので、あくまでも見積もり価格だということに気を付けましょう。

　構成を選んだら画面下のOKボタンを押すと、一つ前の画面に戻ります。内容に間違いがなければ、これまた画面下の「作成」ボタンを押します。それからしばらく（1分程度？）待つと、通知欄に作成完了の旨が表示されます。

 

◆接続してみる

　作成が完了したら、Azure Database for PostgreSQLの一覧に表示が出てきていますので、サーバー名をクリックします。すると、そのサーバーの「概要」ページが表示され、詳細が確認できます。

　設定の「接続のセキュリティ」をクリックします。

 

　テスト用なので、「SSL接続」を無効に。ファイアウォール規則のところに、自PCのIPアドレスを打ち込んで接続許可の状態にします。（入力後、画面上の「保存」を押すのを忘れずに）

 

　ここまで設定しておけば、とりあえず、ローカルクライアントから接続可能になっているはずです。pgAdminなりA5SQLなりのクライアントを起動して、接続テストをしてみます。今回、この記事書いてる段階で自PCのpgAdminがご機嫌ナナメだったので、A5SQLで確認してみます。

 

サーバー名：先ほどの概要ページに表示されているので、それを
データベース名：postgres
ユーザー名：作成時に指定したもの（後ろに「@サーバー名」をつける必要があるので注意）
パスワード：作成時に指定したもの
ポート番号：既定の5432

　内容に誤りが無ければ、テスト接続できるはず。

 

　接続できたあとは、普通にPostgreSQLです。

 

◆少しだけ見てみる

　さてさて、これでPostgreSQLのインスタンスが立ち上がったわけですが、現在どんな状態なんでしょうか。少しのぞいてみます。まずはさくっと、バージョン確認。

　作成時に指定したバージョンは「10」でした。実際にインストールされたものは「10.5」です。10.5がリリースされたのは今年の8月で、現時点では10での最新版ですね。割と早めに新しいバージョンが導入されているようで、好感触です。流石に先月リリースされたばかりのPostgreSQL 11 は、まだみたいですが。

 

　次に、DB一覧を見てみます。

　初期状態では、デフォルトのpostgres以外にも2つ、DBがあることがわかります。名前的に恐らくAzure側の管理・メンテ用でしょう。これらにはウカツに触れないようにしておきましょう。

　では、試しにCREATE DATABASEしておきましょう。

CREATE DATABASE test_db_01 WITH
       OWNER = postgres
       TEMPLATE = DEFAULT
       ENCODING = 'UTF8'
       TABLESPACE = DEFAULT
       CONNECTION LIMIT = -1
;

 

　で、もう一度、DB一覧を取得してみます。

 

 

　ちゃんと出来てますね、OK。

 

　これから先、しばらくAzure Database for PostgreSQLを少しいじっていってみたいと思います。なのでまた何か記事にするかも。

PostgreSQL：「pg_stat_statements」がちょっと便利になってた件

 

　前回の記事の最後でPostgreSQL10にしたい、的なことをぼやいたおかげか、先日から、ようやく社内でもPostgreSQL10の対応がはじまりました。
　まあそんなに問題も発生せず無事に終わりそうなんですが、性能検証しているときに、PostgreSQLではお馴染みの「pg_stat_statements」を使ってスロークエリを探したりしてたんですね。こいつは、クエリの実行回数やかかった時間などを記録した内容を見せてくれるビューなので、性能検証などで処理が遅い部分などを調べていくときには、とてもお世話になっております。

　で、10にしてはじめて知ったんですが……実はいつの間にか、pg_stat_statementsビューのカラムが追加されてたんですよ。「min_time」「max_time」「mean_time」の3つで、それぞれ名前の通り、そのクエリの実行にかかった「最少時間」「最大時間」「平均時間」を記録してくれます。それまで使ってたVersionで記録してくれる時間は「total_time / 合計時間」だけだったので、微妙に使いにくかったんですよね。こういう追加は助かります。

SELECT 
   query
  ,calls
  ,(mean_time / 1000)::numeric(7,3) AS avg_time_sec
  ,(min_time / 1000)::numeric(7,3) AS min_time_sec
  ,(max_time / 1000)::numeric(7,3) AS max_time_sec
FROM
   pg_stat_statements
--WHERE
--   (max_time / 1000) > 3
ORDER BY
   max_time_sec DESC
LIMIT 20;

--※情報をリセットする場合
--SELECT pg_stat_statements_reset();

　こんな感じで使ってます。各時間はミリ秒単位になってるので、僕は1000で割って秒単位に変換してから見ています。検索条件次第で、解析の幅が少し広がりますね。

　調べてみたら9.5から追加されてたようです。これまではずっと9.4を使ってたので、もっと早くバージョンアップさせたかったなぁ……と思いました。いうほど大したものでもないんですけど、地味に便利になりました。この記事が、まだ9.4以前をお使いの方へのバージョンアップの一助になれば……。

PostgreSQL：複数定義があるFunctionの一覧をクエリで取得する

 

　お仕事で作ってるシステムでPostgreSQLを使っておりまして、その際のTIPSというか個人的な覚書というか、そんなことも今後はブログに書いておこうかなと。で、今回のお題はタイトル通りです。（※タイトル少し変だったので修正しました）

◆背景

　今、仕事で担当しているシステムでの話ですが、DB側に作っているFunctionが、とっっっっっっっっっっっても！多くて！！
　数多すぎてまともに管理できておらず、しかも仕様変更も多くて、引数が増えたり減ったりがザラにあります。20個以上の引数が必要なFunctionに引数追加させられて、でも数日後に削除させられたときはキレかけました。そんなことばっかりやっているため、古い引数定義のFunctionを消し忘れているという事態がチーム内で多発しました。
　で、PG側の修正が洩れていたときに、古い定義がちゃんと消してあればエラーになるからいいんですけど、消し忘れているとエラーにならないため、一見正常に動いているように見えるけど実は……ということがいっぱいありまして。

　そのため、定期的に自動で複数定義あるFunction一覧を取得してチェックしたい、という要望があって作りました。pgAdmin等でFunctionの一覧見ればすぐわかることではありますが、自動化させるためにクエリで取ることに。

　そもそもちゃんとした管理体制とか用意すべきなんですが、まずは応急措置的な感じとして。

 

◆クエリ

　こんな感じ。
（動作環境：PostgreSQL 9.4/Windows版）

SELECT
   main.proname AS function_name
  ,main.proargnames AS parameters
  ,FORMAT_TYPE(main.prorettype, NULL) AS return_type
  ,ROW_NUMBER() OVER(PARTITION BY main.proname ORDER BY main.proname ASC, main.proargnames ASC) AS overlap_count
FROM
   pg_catalog.pg_proc main
INNER JOIN (
             SELECT
                chk.proname
             FROM
                pg_catalog.pg_proc chk
             INNER JOIN pg_namespace nms
                     ON chk.pronamespace = nms.oid
--             INNER JOIN pg_catalog.pg_user usr
--                     ON chk.proowner = usr.usesysid
             WHERE
                nms.nspname = 'スキーマ名'
--             WHERE
--                usr.usename = 'オーナーのユーザー名'
             GROUP BY
                chk.proname
             HAVING
                COUNT(1) > 1
           ) cnt
        ON main.proname = cnt.proname
ORDER BY
   main.proname
  ,main.proargnames
;

　このクエリで、スキーマ名やらユーザー名やらで対象範囲を絞って、取得した結果が1件以上あるときは消し忘れがある、という判定で使っています。範囲の絞り込みはクエリを少しいじれば(コメントアウト部分のあたりとか)、ある程度は融通が効きますね。

 

　別に特殊なことをしているわけではありませんが、今後またどっかで使うかもしれないので。使いたい方がいらっしゃったら、自己責任ですがどうぞご自由に～。

 

 

余談：なんでもいいけど、そろそろPostgreSQL10にバージョンアップしたい。したくない？