tuplock 1.2.0

This Release
tuplock 1.2.0
Date
Status
Stable
Latest Stable
tuplock 1.2.1 —
Other Releases
Abstract
lock tuples (rows) with a boolean attribute
Description
Provides a trigger that allows to lock tuples (rows) based on the value of a boolean attribute: once locked, the trigger must be disabled to change a tuple.
Released By
fabien
License
The GNU General Public License, Version 3, June 2007
Resources
Special Files
Tags

Extensions

tuplock 1.2.0
lock tuples (rows) with a boolean attribute

README

Contents

A conservative trigger for locking tuples.
The lock is set by a user-chosen boolean column in each tuple.

psql> CREATE EXTENSION tuplock;

psql> CREATE TABLE foo(data TEXT, locked BOOL NOT NULL DEFAULT FALSE);

psql> CREATE TRIGGER foo_tuplock BEFORE UPDATE OR DELETE ON foo
psql>   FOR EACH ROW EXECUTE PROCEDURE tuplock(locked);

Then after some tuples are added to foo, they can be locked:

psql> UPDATE foo SET locked=TRUE WHERE some_condition;

Any update or delete attempt on locked tuples will then be rejected:

psql> UPDATE foo SET data='hobbes' WHERE data='Hobbes';
ERROR:  trigger "foo_tuplock" on "foo": item locked by attribute "locked"

If the trigger is badly defined (wrong attribute name or type,
wrong number of arguments), or the attribute value is NULL,
then the tuple is locked by default.


One must drop the trigger in order to really unlock,
with something like:

psql> BEGIN;
psql> LOCK TABLE foo IN ACCESS EXCLUSIVE MODE;
psql> ALTER TABLE foo DISABLE TRIGGER foo_tuplock;
psql> UPDATE foo SET locked=FALSE WHERE some_condition;
psql> ALTER TABLE foo ENABLE TRIGGER foo_tuplock;
psql> COMMIT;