tuplock

NAME

tuplock - lock tuples (rows) based on a boolean attribute for PostgreSQL

DESCRIPTION

The tuplock PostgreSQL extension provides a trigger to lock tuples (rows) based on a boolean attribute within the tuple.

INSTALL

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.4.tgz
sh> cd tuplock-1.2.4
sh> make
sh> sudo make install
sh> make installcheck
sh> make clean

EXAMPLE

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.

DOWNLOAD

The main site for this extension is http://www.coelho.net/tuplock/.

• version 1.2.4 on 2019-04-07

  http://www.coelho.net/tuplock/tuplock-1.2.4.tgz

  Allow to override pg_config path. Tested with Postgres 11.2.

• version 1.2.3 on 2019-04-06

  Fix pointer type error.

• version 1.2.2 on 2017-10-08

  http://www.coelho.net/tuplock/tuplock-1.2.2.tgz

  Improved tests. Minor code cleanup. Tested with PostgreSQL 10.0.

• version 1.2.1 on 2012-12-15

  http://www.coelho.net/tuplock/tuplock-1.2.1.tgz

  Improve README documentation.

• version 1.2.0 on 2012-12-15

  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.

• version 1.1.1 on 2012-03-17

  http://www.coelho.net/tuplock/tuplock-1.1.1.tgz

  Set client logging level for test explicitly.

• version 1.1.0 on 2012-03-17

  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.

• version 1.0.0 on 2012-02-26

  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.

BUGS

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.

LICENSE

Copyright 2004-2019 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.