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_delup BEFORE UPDATE OR DELETE ON foo
psql>   FOR EACH ROW EXECUTE PROCEDURE tuplock(locked);

psql> CREATE TRIGGER foo_tuplock_trunc BEFORE TRUNCATE ON foo
psql>   EXECUTE PROCEDURE tuplock();

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_delup" 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.

If the second trigger is present, it will prevent any TRUNCATE on the
table, otherwise the user may be able to erase the whole contents
of the table, and reinsert them as required.

One must drop the trigger in order to really unlock,
which require some admin permissions, with something like:

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