tuplock extension

しばらく本業その他にかまけていて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=>

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

まとめ

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