OSS ユーザーのための勉強会 <OSS X Users Meeting> #6 　

PostgreSQL と MySQL

Lighting-Talk私的な PostgreSQL の楽しみ方

ぬこ＠横浜 (@nuko_yokohama)

自己紹介「ぬこ＠横浜」で検索

ラーメンの食べ歩きと無駄な PostgreSQL 拡張を考えるのが趣味

さて、 MySQL と PostgreSQLどっち派なの？と聞かれたら躊躇なく PostgreSQL 派と私は答えちゃうわけですが

なんで私が PostgreSQL をお気に入りなのか

無駄に？機能拡張能力が

高いから

ユーザ定義関数ユーザ定義型

独自インデックスForeign Data Wrapper

Background Worker ProcessCustom Scan API手続き言語ハンドラ

HOOK 組込etc ・・・

面白いので自分でも

いくつか作った

ユーザ定義型の例

漢数字型 (ksj)

SELECT data, data + '拾 ' FROM ksj_sample; data | ?column? ------------+---------------- 五百壱拾六 | 五百弐拾六 零 | 壱拾 参万壱百壱 | 参万壱百壱拾壱(3 rows)

SELECT data, data * '拾 ' FROM ksj_sample; data | ?column? ------------+-------------- 五百壱拾六 | 五千壱百六拾 零 | 零 参万壱百壱 | 参拾壱千壱拾(3 rows)

SELECT data, data % '拾 ' FROM ksj_sample; data | ?column? ------------+---------- 五百壱拾六 | 六 零 | 零 参万壱百壱 | 壱(3 rows)

SELECT sum(data) FROM ksj_sample; sum ---------------- 参万六百壱拾七(1 row)SELECT * FROM ksj_sample ORDER BY data; data ------------ 零 五百壱拾六 参万壱百壱(3 rows)

漢数字で四則演算や

比較演算できます

ユーザ定義型の例

ゆるい TEXT 型(ntext)

SELECT * FROM foo WHERE data = '味噌らーめん '; id | data ----+-------------- 1 | 味噌ラーメン(1 row)

SELECT * FROM foo WHERE data = 'postgresql'; id | data ----+---------------------- 2 | ＰｏｓｔｇｒｅＳＱＬ(1 row)

SELECT * FROM foo WHERE data = 'エバンゲリオン '; id | data ----+------------ 3 | ｴｳﾞｧﾝｹﾞﾘｦﾝ(1 row)

SELECT * FROM foo WHERE data = 'センヌリティウス '; id | data ----+------(0 rows)

SELECT * FROM foo WHERE data = 'フィロストラス '; id | data ----+------(0 rows)

SELECT * FROM foo WHERE data /= 'センヌリティウス '; id | data ----+------------------ 4 | セリヌンティウス(1 row)

SELECT * FROM foo WHERE data /= 'フィロストラス '; id | data ----+------------------ 5 | フィロストラトス(1 row)

日本語正規化＋

typo を許容

Foreign Data Wrapper の例

neo4j_fdw

Neo4j Server

Neo4j Foriegn Data WrapperPostgreSQL

REST I/FCypherQuery

JSONResult

SQL

PostgreSQLRecord

Set Cypher Query

Execute Cypher Query on REST

Generate PostgreSQL Record

Neo4j-FDWConceptDo not convert SQL into Cypher!

neo4j_fdw のデータソース

CREATE EXTENSION neo4j_fdw;CREATE EXTENSIONCREATE SERVER foo FOREIGN DATA WRAPPER neo4j_fdw OPTIONS (url 'http://localhost:7474/db/data/cypher');CREATE SERVER

CREATE FOREIGN TABLE bar2 ( name text, follower_name text, follower_gender text) SERVER foo OPTIONS (query '{"query":"START n=node(*) MATCH p=n<-[]-fm RETURN n.name as name , fm.name as follower_name, fm.gender? as follower_gender " }');CREATE FOREIGN TABLE

SELECT name, follower_name, follower_gender FROM bar2; name | follower_name | follower_gender ---------+---------------+----------------- Yamato | Akagi | Famale Musashi | Yamato | Male Akagi | Hiryu | Famale Akagi | Yamato | Male Kaga | Hiryu | Famale Kaga | Musashi | Male Nagato | Mutsu | Male Nagato | Yamato | Male Mutsu | Nagato | Male Hiryu | Soryu | Famale Hiryu | Akagi | Famale Soryu | Hiryu | Famale(12 rows)

グラフデータベースのビューを定義して、

そこへ SQL で問い合わせ

Background Worker Process の例

hit_and_blow

hb_worker.so

Postgres server(9.3)

BackgroundWorker Process(libpqを使用 )

【 postgresql.conf】

shared_preload_libraries = 'hb_worker'hb_worker.conninfo = 'dbname=xxx user=yyy'

psql

チャネルHB_SV

チャネルHB_CL

NOTIFY HB_CL,'結果 ' NOTIFY HB_SV,'数字列 '

こんな感じ起動

LISTEN HB_CL

起動

LISTEN HB_SV

ロード・起動

数当て判定処理

psql からの実行例postgres=# LISTEN HB_CL;LISTENpostgres=# NOTIFY HB_SV,'xxxx';;NOTIFYAsynchronous notification "hb_cl" with payload "Invalid data.(xxxx)" received from server process with PID 29520.postgres=# NOTIFY HB_SV,'0123';;NOTIFYAsynchronous notification "hb_cl" with payload "2 Hit / 1 Blow." received from server process with PID 29520.postgres=# NOTIFY HB_SV,'0813';;NOTIFYAsynchronous notification "hb_cl" with payload "4 Hit! Conguratulatoins!, next new game." received from server process with PID 29520.postgres=#

hb_worker の応答LOG: NOTIFY HB_CL,'2 Hit / 1 Blow.'LOG: hb_worker: NOTIFY HB_CL,'4 Hit! Conguratulatoins!, next new game.'

LOG: hb_worker: set secret number=0391

（注：見やすさのために適宜改行している）

psql から数当て

HOOK の例

pg_heartman

pg_heartman とは

[nuko]$ LOG: loaded library "hartmanhook"LOG: database system was shut down at 2014-02-16 04:45:33 PSTLOG: database system is ready to accept connectionsLOG: autovacuum launcher started

[nuko]$ psql testpsql (9.3.2)Type "help" for help.

test=# SELECT 1;NOTICE: Sir, yes, sir! ?column? ---------- 1(1 row)

test=# \dNOTICE: Sir, yes, sir! List of relations Schema | Name | Type | Owner --------+------+-------+-------- public | test | table | harada(1 row)

test=#

SQL を投げるとハートマン軍曹から罵ってもらえます（現在実装中）

実にくだらない

でも、拡張機能を使って、こういう遊びが出来るのがPostgreSQL の

好きなところです

時間が余ったらデモします

おしまい