-- validate lock tuple trigger

-- set defaults for the testing environment
\set ON_ERROR_STOP 1
SET client_min_messages='notice';

-- load extension for PostgreSQL 9.1
CREATE EXTENSION tuplock;

-- test in a distinct schema...
CREATE SCHEMA test_tuplock;
SET search_path TO test_tuplock,public;

CREATE TABLE test(
  tid SERIAL PRIMARY KEY,
  data TEXT NOT NULL,
  -- locking attribute
  lock BOOLEAN NOT NULL DEFAULT FALSE
);

-- create the locking trigger
CREATE TRIGGER testlock
BEFORE UPDATE OR DELETE ON test
FOR EACH ROW EXECUTE PROCEDURE tuplock(lock);

-- must also forbid truncate
CREATE TRIGGER testlock2
BEFORE TRUNCATE ON test
EXECUTE PROCEDURE tuplock();

-- current status: 0
SELECT COUNT(*) FROM test;

-- first insert new values
INSERT INTO test(data) VALUES('hello');
INSERT INTO test(data) VALUES('world');
INSERT INTO test(data) VALUES('!');
INSERT INTO test(data) VALUES('foo');
INSERT INTO test(data) VALUES('bla');

-- show all
SELECT * FROM test ORDER BY tid;

-- update is fine
UPDATE test SET data='HELLO' WHERE data='hello';

-- delete is fine
DELETE FROM test WHERE data='!';

-- then lock all but foo & bla entries
UPDATE test SET lock=TRUE WHERE lock=FALSE AND length(data)<>3;

-- errors are fine, now:
\set ON_ERROR_STOP 0

-- now updates are not ok
UPDATE test SET data='WORLD' WHERE data='world'; -- fails
UPDATE test SET lock=FALSE; -- fails

-- now deletes are not ok
DELETE FROM test WHERE data='HELLO'; -- fails

-- as well as truncates
TRUNCATE test;

-- back to normal
\set ON_ERROR_STOP 1

-- this update is ok
UPDATE test SET data='FOO' WHERE data='foo';

-- this delete is ok
DELETE FROM test WHERE data='bla';

-- show all
SELECT * FROM test ORDER BY tid;

-- cleanup
DROP SCHEMA test_tuplock CASCADE;
DROP EXTENSION tuplock;