201505 PostgreSQLアンカンファレンス(PL/Pythonで作るWEBアプリ)
-
Upload
satoshi-yamada -
Category
Technology
-
view
105 -
download
2
Transcript of 201505 PostgreSQLアンカンファレンス(PL/Pythonで作るWEBアプリ)
クラサバクライアントとデータベースが直ビジネスロジックはクライアント側中心
graph LR id1(クライアントロジック)-->id2(データベース) style id1 fill:#955,stroke:#111,stroke-
width:4px; style id2 fill:#559,stroke:#f66,stroke-width:2px;
3層アプリケーション
間にWEB/APサーバ増えたクライアント側はブラウザビジネスロジックはWEB/APサーバ
graph LR id1(クライアント:ブラウザ)-->id2(WEBサーバ:
ロジック) id2-->id3(DB) style id1 fill:#955,stroke:#111,stroke-width:4px; style id2 fill:#555,stroke:#111,stroke-width:4px; style id3
fill:#559,stroke:#f66,stroke-width:2px;
構造PostgreSQLに子プロセスで直接リスニングさせるPL/Pythonを使用Pythonの を利用bottoleフレームワーク
プレースホルダーを使って実行する場合
CREATE FUNCTION pystrip(x text) RETURNS textAS $$
target_query = "select empno from emp where ename=$1"plan = plpy.prepare(target_query,["text"])r_set = plpy.execute(plan, [ "SMITH" ])
return r_set[0]["empno"]
$$ LANGUAGE plpythonu;
直接実行する場合CREATE FUNCTION pl_py_test(x text) RETURNS textAS $$
r_set = plpy.execute("select empno from emp where ename='SMITH'")return r_set[0]["empno"]
$$ LANGUAGE plpythonu;
PG2LAYER 構成でも2層構造
graph LR id1(クライアント:ブラウザ)-->id2(DBサーバ:
ロジック) style id1 fill:#955,stroke:#111,stroke-width:4px; style id2fill:#555,stroke:#111,stroke-width:4px;
ソースCREATE OR REPLACE FUNCTION start_bottole_httpd(v_port integer) RETURNS text AS$$
"""PG2LAYER PostgreSQL Two Layer Management System
* Low dependency ( only bottole.py , only dblink)* Low layer (browser <> DB, never AppServer or HTTP Server)* Low manage cost ( backup database backup the Web App)
It's Joke Web App.
2015/05 sayamada"""
import sysimport osimport jsonimport signal# $PGDATA配下に配置したbottole.pyをPYTHONPATHに追加しないといけないのでsys.path.append(os.getcwd()+"/PL_Python_Httpd")from bottle import route, run, template, response, request, get, post, redirect
# for DBLINKDB_NAME = "pg2layer_db"DB_USER = "sayamada"DB_HOST = "localhost"
api(select_list,table_name,where_col=None,col=None,col_val=None):
URLからクエリを生成しJSONのレスポンスを戻す
static(content_type,file_name):
静的なソース(css/js等)を戻す静的ファイルは表データとして格納
get_tmplt(tmplt_name=None):
bottoleのテンプレートファイルを戻すほぼHTML表データとして格納
do_query_over_dblink(v_query_string):
DBLIK経由でSQL処理しJSONで戻す(後述)
edit():
POSTリクエストに基づきテンプレートを更新
問題その1
ImportError: No module named bottoleカレントに配置したbottole.pyが読み込めない普通はカレントディレクトリは指定しなくてもいいPostgreSQLの子プロセスは$PGDATAがカレント
pg2layer_db=# CREATE OR REPLACE FUNCTION test() RETURNS text ASpg2layer_db# $$pg2layer_db$# pg2layer_db$# import bottolepg2layer_db$# $$pg2layer_db# LANGUAGE 'plpythonu' VOLATILE;CREATE FUNCTIONpg2layer_db=# pg2layer_db=# select test();ERROR: ImportError: No module named bottole CONTEXT: Traceback (most recent call last): PL/Python function "test", line 3, in <module> import bottolePL/Python function "test"
対処AP内で動的に$PGDATA配下をPYTHONPATH
に追加import sys# $PGDATA配下に配置したbottole.pyをPYTHONPATHに追加sys.path.append(os.getcwd()+"/PL_Python_Httpd")
PL/Pythonでトランザクション管理
をしてもだめだった(というかcommitないし)43.8. 明示的サブトランザクション
対処DBLINKで自律型トランザクション
DBLINKでループバックして自律型トランザクションで対応
base_query = "SELECT * FROM dblink('host=%s port=%s dbname=%s user=%s',%s) AS t(r text)" target_query = "update pg_2_template set src=%s where file_name=%s returning file_name" % ( plpy.quote_literal(edit_src), plpy.quote_literal(edit_file_name) ) last_query = base_query % ( DB_HOST, DB_PORT, DB_NAME, DB_USER, plpy.quote_literal(target_query) )
r_set = plpy.execute(last_query)
問題3
稼動統計が意図せず読み取り一貫性を発揮api()関数で発生pg_stat_activity等がstart_bottole_httpd起動時点の結果しかとれないrepetable read 的な挙動普通の表はちゃんとread commited
問題4
DBLINK経由のクエリで列リストが不定APIでは列リストをURLで指定する実装
url query
/api/*/hoge -> select * from hoge
/api/col1,col2/hoge -> select col1,col2 from hogeDBLINKは戻り値のデータ型を明示しないといけない
対処そうだJSONにしよう
元クエリをjson_aggでラップ戻り値はかならずJSONdblink('dbname=pg_2_layer', 'select json_agg(t) from (元クエリ) t')AS t(result json)
# DBLINKで自律型トランザクションとする# 型にしばられないため、json_aggでラップしている# 戻りは全部JSONdef do_query_over_dblink(v_query_string): # DBLINKの大枠 base_query = "SELECT * FROM dblink('host=%s port=%s dbname=%s user=%s', %s) AS t(result json)" last_query = base_query % ( DB_HOST, DB_PORT, DB_NAME, DB_USER, plpy.quote_literal("select json_agg(t) from (" + v_query_string+ ") t") # クエリも引数なのでquoteする )
plpy.log( last_query) r_set = plpy.execute(last_query) plpy.log(r_set) # jsonで戻しても取得時はstrになってたのでstrとして統一 result_json_str = "[]" if r_set[0]["result"] is not None: result_json_str = r_set[0]["result"] # 利用側の利便性を考えてjsonで戻す
元のクエリをselect * from emp
json_aggでラップしてselect json_agg(t) from ( select * from emp) t
dblinkでラップするselect * from dblink(' select json_agg(t) json from ( select * from emp ) t') AS t(result json)
ctrl+cpg2layer_db=# select start_bottole_httpd(1192);CCancel request sentCCancel request sentCCancel request sent
ctrl+cpg2layer_db=# select start_bottole_httpd(1192);CCancel request sentCCancel request sentCCancel request sentCCancel request sent
対処自分でシグナルハンドラ書くPythonコンテキストでSIGINTを処理するように受け取ったら落ちる
def signal_handler(num, frame): plpy.log("SIGINT_restart") sys.exit(0)
signal.signal(signal.SIGINT, signal_handler)run(host='0.0.0.0', port=v_port)