Hatena::ブログ(Diary)

日々の記録 別館

2012-03-26

tuplock extension

しばらく本業その他にかまけていてPostgreSQL関連のwatchをなんにもしてなかった・・・。
で、一週間くらい前にANNOUNCEのMLに流れていたんだけど、tuplockなるextensionがあるらしい。

tuplockとは?

ざっと斜め読みした感じでは、ロック用のタプルをテーブル定義時に入れて、そのタプルの値を一旦TRUEにすると、以降、その行への更新/削除を行おうとするときにトリガ内でエラーにしてくれるものっぽい。
所謂排他制御的なロックというよりも、行単位でのレコードの値を固定するもの、という感じか。

とはいえ、どういう時に使える代物なのかな・・・。

  • 挿入は許容するけど、以降の更新を許容しない。例えば監査ログを書きこんでおき、その監査ログへの参照は許容するけど、更新を許容しないとか?
    • でも、そのケースならGRANTで普通に制御出来そうな気もするしなあ・・・。
    • トリガを削除しちゃえばノーガードにされちゃうし・・・。

この用途としてはGRANT/REVOKEだとテーブル単位の制御になってしまうのを、タプル単位かつ、ユーザが指定するあるタイミングから行単位で更新/削除を抑止するというところにあるのだろう。

動かしてみる

まあいい。とりあえずインストールしてみよう。
zipになっているのでWindows上で解凍してからWinSCPPostgreSQL実験用サーバへ送り、contrib上に置いてmakeしてmake installする。ここまでは問題なし。
で、テスト用のデータベースにCREATE EXTENSIONで突っ込もうとすると

$ psql -p 5434 test -U harada -c "CREATE EXTENSION tuplock"
ERROR: syntax error at or near "\"
LINE 1: CREATE EXTENSION tuplock
^

なんぞこれ
強引にinstallスクリプトを実行しようとすると
$ psql -p 5434 test -U harada -f ~/pgsql/share/extension/tuplock--1.1.1.sql
Use "CREATE EXTENSION tuplock" to load this file.
$
怒られちゃうしなあ・・・
CREATE EXTENSIONで怒られる原因は後で調べるとして、とりあえず直接インストールスクリプトをぶっこんでみる。
$ psql -p 5434 test -U harada -f ~/pgsql/share/extension/tuplock.sql
LOAD
CREATE FUNCTION
$

一応、動作はするようになったと思うが、psqlの\xコマンドで表示されるパッケージ一覧には表示されない。やはりこの方法はおすすめは出来ないのだろうなあ。

まあいい。とりあえずREADMEに従ってテーブルを作ってみよう。
test=> CREATE TABLE hoge (id int, data text, lock BOOLEAN NOT NULL DEFAULT FALSE);
CREATE TABLE
test=> \d hoge
Table "public.hoge"
Column | Type | Modifiers
--------+---------+------------------------
id | integer |
data | text |
lock | boolean | not null default false

test=>

で、トリガを設定。トリガ設定をさせるあたりがイケてない。
test=> CREATE TRIGGER hoge_tuplock BEFORE UPDATE OR DELETE ON hoge FOR EACH ROW EXECUTE PROCEDURE tuplock(lock);
CREATE TRIGGER

データを突っ込む。
test=> INSERT INTO hoge VALUES (1, 'AAA');
INSERT 0 1
test=> INSERT INTO hoge VALUES (2, 'BBB');
INSERT 0 1
test=>

ここまではどうということもない。で、id=1の行のlock列をTRUEに変更(今更だが、列名にlockというSQLキーワードをつけちゃたのはあまりよろしくないなあ・・・)。
test=> UPDATE hoge SET lock = TRUE WHERE id = 1;
UPDATE 1
test=>

で以降、id=1のタプルを変更しようとしたり、削除しようとしたりするとエラーになるというわけだ。
test=> UPDATE hoge SET data='ZZZ' WHERE id = 1;
ERROR: trigger "hoge_tuplock" on "hoge": item locked by attribute "lock"
test=> DELETE FROM hoge WHERE id = 1;
ERROR: trigger "hoge_tuplock" on "hoge": item locked by attribute "lock"
test=> DELETE FROM hoge ;
ERROR: trigger "hoge_tuplock" on "hoge": item locked by attribute "lock"
test=> TRUNCATE TABLE hoge ;
TRUNCATE TABLE
test=>

TRUNCATEトリガはセットしていないのでTRUNCATEされちまった。当たり前だw

まとめ

ちょっと面白い拡張ではあるけど、使い所はちょっと難しそうだなあ・・・。

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証

トラックバック - http://d.hatena.ne.jp/nuko_yokohama/20120326/1332745100
リンク元