Lock tuple PostgreSQL extension

• NAME
• DESCRIPTION
• INSTALL
• EXAMPLE
• DOWNLOAD
• BUGS
• LICENSE

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.