tuplock extension

2012-03-26

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

tuplockとは？

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

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

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

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

動かしてみる

まあいい。とりあえずインストールしてみよう。
zipになっているのでWindows上で解凍してからWinSCPでPostgreSQL実験用サーバへ送り、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=>