tuplock - lock tuples (rows) based on a boolean attribute for PostgreSQL
The tuplock PostgreSQL extension provides a trigger to lock tuples (rows) based on a boolean attribute within the tuple.
This PostgreSQL extension can be installed through the PostgreSQL Extension Network (pgxn):
sh> pgxn install tuplock
It can also be compiled and installed manually from sources thanks to the pgxs framework.
sh> tar xzf tuplock-1.2.1.tgz sh> cd tuplock-1.2.1 sh> make sh> sudo make install sh> make installcheck sh> make clean
Once the extension is installed (see INSTALL above), first load the extension:
psql> CREATE EXTENSION tuplock;
Create a table with a boolean attribute for locking:
psql> CREATE TABLE foo(..., lock BOOLEAN NOT NULL DEFAULT FALSE);
Then add the trigger with the boolean locking attribute name:
psql> CREATE TRIGGER foo_tuplock
BEFORE UPDATE OR DELETE ON foo
FOR EACH ROW EXECUTE PROCEDURE tuplock(lock);
You must also prevent TRUNCATE globally:
psql> CREATE TRIGGER foo_tuplock_truncate
BEFORE TRUNCATE ON foo
EXECUTE PROCEDURE tuplock();
Fill in foo, then lock some tuples:
psql> UPDATE foo SET lock=TRUE WHERE ...;
After that, UPDATE or DELETE on tuples where lock is TRUE
will fail. Moreover, all TRUNCATEs are prevented on the table.
The lock can be circumvented by disabling the triggers, if allowed
to the user. This may be prevented with an appropriate REVOKE.
http://www.coelho.net/tuplock/tuplock-1.2.1.tgz
Improve README documentation.
http://www.coelho.net/tuplock/tuplock-1.2.0.tgz
Fix compilation issue for PostgreSQL 9.2. Add note and example about TRUNCATE to explain how to deal with the issue raised in Nuko Yokohama Blog.
http://www.coelho.net/tuplock/tuplock-1.1.1.tgz
Set client logging level for test explicitly.
http://www.coelho.net/tuplock/tuplock-1.1.0.tgz
Add PostgreSQL 9.1 CREATE EXTENSION support,
and use it in the non regression test.
Add PostgreSQL Extension Network (pgxn) support.
http://www.coelho.net/tuplock/tuplock-1.0.0.tgz
Initial revision, tested with PostgreSQL 9.1.2. Should work with earlier versions as well.
All software have bugs, this is a software, hence it has bug.
PostgreSQL does not raise DELETE triggers on TRUNCATE. You can revoke user's TRUNCATE privileges to protect from TRUNCATE, or add another before trigger for this case.
Copyright 2004-2012 Fabien Coelho <fabien at coelho dot net>
PostgreSQL extension tuplock is free software, both inexpensive and provided with sources.
The GNU General Public Licence v3 applies, see http://www.gnu.org/copyleft/gpl.html for details.
The summary is: you get as much as you paid for, and I am not responsible for anything. Beware that you may lose your data, your business or your friends because of this software.