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;