技術メモ置き場

2016-03-03

PLpgSQL クイズ

| 22:12

ワケあってPLpgSQL + Triggerのクイズを解いたのですが、PLpgSQLの基本はもとより、pg_notify()やらCTEやらJSONやら、実力を試すにはよいクイズだったように思うので、上げておきます。

質問

以下のテーブルがある。

CREATE TABLE transaction (
       id SERIAL PRIMARY KEY,
       payment_id BIGINT,
       trade_id BIGINT
);

CREATE TABLE payment (
       id SERIAL PRIMARY KEY,
       tid BIGINT REFERENCES transaction(id) ON UPDATE RESTRICT ON DELETE RESTRICT NOT NULL,
       details JSON NOT NULL
);

CREATE TABLE trade (
       id SERIAL PRIMARY KEY,
       tid BIGINT REFERENCES transaction(id) ON UPDATE RESTRICT ON DELETE RESTRICT NOT NULL,
       details JSON NOT NULL
);

ALTER TABLE transaction ADD FOREIGN KEY (payment_id)
REFERENCES payment(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE transaction ADD FOREIGN KEY (trade_id)
REFERENCES trade(id) ON UPDATE RESTRICT ON DELETE RESTRICT;

次のような操作を行う (中身は本質的ではない)。どちらも自分自身とtransactionテーブルにデータを放り込む。

WITH op(details, tid, pid) AS 
(VALUES ('{"data":"details"}'::JSON,
     nextval('transaction_id_seq'), 
     nextval('payment_id_seq'))
),
tr AS (
   INSERT INTO transaction(id, payment_id)
   SELECT op.tid, op.pid FROM op RETURNING *
),
pm AS (
   INSERT INTO payment(id, tid, details)
   SELECT op.pid, op.tid, op.details FROM op RETURNING *
)
SELECT tr AS transaction, pm AS payment FROM tr CROSS JOIN pm;

WITH op(details, tid, trid) AS (
VALUES ('{"data":"details"}'::JSON,
    nextval('transaction_id_seq'),
    nextval('trade_id_seq'))
),
tr AS (
   INSERT INTO transaction(id, trade_id)
   SELECT op.tid, op.trid FROM op RETURNING *
),
td AS (
   INSERT INTO trade(id, tid, details) 
   SELECT op.trid, op.tid, op.details FROM op RETURNING *
)
SELECT tr AS transaction, td AS trade FROM td CROSS JOIN tr;

問題は"テーブルtransactionのトリガを作れ。ただし、tradeとpaymant両テーブルの変更分をpayloadとしてJSON形式でNOTIFYするように。"

答え

私の答え。

CREATE OR REPLACE FUNCTION trans_update () RETURNS trigger AS
$$
DECLARE
  target text;
  target_id bigint;
  payload text;
  query text;
BEGIN
 target := 'payment';
 SELECT NEW.payment_id INTO target_id;
 IF target_id IS NULL THEN
    target := 'trade';
    SELECT NEW.trade_id INTO target_id;
 END IF;
 query := 'SELECT to_json(t)  FROM ' || target || ' as t WHERE  t.id = ' || target_id;
 EXECUTE query INTO payload; 
 PERFORM pg_notify(target,  payload);
 RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER trans_trig AFTER INSERT ON transaction
       FOR EACH ROW EXECUTE PROCEDURE trans_update();

実行結果は以下。

sampledb=# LISTEN trade; LISTEN payment;
LISTEN
LISTEN
sampledb=# WITH op(details, tid, trid) AS (VALUES ('{"data":"details"}'::JSON,
     nextval('transaction_id_seq'), nextval('trade_id_seq'))),
tr AS (INSERT INTO transaction(id, trade_id) SELECT op.tid, op.trid FROM op RETURNING *),
td AS (INSERT INTO trade(id, tid, details) SELECT op.trid, op.tid, op.details FROM op RETURNING *)
SELECT tr AS transaction, td AS trade FROM td CROSS JOIN tr;
 transaction |              trade               
-------------+----------------------------------
 (19,,10)    | (10,19,"{""data"":""details""}")
(1 row)

Asynchronous notification "trade" with payload "{"id":10,"tid":19,"details":{"data":"details"}}" received from server process with PID 25552.
sampledb=# WITH op(details, tid, pid) AS (VALUES ('{"data":"details"}'::JSON,
     nextval('transaction_id_seq'), nextval('payment_id_seq'))),
tr AS (INSERT INTO transaction(id, payment_id) SELECT op.tid, op.pid FROM op RETURNING *),
pm AS (INSERT INTO payment(id, tid, details) SELECT op.pid, op.tid, op.details FROM op RETURNING *)
SELECT tr AS transaction, pm AS payment FROM tr CROSS JOIN pm;
 transaction |             payment              
-------------+----------------------------------
 (20,10,)    | (10,20,"{""data"":""details""}")
(1 row)

Asynchronous notification "payment" with payload "{"id":10,"tid":20,"details":{"data":"details"}}" received from server process with PID 25552.

こんなのいきなり30分から1時間くらいで解けと言われても、しばらくPLpgSQLもトリガも書いてないしNOTIFYも完全に忘れきっていたので1時間では無理だった。リハビリが必要。

ねこねこ 2016/03/24 11:00 そろそろバイブル新刊は出さないのですか?
9.5は機能も増えたので是非お願いします!

interdbinterdb 2016/03/24 16:36 出版を決めるのは著者ではなく出版社で、(多分)推定読者数に大きく依存するはずですので、出版社に直接言っていただければ。因みに今のところ出版社からのコンタクトはないです。

ねこねこ 2016/03/24 23:34 そういうものだったんですね。。
とりあえずダメ元でリクエストしてきました。
どうか出版社さんが動いてくれますように!

interdbinterdb 2016/03/25 10:59 ありがとうございます。

2016-02-23

PostgreSQLのROLLUP

| 03:34

こちらでROLLUPが考察さているが、PostgreSQLも先月(2016年1月)リリースのversion 9.5でROLLUPをサポートしたので、試す。

PostgreSQLのドキュメントはこちら。みるとわかるけども、PostgreSQLSQL-ServerはSQL標準準拠。MySQLが方言。


サンプルはこちらと同様のもの。

\d ureyuki
     Table "public.ureyuki"
  Column  |  Type   | Modifiers 
----------+---------+-----------
 id       | integer | 
 shurui   | text    | 
 ure_date | date    | 
 seibetsu | text    | 
 ure_su   | integer | 

 SELECT * FROM ureyuki;
 id |    shurui    |  ure_date  | seibetsu | ure_su 
----+--------------+------------+----------+--------
  1 | まんが       | 2016-02-01 | 男       |      4
  2 | まんが       | 2016-02-02 | 男       |      1
  3 | まんが       | 2016-02-02 | 女       |      2
  4 | 情報誌       | 2016-02-05 | 男       |      2
  5 | 情報誌       | 2016-02-03 | 女       |      2
  6 | ファッション | 2016-02-03 | 男       |      1
  7 | ファッション | 2016-02-04 | 女       |      1
(7 rows)

例1

MySQL

SELECT * FROM ( SELECT shurui, SUM(ure_su) sum_su
       FROM   ureyuki
       GROUP BY shurui WITH ROLLUP
     ) SQ
ORDER BY shurui;

は、PostgreSQLの場合、以下のように書く。

SELECT * 
FROM ( SELECT shurui, SUM(ure_su) sum_su
       FROM   ureyuki
       GROUP BY  ROLLUP (shurui)
     ) SQ                                  
ORDER BY shurui DESC;
    shurui    | sum_su 
--------------+--------
              |     13
 情報誌       |      4
 ファッション |      2
 まんが       |      7
(4 rows)

例2

MySQLのquery

SELECT * FROM
 (SELECT shurui, seibetsu, SUM(ure_su)su
  FROM ureyuki
  GROUP BY shurui, seibetsu WITH ROLLUP) SQ
ORDER BY shurui, seibetsu;

PostgreSQLの場合、次のように書く。

SELECT * FROM  (SELECT shurui, seibetsu, SUM(ure_su)su
  FROM ureyuki
  GROUP BY ROLLUP (shurui, seibetsu)) SQ
ORDER BY shurui, seibetsu;
    shurui    | seibetsu | su 
--------------+----------+----
 まんが       | 女       |  2
 まんが       | 男       |  5
 まんが       |          |  7
 ファッション | 女       |  1
 ファッション | 男       |  1
 ファッション |          |  2
 情報誌       | 女       |  2
 情報誌       | 男       |  2
 情報誌       |          |  4
              |          | 13
(10 rows)

2016-02-22

「ま、ここでいいか」と適当なディレクトリにPostgreSQLのデーブルスペースを作って後悔した場合の対処法

| 08:42

オリジナルの記事はこちら


ディレクトリ`/usr/local/pgsql/tblspc`に作った`tblspc`を、`/home/postgres/tblspc`に移す。


[1] テーブルスペース`tblspc`のOidを調べる
testdb=# SELECT oid,spcname FROM pg_tablespace WHERE spcname = 'tblspc';
  oid  | spcname 
-------+----------
 24580 | tblspc
(1 row)
[2] PostgreSQLを止める
postgres$ pg_ctl -D $PGDATA stop

[3] ディレクトリを移動する
postgres$ mv /usr/local/pgsql/tblspc /home/postgres/tblspc

or

postgres$ cp -r /usr/local/pgsql/tblspc /home/postgres/tblspc
postgres$ rm -rf /usr/local/pgsql/tblspc

[4] pg_tblspc以下のリンクを張り替える
postgres$ cd $PGDATA/pg_tblspc
postgres$ rm 24580
postgres$ ln -s /home/postgres/tblspc 24580
[5] PostgreSQLを起動する
postgres$ pg_ctl -D $PGDATA start

以下の関数テーブルスペースの新しいディレクトリが確認できる。

testdb=# SELECT pg_tablespace_location(24580);
  pg_tablespace_location
--------------------------
/home/postgres/tblspc
(1 row)

参照

The Internals of PostgreSQL

2016-02-11

Postgres-XC 多重化GTM システムが走るVagrant box

| 13:39

(5年前に作った)Postgres-XC用の多重化GTMシステムが動くVagrant boxを作ったので公開します。

Postgres-XC用の多重化GTMシステムについては例えば”こちら”を参照。

f:id:interdb:20160211012339p:image

Requirement

Install

GitHubからリポジトリをcloneしてください。

# git clone https://github.com/s-hironobu/mgtm.git

次にAtlasからVagrant boxをダウンロード登録します。

# cd mgtm
# vagrant box add s-hironobu/centos67_32_mgtm4pgxc092 

実行

[1] 起動

5つのVMを起動します: mgtm1, mgtm2, mgtm3, node1, node2

# vagrant up

ssh関連で起動に失敗する場合があります。そのときは起動がうまくいかないVMvagrant destroyで一旦削除し、改めてvagrant upで作成するとうまくいくことが多いです。

[2] 5つのターミナルを作成

5つのターミナルを、以下の図のように生成します。

f:id:interdb:20160211012219p:image

ターミナルvagrant sshでアクセスします。例えばmgtm1には次のようにしてアクセスします。

# vagrant ssh mgtm1
[vagrant@mgtm1 ~]$
[3] GTM起動

mgtm1, mgtm2, mgtm3で mgtm-startコマンドを実行します。

[vagrant@mgtm1 ~]$ mgtm-start
[vagrant@mgtm2 ~]$ mgtm-start
[vagrant@mgtm3 ~]$ mgtm-start

すると、mgtm1に以下のようなメッセージが表示されます。

[vagrant@mgtm1 ~]$ mgtm-start
I'm running...
I'm LEADER!!!!
[4] XC-nodeの起動

node1とnode2でnode-startコマンドを実行します。

[vagrant@node1 ~]$ node-start

I'm ready!!!
[vagrant@node2 ~]$ node-start

I'm ready!!!

両方のターミナルに"I'm ready!!!"というメッセージがでれば、それがPostgres-XC準備完了の合図です。


[5] pgbench

node1かnode2でpgbenchを実行します。

[vagrant@node2 ~]$ pgbench

SELECT, UPDATE, INSERT, DELETEなど簡単なSQLも実行できます。(ただし、最初に実行する前にvacuumを実行してください。)

[vagrant@node2 ~]$ psql -c "vacuum"
VACUUM
[vagrant@node1 ~]$ psql 
psql (8.4.3)
Type "help" for help.

pgbench=# 

XC version 0.9.2は超基本的なSQLしかサポートしていません。実行できないSQLの場合はシステムがクラッシュします。

GRMクラッシュのシミュレーション

(1) pgbenchを-Tオプションで起動

適当な時間、pgbenchを実行し続けます。

[vagrant@node2 ~]$ pgbench -T 30

(2) mgtm1で^Cを実行:mgtm1を強制終了
[vagrant@mgtm1 ~]$ mgtm-start
I'm running...
I'm LEADER!!!!
I'm LEADER!!!!
I'm LEADER!!!!

^C

すると、mgtm2が次のリーダーになります。

[vagrant@mgtm2 ~]$ mgtm-start
I'm running...
I'm LEADER!!!!

この状況を図に示します。

f:id:interdb:20160211012037p:image

GTMとXCの終了

GTMは^Cで終了させます。

[vagrant@mgtm2 ~]$ mgtm-start 
I'm running...
I'm LEADER!!!!
^C
[vagrant@mgtm3 ~]$ mgtm-start 
I'm running...
^C

node1とnode2でnode-stopコマンドを実行し、関連プロセスを終了します。

[vagrant@node1 ~]$ node-stop
[vagrant@node2 ~]$ node-stop

再実行

もう一度試したい場合は、[3] GTM起動に戻ります。

Vagrantの終了

どれか一つのターミナルexitし、HostOS上でvagrant haltを実行します。

[vagrant@node2 ~]$ exit

# vagrant halt

ベンチマーク

詳しい説明抜きでベンチマーク結果を示します。

f:id:interdb:20160211011711p:image

"mgtm x 3"は3台のgtm, "mgtm x 2"は2台のgtmが稼働しているときの結果。gtmはXCオリジナルgtmによる結果です。


Memo

簡単にmulti-master open source database clusterの状況をしめします。

  1. 2015年、Postgres-XCはPostgres-X2に改名しました。相変わらずwrite-scalableが目標です。
  2. 2014年、XCをベースにしたPostgres-XLがリリースされました。Postgres-XLは大規模なデータに対する効率的なOLAPやOLTPを目指しています。
  3. 一方、Galera Cluster (MariaDBMySQL)が技術的にも商業的にも成功しています。GaleraはPostgres-Rとほぼ同様の機構(XC/XLとは全く異なる)のmulti-master database clusterです。

2015-10-07

PostgreSQL version 9.5 のWALファイル管理

| 14:03

公開した文書のごく一部を、翻訳して公開。(BLOGにするにあたり、構成は変えた。)

http://www.interdb.jp/pg

概要

  1. checkpoint_segmentsが廃止され、WALファイル数の上限下限をmax_wal_sizeとmin_wal_sizeで指定できるようになった。
  2. WALファイル数は(これらの制限内で)サーバの活動状況=WALファイルの消費量に合わせて変化する。あまり書き込みがなければWAL数は減少し、活発になればWAL数が増える。
  3. 保存するWAL数は基本的にリカバリに必要不可欠なものに限られ、無駄なWALファイルを溜め込む事は無くなった。

理解のための前提条件

CHECKPOINTの起動タイミング

CHECKPOINTは以下のどれか一つが発生した場合に起動する。

  1. checkpoint_timeoutに設定した時間が、前回のcheckpointから経過した場合。デフォルトのインターバルは300秒(5分)。
  2. バージョン9.4以前では、checkpoint_segmentsに設定した数のWALセグメントファイルが、前回のcheckpointから消費された場合。デフォルトの数は3。
  3. バージョン9.5の場合、pg_xlog以下のWALファイルの総サイズがmax_wal_sizeを超えた場合。デフォルトのサイズは1Gbyte (64ファイル)。
  4. smart かfastモードでPostgreSQLサーバが停止する場合
  5. 手動でCHECKPOINTコマンドを実行した場合
  6. pg_start_backup()関数が実行されたとき

version 9.5からはcheckpoint_segments(廃止)でなく、max_wal_sizeによることに注目。

リカバリの概要

リカバリはCHECKPOINT起動直後のWALデータ書き込み位置=REDOポイントから始まる。もしもlast REDOポイントが読めない場合は、前の(prior) REDOポイントから始める。どちらも読めなければリカバリを諦める。

ここで大事な点は、リカバリ機構から明らかなように、「prior REDOポイントを含むWALファイルよりも古いファイルは不要」ということ。


注:「checkpointが終ったらprior-last間のWALファイルも消せば?」的意見もありますが(checkpointが終ったらというのも微妙だが)万が一 last-checkpoint recordが読み取り不可だった場合であってもprior checkpoint recordからリカバリできるので、決してprior-last間のWALファイルが無駄に保存されているわけではないので、誤解無きよう...

version 9.4までのpg_xlog以下のWALファイルの管理方法

WALセグメントファイルの数は主に以下の3つのパラメータで制御される:

  • checkpoint_segments
  • checkpoint_completion_target
  • wal_keep_segments

WALファイルの数は通常は (2 + checkpoint_completion_target) * checkpoint_segments + 1checkpoint_segments + wal_keep_segments + 1 files のどちらか大きいほうになる。この数はサーバのactivityによっては一時的に 3 * checkpoint_segments + 1 files まで拡大するときがある。

上に書いたように、CHECKPOINTはcheckpoint_segments個のWALファイルを消費したときに起きる。WALファイルの数は常に2*checkpoint_segmentsよりも大きいから、2つ以上のREDOポイント(lastとpriorは確実に、それ以前のものも)が常にWALファイルの中に含まれていることが保証されている。CHECKPOINTがタイムアウトによって発生しても同様である。よって、version 9.4以前のPostgreSQLリカバリのために十分な(時として必要以上の)WALファイルを常にpg_xlog以下に保存している。

実際のところ、checkpoint_segmentsは頭痛の種である。小さな値を設定すると頻繁にCHECKPOINTが発生して性能低下を引き起こし、大きな値を設定すると巨大なディスク領域が常に必要となるにも関わらず(保存された)WALファイルのすべてが必要とは限らない、というトレードオフ問題を抱え込んでしまう。

version 9.5のWALファイルの管理方法

Version 9.5でpg_xlog以下のWALファイルの管理方法が大幅に改善された。

CHECKPOINTが起動する度にPostgreSQL次のcheckpointサイクルでいくつのWALファイルが必要か推定し、推定された数のファイルをpg_xlog以下に用意する。推定値は以前のcheckpointサイクルで消費されたファイル数から計算する。計算式は突発的な変化にはあまり影響されず、大まかな傾向が反映されるようなものを使っている*1。また、推定値はprior REDOポイントを含むファイルから数え、その数はmin_wal_size (デフォルト 80Mbyte, 5 files)とmax_wal_size(1 Gbyte, 64 files)の間の値である*2

CHEKPOINTが起動する際、必要な数のファイルは保持もしくはリサイクルし、不要なファイルは削除する。具体的な例を下図に示す。CHECKPOINTの実行直前には6つのファイルがあり、そのうちWAL_3がprior REDOポイントを含む、そしてPostgreSQLは5つのファイルが必要と推定したと仮定する。この場合、WAL_1をWAL_7にリサイクルし、WAL_2は削除する。

f:id:interdb:20151007052421j:image

もしもWALの活動が突発的に上昇してもっとWALファイルが必要になった場合、総サイズがmax_wal_size未満の間は新しいファイルを作成する。例えば、下図において、WAL_7が一杯になったら、WAL_8を新たに作成する。

f:id:interdb:20151007052422j:image

このように、書込みが減れば推定値も小さくWALファイル数も減る;書込みが増えればそれに合せて推定値も大きくなり、WALファイル数も増える。いずれにせよ書込みが定常的に続くならWALファイル数もそれに応じた数に落ち着く(平衡する)。


激しい書込みがずっと続くと(checkpointの度に)推定値が徐々に増えていくが、それでも間に合わずにWALファイルが追加され、結果的に総サイズがmax_wal_sizeを超えたら、(新たに)CHECKPOINTが起動する。下図にこの状況を示す。CHECKPOINTによってあたらしいREDOポイントが生成され、last REDO pointはprior REDOポイントになる。そして不要な古いファイルはリサイクルする。

f:id:interdb:20160110031529j:image

このように、version 9.5は常にデータベースリカバリに必要なWALセグメントファイルだけを保持するので、上に述べたトレードオフ問題も解消できる。

*1:En+1を次回の推定値、Cnを今回の消費量、Cn-1を前回の消費量とすると、En+1 = 0.1*Cn + 0.9*Cn-1。 直近の消費量Cnに重み0.1を、その前の消費量に重み0.9を乗算するすることで、一時的に急激な変動があっても大きく影響を受けないような推定値計算法を採用している。

*2:version 9.4までもCHECKPOINT起動時に用意していたが、prior REDOポイントの位置は考慮していなかったし、このような賢い推定もしていなかった。