iakioの日記

psycopg2でhstoreをdictにマップ

Python | 02:40 |

hstoreというのはcontribにはいっているkey-valueデータ型です。

psycopg2の2.3.0以降では、hstoreをPythonのdictにマップできるそうです。

• hstore
• Psycopg 2.3.0 released | Psycopg

ところで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のサンプルもありましたが理解してないのでリンクだけ貼っておきます。

• http://initd.org/psycopg/articles/2010/12/01/postgresql-notifications-psycopg2-eventlet/

EXIT WHEN NOT FOUND

PL/pgSQL | 19:19 |

昔書いた、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... もあるそうですよ。

• 制御構造

【9/18】札幌でハンズオン形式の勉強会を開催します【PCあるよ】

00:23 |

2010/09/18(土)に札幌でJPUG主催のハンズオン形式の勉強会を行うそうです。

• PostgreSQL勉強会＠札幌 : ATND
• 【受付開始】札幌でハンズオン形式の勉強会を開催します！ — NPO法人 日本PostgreSQLユーザ会

会場にPCが用意されているのでノートPC等を持参する必要は無いとのこと。無料です。

日本PostgreSQLユーザ会 北海道支部 勉強会

00:46 |

日本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でレプリケーションしてみたけど何か質問ある？

趣旨：あんま詳しい説明はできないけどこんぐらいで試してみることはできますよ

インストール

• yumでPostgreSQLをインストールしてみよう — Let’s Postgres

/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はモダンです

• 日本PostgreSQLユーザ会 北海道支部 勉強会（2010-08-18）に参加してきた - gishi_yamaの日記

PostgreSQLのマニュアルでよく使われている英単語(ry)その3

16:38 |

最初に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)