2010-12-09
psycopg2でhstoreをdictにマップ
Python | |
hstoreというのはcontribにはいっているkey-valueデータ型です。
psycopg2の2.3.0以降では、hstoreをPythonのdictにマップできるそうです。
ところでhstoreの前にARRAYはどうなってるのと思ったらサポート済みの様子。
>>> import psycopg2
>>> conn = psycopg2.connect("")
>>> cur = conn.cursor()
>>> cur.execute("select %s", ([1,2,3],))
>>> cur.fetchone()
([1, 2, 3],)
cursor.mogrify()で実際に発行されるクエリを確認できます。
>>> cur.mogrify("select %s", ([1,2,3],))
'select ARRAY[1, 2, 3]'
タプルをROWにはできるけどその逆はできないのかもしれない。
>>> cur.mogrify("select %s", ((1,"foo"),))
"select (1, E'foo')"
>>> cur.execute("select %s", ((1,"foo"),))
>>> cur.fetchone()
('(1,foo)',)
さて、hstoreを使うには、DBにhstoreをインストールした上で、psycopg2.extras.register_hstore()を実行します。これを実行した時にhstoreのoidを取りにいっている様子。
>>> import psycopg2.extras
>>> psycopg2.extras.register_hstore(cur)
>>> cur.execute("select 'a => 1'::hstore")
>>> cur.fetchone()
({'a': '1'},)
>>> cur.mogrify("select %s", (dict(a=1, b=2),))
"select hstore(ARRAY[E'a', E'b'], ARRAY[1, 2])"
>>> cur.execute("select * from each(%s)", (dict(a="foo", b="bar"),))
>>> cur.fetchall()
[('a', 'foo'), ('b', 'bar')]
あとEventletを使ったLISTEN/NOTIFYのサンプルもありましたが理解してないのでリンクだけ貼っておきます。
2010-11-03
EXIT WHEN NOT FOUND
PL/pgSQL | |
昔書いた、PREPARE / EXECUTEにrefcursorを渡すというヤツは無くなったのかもしれない。
- PREPARED STATEMENTにカーソルを渡す - iakioの日記 - postgresqlグループ
- Fix WHERE CURRENT OF to work as designed within plpgsql. The argument ? 2ace38d ? postgres/postgres ? GitHub
無くなっても別に困りはしないのだけれど。
それよりこのテストケースのplpgsql、"exit when not found"って初めて見ました。なんかカッコイイ。
open c for select * from forc_test;
loop
fetch c into r;
exit when not found;
raise notice '%, %', r.i, r.j;
update forc_test set i = i * 100, j = r.j * 2 where current of c;
end loop;
後置if的なもの?かと思ったらEXITステートメントのオプションですか。CONTINUE WHEN... もあるそうですよ。
2010-09-06
【9/18】札幌でハンズオン形式の勉強会を開催します【PCあるよ】
2010/09/18(土)に札幌でJPUG主催のハンズオン形式の勉強会を行うそうです。
会場にPCが用意されているのでノートPC等を持参する必要は無いとのこと。無料です。
2010-08-18
日本PostgreSQLユーザ会 北海道支部 勉強会
日本PostgreSQLユーザ会 北海道支部 勉強会 : ATNDに参加してきました
俺の設計にみんながダメ出しをするテーブル設計勉強会
- prefixやpostfix、複数形の命名は予約語除けになる
- SQL予約語多すぎhttp://www.postgresql.org/docs/9.0/static/sql-keywords-appendix.html
- 複雑なデータ構造はJSONやXMLにシリアライズして格納してしまう
- 多対多の中間テーブルの主キーはサロゲートキーを導入するか、複合キーとするか
- サロゲートキーを導入するなら、2つのキーは複合一意にすべき
- 主キーを複合キーとすると、usersの外部キーをON DELETE SET NULLとかできない
- unique(key1, key2)は一方がNULLであれば他方が同じ値でも許容する
=# create table t1(i1 int, i2 int, primary key(i1, i2)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" CREATE TABLE =# insert into t1 values(1, NULL); ERROR: null value in column "i2" violates not-null constraint =# create table t2(i1 int, i2 int, unique(i1, i2)); NOTICE: CREATE TABLE / UNIQUE will create implicit index "t2_i1_i2_key" for table "t2" CREATE TABLE =# insert into t2 values(1, NULL); INSERT 0 1 =# insert into t2 values(1, NULL); INSERT 0 1
- ATNDはユーザーを削除できないすばらしい仕様
- 削除フラグェ、、、
PostgreSQL9.0でレプリケーションしてみたけど何か質問ある?
趣旨:あんま詳しい説明はできないけどこんぐらいで試してみることはできますよ
インストール
/usr/pgsql-9.0/bin にパスを通す
SR/HS設定の参考資料
- OSC2010-do ホット・スタンバイ解説 - PostgreSQL 雑記 - postgresqlグループ
- レプリケーションでのノード構成 - PostgreSQL 雑記 - postgresqlグループ
- PostgreSQL:Streaming Replication [1] クイックスタート - The Programmer’s Guide to the Parallel World
ポイント
- Streaming Replicationでは、slaveがmasterに接続してログを転送する(pg_hba.conf / recovery.confのprimary_conninfo)
masterでやること
1. postgresql.confを編集
logging_collector = off log_line_prefix='db1 %t ' port=5401 archive_mode=on archive_command='cp %p /home/hogehoge/archive/%f' max_wal_senders=3 wal_level = hot_standby
2. pg_hba.confを編集
local replication all trust
3. ベースバックアップを作成
- select pg_start_backup('..');
- cp -R db5401 db5402
- select pg_stop_backup();
slaveでやること
1. recovery.conf を作成
restore_command='cp /home/jpug_ezo/archive/%f %p' standby_mode='on' primary_conninfo='port=5401'
2. postgresql.confを編集
hot_standby=on
3. postmaster.pidを削除
4. 起動
質疑等
- 最初失敗したけど何やったの?
- recovery.confを編集したけどslaveの$PGDATAの中に入れてませんでした
- replicationというdbは作れないの?
- pg_hba.conf内では"replication"とクオートすればOK
- slaveでsetval()すると?
=# SELECT setval('r_i_seq', 1000);
ERROR: cannot execute setval() in a read-only transaction
- slaveでLOCKすると?
=# begin; BEGIN =# lock r; ERROR: cannot execute LOCK TABLE during recovery
- slaveを増やすには
- 一番簡単なのは、既存のslaveを止めてインスタンス丸ごとコピーで大丈夫な気がする。slave動かしたままやる方法は http://www.postgresql.org/docs/9.0/static/backup-incremental-updated.html
- マスタ側の負荷ってどう?
- 正直よくわからんです
- ちょうどpgpool-II 3.0のbeta1が出ました
gishi_yamaさんから感想いただきました。RETURNINGはモダンです
2010-08-16
PostgreSQLのマニュアルでよく使われている英単語(ry)その3
最初にPHPでやった時は文字コードをiso8859-1からUTF-8に変換したのに、前回は何でやらなかったんだろうと思ってたら、実はデータベースエンコーディングがSQLASCIIだったというヘマをやってました。
で、UTF-8のDBにインポートしてみようと思ったのですが、pg_read_file()が文字コード指定できないうえに内部でpg_verifymb()されているために、テータベースエンコーディング以外のファイルを読み込もうとするとエラーになってしまいます。せめてtextじゃなくbyteaを返してくれればいいんですが。
この関数をこんな目的で使おうとしているのが世界で一人かもしれないけど。
で、仕方ないんでpg_verifymb()はコメントアウトして(w)、あとpg_read_file()にはbyteaを返してもらうようにします。textとbyteaは内部表現は一緒なはずなので、
=# create function pg_read_file_bytea(text, bigint, bigint) returns bytea language internal as 'pg_read_file';
と、無理矢理再利用してあげればOK。byteaをconvert_fromで変換します。
=# insert into pg84doc
with
s1 as (select (pg_stat_file('html/' || pg_ls_dir)).*, pg_ls_dir from pg_ls_dir('html')),
s2 as (select pg_ls_dir, convert_from(pg_read_file_bytea('html/' || pg_ls_dir, 0, size)::bytea, 'iso88591') as contents from s1)
select pg_ls_dir, contents, to_tsvector('english', contents) from s2;
さて日本語でもやってみてとの声が聞こえてきたので、textsearch_jaでやってみました。トップ30は以下の通り。
=# select rank() over (order by nentry desc),
word, nentry from ts_stat('select vec from pg84jdoc') limit 30;
rank | word | nentry
------+--------------+--------
1 | する | 39986
2 | れる | 13053
3 | 場合 | 5466
4 | 使用 | 5228
5 | できる | 5129
6 | この | 4791
7 | いる | 4624
8 | なる | 4609
9 | 関数 | 4493
10 | ある | 4487
11 | 型 | 3905
12 | 1 | 3765
13 | 列 | 3459
14 | テーブル | 3424
15 | 値 | 3277
16 | postgresql | 2998
17 | データ | 2866
18 | pg | 2860
19 | くださる | 2427
20 | データベース | 2370
21 | 2 | 2330
22 | 指定 | 2326
23 | 設定 | 2318
24 | 行 | 2314
25 | 的 | 2217
25 | sql | 2217
27 | その | 2213
28 | コマンド | 2163
29 | リリース | 2141
30 | 問い合わせ | 2081
(30 rows)
通りすがりです。
面白い内容のブログですね。
更新楽しみです^^
ではでは。