スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

ストアードプロシージャのノウハウ。名前編

ストアードプローシージャの名前のつけ方です。
だいたい以下のようにやっています。

[プロジェクト名]_[set or get]_[動詞]_[名詞(主なテーブル名)]_for_[目的]


ecproj_get_list_product_for_member_top
ecproj_set_regist_product

プロジェクト名をつけるのはnamespaceとして他とぶつからないために使用します。

get or setはpgpoolでQueryを分散できるかどうかの判定に使います。
getはDBを更新しないので、どのサーバーで実行しても問題ありませんが
setはDBを更新するので、マスターでしか実行しないように強制する必要があります。
pgpoolでは名前で判定できるようになっています。

残りは他とぶつからなければ、特にどういうつけ方をしても問題ないのですが
なんとなく統一して使っています。

ストアードプロシージャのノウハウ。引数編

PostgreSQLのユーザ関数の引数はデフォルト値が指定できて省略が可能です。

CREATE OR REPLACE FUNCTION sp_test(p_param1 INT, DEFAULT 0 p_param2 text DEFAULT '')
RETURNS VOID AS $FUNCTION$
BEGIN
END;
$FUNCTION$ LANGUAGE plpgsql;

以下のように使います。

SELECT sp_test(1, 'abc');
SELECT sp_test(p_params1 := 2);
SELECT sp_test(p_params2 := 'efg');

この機能は色々な条件で検索する画面などで便利に使えます。
画面でいくつかの値を指定して検索を実行するときに、
指定された値と関数の引数がマッチさせることでMVCがスムーズに実装できます。

Railsでの実装例です。Modelクラスのクラスメソッドとして実装します。

def self.find_for_sp(name, db_params)
bind_params = db_params.map{|k, v| "p_#{k} := :#{k}" }
sql = " SELECT * FROM #{name} (#{bind_params.join(",")}) "
find_by_sql([sql, db_params])
end

以下のように実行します。
Model.find_for_sp("sp_test", {params1: 1, params2: "abc"});
# SELECT * FROM sp_test(p_params1 := 1, p_params2 := 'abc');

ストアードプロシージャのノウハウ。削除編

PostgreSQLの関数定義では同じ名前でも引数の型や引数の数が違えば、定義することが可能です。

CREATE OR REPLACE FUNCTION sp_test(param1 int)
RETURNS VOID AS $FUNCTION$
BEGIN
END;
$FUNCTION$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION sp_test(param1 int, param2, text)
RETURNS VOID AS $FUNCTION$
BEGIN
END;
$FUNCTION$ LANGUAGE plpgsql;

そのため関数を削除する場合、名前と引数の型が一致していないと削除できません。

DROP FUNCTION sp_test(param1 int);
DROP FUNCTION sp_test(param1 int, param2, text);

関数を開発中の場合、引数が増えたり減ったりすることがよくあります。
そのたびに引数の型すべてを意識して削除するのは、面倒くさいです。

そこで独自に型を定義して、関数はその型を返すようにすることで
独自の型をCASCADE指定で削除すると関数も削除することができます。

DROP TYPE IF EXISTS type_sp_test CASCADE;
CREATE TYPE type_sp_test AS (
id BIGINT
);
CREATE OR REPLACE FUNCTION sp_test(param1 int, param2, text)
RETURNS SETOF type_sp_test AS $FUNCTION$
BEGIN
END;
$FUNCTION$ LANGUAGE plpgsql;

独自の型の削除、独自の型の定義、独自の型を仕様した関数の定義を連続して流すことで、引数が変わったとしても同じ名前の関数が増えたりしません。

スカラーの値を戻したい関数の場合は使えませんが
それ以外の目的で、例え戻り値が特に必要が無い場合でも、独自の型を定義するのがお勧めです。

PL/pgSQLで動的SQLを書く必要がなくなった

ちょっと古いですけどPostgreSQL9.2のリリースノートに

「プリペアド文が使われる場合であっても、特定のパラメータ値のための特別な計画をプランナが生成できるようにしました。」

とあります。2番目にあるくらいなので重要だと思うのですが、当時見た私は何のことだかわからずスルーしていたようです。で詳細を見ると、

「以前は、プリペアド文は必ず1つだけの"一般的な"計画があり、それがすべてのパラメータ値に対して使われていました。 これは、明示的な定数値を持つプリペアドでない文で使われる計画に比べ、大幅に劣ることが時々ありました。 これからは、プランナは、特定のパラメータ値に対して特別な計画を生成しようとします。 特別な計画による利点がないことが繰り返し証明された後になって初めて、一般的な計画が使われます。 この変更により、以前からあった、(PL/pgSQLの動的でない文を含め)プリペアド文を使うことによるパフォーマンスの不利を排除できます。」

これ重要なのは最後の文章で、以前はPL/pgSQLを使うと動的でない素のSQLを書くとインデックスが効かずにまるでパフォーマンスがでませんでした。で、ほとんどすべて動的SQLで書くことになっていました。
ところが、このリリースノートによればそれが解消されているとなります。そうなると直感的に動的SQLを使っているほうが、都度SQLがパースされるため重くなるんじゃないかと思いました。

ということで、検証してみました。以下、使用したSQLです。
DROP TABLE IF EXISTS t_user CASCADE;
CREATE TABLE t_user (
id BIGINT
,nm TEXT
,PRIMARY KEY(id)
);

CREATE OR REPLACE FUNCTION insert_test(
p_count BIGINT DEFAULT NULL
) RETURNS VOID AS $FUNCTION$
DECLARE
BEGIN
FOR i IN 1..p_count LOOP
INSERT INTO t_user (
id
,nm
) VALUES (
i
,i::TEXT
);
END LOOP;
END;
$FUNCTION$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION sel_test1(
p_id BIGINT DEFAULT NULL
) RETURNS SETOF t_user AS $FUNCTION$
DECLARE
BEGIN
RETURN QUERY SELECT * FROM t_user WHERE nm = p_id::TEXT;
END;
$FUNCTION$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION sel_test2(
p_id BIGINT DEFAULT NULL
) RETURNS SETOF t_user AS $FUNCTION$
DECLARE
BEGIN
RETURN QUERY SELECT * FROM t_user WHERE id = p_id;
END;
$FUNCTION$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION sel_test3(
p_id BIGINT DEFAULT NULL
) RETURNS SETOF t_user AS $FUNCTION$
DECLARE
BEGIN
RETURN QUERY EXECUTE $$
SELECT * FROM t_user WHERE id = $1
$$ USING
p_id
;
END;
$FUNCTION$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION sel_test4(
p_id BIGINT DEFAULT NULL
) RETURNS SETOF t_user AS $FUNCTION$
DECLARE
BEGIN
RETURN QUERY SELECT * FROM sel_test2(p_id);
END;
$FUNCTION$ LANGUAGE plpgsql;

これをrubyから呼んでみます。
require 'sequel'

DB = Sequel.connect('postgres://localhost/test')

def select(n)
ds = DB["SELECT * FROM sel_test4(?)", :$id]
n.times do |i|
ds.call(:select, id: i)
end
end

s = Time.now
p s

select(100000)

e = Time.now
p e
puts "#{e - s} sec"

データは10,000,000件作成。(77.97秒)
select insert_test(10000000)

sel_test2,3,4を100,000回呼び出してみました。
sel_test2 45.524594 sec
sel_test3 53.544521 sec
sel_test4 45.996167 sec

結論としては、PL/pgSQLでは素のSQLが速度が速いです。PL/pgSQLを多段に呼ぶのはそんなに重くないようです。動的SQLは確かに遅いですが、そこまで速度にシビアでなければ便利に利用すれば良いと思います。

PostgreSQLのupstartを書いてみた

/etc/init.dから/etc/initへ移行。

・/etc/init.dから削除
sudo /etc/init.d/postgresql stop
sudo update-rc.d -f postgresql remove
sudo rm /etc/init.d/postgresql

・/etct/initへ
sudo vi /etc/init/postgresql.conf
description "PostgreSQL"

start on net-device-up and local-filesystems
stop on shutdown

env PGUSER=aoyagikouhei
env PROGRAM=/home/aoyagikouhei/opt/postgresql/bin/postgres
env PGDATA=/home/aoyagikouhei/var/postgresql/9.3/5432/data
env PGPID=/home/aoyagikouhei/var/postgresql/9.3/5432/5432.pid

respawn
respawn limit 10 5
exec start-stop-daemon --start --make-pidfile --pidfile $PGPID --chuid $PGUSER --exec $PROGRAM -- -D $PGDATA
上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。